Executing Stored Proc from file in Ado .NET

[CliffNews]

Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin

Thanks

 

[Val Mazur]

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

 

[CliffNews]

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP

Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin

Thanks

 

[Joe Fallon]

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP

Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin

Thanks

 

[William Ryan eMVP]

Cliff, this shouldn't be a problem since the CommandText property of the
command object is of type string. you could do something like sql =
sql.Replace("GO", ";") as a quick and dirty way, but I'd recommend using
Regex Replace to make sure "Handango" isn't replaced with "Handan;". Once
you write it once, you could have somethign like a batchEncode class
somewhat like the HTMLEncode class that does this for you..just write it in
one function, make it static/shared and off you go.

HTH,

Bill

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP

Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin

Thanks

 

[Val Mazur]

Hi,

Yes, this is what I meant. In this case provider will treat each block as a
one logical SQL statement

--
Val Mazur
Microsoft MVP

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through
these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response]
@day_delete_interval int
as
begin

Thanks

 

[CliffNews]

Thanks everyone, however when I replace the go with (
I still get 'CREATE PROCEDURE' must be the first statement in a query
batch. Here is my code snippet maybe someone can tell me what I'm doing
incorrectly

string
sqlCode=Regex.Replace(this.GetSQLStream(sqlfile),@"\bGO\b",";",RegexOptions.
IgnoreCase);

string frnConnectionString=@"Software\test\COMX\Database\SqlServerLogin";

RegistryKey Hklm = Registry.LocalMachine.OpenSubKey(frnConnectionString);

string RegValue = Hklm.GetValue("SqlConnection").ToString();

Hklm.Close();

DataSet pvtDataSet;

SqlConnection pvtConnection;

SqlCommand pvtCommand;

SqlDataAdapter pvtSQLDataAdapter;

pvtConnection = new SqlConnection(RegValue);


try

{


pvtCommand = new SqlCommand(sqlCode, pvtConnection);

pvtDataSet = new DataSet();

pvtSQLDataAdapter = new SqlDataAdapter(pvtCommand);

pvtSQLDataAdapter.Fill(pvtDataSet);

return true;//return pvtDataSet;

}


here is the code

Cliff, this shouldn't be a problem since the CommandText property of the
command object is of type string. you could do something like sql =
sql.Replace("GO", ";") as a quick and dirty way, but I'd recommend using
Regex Replace to make sure "Handango" isn't replaced with "Handan;". Once
you write it once, you could have somethign like a batchEncode class
somewhat like the HTMLEncode class that does this for you..just write it in
one function, make it static/shared and off you go.

HTH,

Bill

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response]

@day_delete_interval
int

as
begin

Thanks

 

[CliffNews]

I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon

Thanks for the quick reply Val. I'm not sure if I quite follow you. Are you
saying for each statement in my batch I should terminate it with a (. e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the end
of each statement). If so that will be a pain considering the procs that i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure from a file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response]

@day_delete_interval
int

as
begin

Thanks

 

[Joe Fallon]

You can't do that in Query Analyzer.

1. QA expects the GO keyword. It is a feature in *QA*!!!!
QA uses it to parse the batch.

2. SQL Server takes the parsed batches and processes them.
So when you are NOT using QA - and you ARE talking directly to SQL Server
use the ; as the batch separator.
--
Joe Fallon

I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon

Thanks for the quick reply Val. I'm not sure if I quite follow you.

Are
you

saying for each statement in my batch I should terminate it with a

(.
e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response] @day_delete_interval int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) > @day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at

the
end

of each statement). If so that will be a pain considering the procs

that
i'm

executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you

cannot
use

it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure from a
file.
The problem is when I remove the 'GO' command, I get "'CREATE PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
as
begin

Thanks

 

[CliffNews]

Joe I understand this, however after replacing the GO's with ; I still get
the error. So My question now is,instead of doing this:

pvtCommand = new SqlCommand(sqlCode, pvtConnection); //where sqlCode
contains the new stored proc with the ( instead of (GO)

pvtDataSet = new DataSet();

pvtSQLDataAdapter = new SqlDataAdapter(pvtCommand);

pvtSQLDataAdapter.Fill(pvtDataSet);

should I do something else... perhaps executenonquery??



Cliff

You can't do that in Query Analyzer.

1. QA expects the GO keyword. It is a feature in *QA*!!!!
QA uses it to parse the batch.

2. SQL Server takes the parsed batches and processes them.
So when you are NOT using QA - and you ARE talking directly to SQL Server
use the ; as the batch separator.
--
Joe Fallon

I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon




Thanks for the quick reply Val. I'm not sure if I quite follow you. Are
you
saying for each statement in my batch I should terminate it with a (.
e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response]

@day_delete_interval
int

as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) >
@day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the
end
of each statement). If so that will be a pain considering the procs that
i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot
use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some
other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure

from

a

file.
The problem is when I remove the 'GO' command, I get "'CREATE
PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
as
begin

Thanks

 

[CliffNews]

so I tried using ExecuteNonequery method. it still failed: Here is my test
code

try

{

if(con.State !=ConnectionState.Open)

{

con.Open();

}

int i=cmd.ExecuteNonQuery();

return true;

}

catch(SqlException sql)

{

MessageBox.Show(sql.Message);

return false;

}

Here is a snippit of one of the SQL script:

DECLARE @CORE_CORE3572301500_release_exists INT
EXEC @CORE_CORE3572301500_release_exists = sp_COMXVerifyPatch
'3',
'5',
'723',
'CORE',
NULL,
0,
'15',
'00'
IF @CORE_CORE3572301500_release_exists > 0
BEGIN
PRINT 'Maintenance Release 3.5.723.01500 has already been applied.'
RETURN
END

PRINT 'BEGIN: Maintenance Release - Schema'
PRINT ''
PRINT 'X:\Db\Schema\Tables\Change\bill_wrk_attr_prc_plan_chg.sql
15716(myurkus)'
; <--------------GO REPLACE HERE

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.bill_wrk_attr_prc_plan_chg ADD
is_recalculate bit NOT NULL CONSTRAINT
DF_bill_wrk_attr_prc_plan_chg_is_recalculate DEFAULT 0
;<--------------GO REPLACE HERE

COMMIT
PRINT 'X:\Db\Schema\Tables\Change\bill_wrk_invoice_portfolio.sql
15716(myurkus)'
;<--------------GO REPLACE HERE

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.bill_wrk_invoice_portfolio ADD
override_price money NULL
;<--------------GO REPLACE HERE

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[acport_upd_portfolioChangeHistory]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[acport_upd_portfolioChangeHistory]
;<--------------GO REPLACE HERE

CREATE Procedure acport_upd_portfolioChangeHistory ------CODE BOMBS OUT WHEN
IT GETS TO CREATE PROCEDURE

SO someone tell me what am i doing wrong, I'm talking directly to SQL Server
using ADO .NET

Sorry for being such a pain but you guys seem so convince that it works...
and I fee like a dumb ass. Show me Light.

Cliff

You can't do that in Query Analyzer.

1. QA expects the GO keyword. It is a feature in *QA*!!!!
QA uses it to parse the batch.

2. SQL Server takes the parsed batches and processes them.
So when you are NOT using QA - and you ARE talking directly to SQL Server
use the ; as the batch separator.
--
Joe Fallon

I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.

I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon




Thanks for the quick reply Val. I'm not sure if I quite follow you. Are
you
saying for each statement in my batch I should terminate it with a (.
e.g

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];


create procedure [dbo].[delete_datafeed_response]

@day_delete_interval
int

as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;

if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) >
@day_delete_interval);

end;

I'm not sure if this is what you mean (that is putting semi-colon at the
end
of each statement). If so that will be a pain considering the procs that
i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting ( at the end of each line will be pain.

Hi,

Since GO is not a valid SQL statement, but batch statement, you cannot
use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some
other
providers do not support this feature

--
Val Mazur
Microsoft MVP


Hi all,
I'm trying to a automate the execution of a stored procedure

from

a

file.
The problem is when I remove the 'GO' command, I get "'CREATE
PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.

code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]

GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
as
begin

Thanks