How to udpate and alter procedure at once.

[ad]

I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read from
a text file.
I want to sql to update a record in myTable, and alter a procedure.

The text file is like:
--------------------------------------------------------------------------------
UPDATE myTable set Col1='text'

ALTER procedure [dbo].[EmptyData]
as
Delete from Acc;
Delete from BodyMind;
Delete from Checks;
Delete from CheckTemp;
--------------------------------------------------------------------------------

when I execute the ExecuteNonQuery, it result into an error: The Alter
command must in the first line.

But if I put the Update command at last line, it become a part of the ALTER
procedure.

How can I udpate and alter procedure at once.

 

[Cor Ligthert [MVP]]

AD,

I assume with a ";" between it.

Cor

 

[Jim Hughes]

ALTER and CREATE statements need to be executed in their own batch.

Query Analyzer uses the GO keyword to separate batches. While it is not
TSQL, it is the convention used to separate batches. In other words you
can't send GO to SQL Server, you can do string processing to separate the
batches.

 

[William \(Bill\) Vaughn]

Nope, the ";" is ignored.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

AD,

I assume with a ";" between it.

Cor

I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read
from a text file.
I want to sql to update a record in myTable, and alter a procedure.

The text file is like:
--------------------------------------------------------------------------------
UPDATE myTable set Col1='text'

ALTER procedure [dbo].[EmptyData]
as
Delete from Acc;
Delete from BodyMind;
Delete from Checks;
Delete from CheckTemp;
--------------------------------------------------------------------------------

when I execute the ExecuteNonQuery, it result into an error: The Alter
command must in the first line.

But if I put the Update command at last line, it become a part of the
ALTER procedure.

How can I udpate and alter procedure at once.

 

[ad]

Thanks
Could you tell me how to use stirng processing?

ALTER and CREATE statements need to be executed in their own batch.

Query Analyzer uses the GO keyword to separate batches. While it is not
TSQL, it is the convention used to separate batches. In other words you
can't send GO to SQL Server, you can do string processing to separate the
batches.

I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read
from a text file.
I want to sql to update a record in myTable, and alter a procedure.

The text file is like:
--------------------------------------------------------------------------------
UPDATE myTable set Col1='text'

ALTER procedure [dbo].[EmptyData]
as
Delete from Acc;
Delete from BodyMind;
Delete from Checks;
Delete from CheckTemp;
--------------------------------------------------------------------------------

when I execute the ExecuteNonQuery, it result into an error: The Alter
command must in the first line.

But if I put the Update command at last line, it become a part of the
ALTER procedure.

How can I udpate and alter procedure at once.

 

[William \(Bill\) Vaughn]

We built an application to do just this as a lab exercise in my TSQL/VB
classes (years ago). Basically, you take the inbound string (the script)
which contains each batch of commands to execute separated by "GO<cr><lf>".
Your code parses the string and executes each batch in turn watching for
exceptions that would mean you need to stop executing subsequent batches.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Thanks
Could you tell me how to use stirng processing?

ALTER and CREATE statements need to be executed in their own batch.

Query Analyzer uses the GO keyword to separate batches. While it is not
TSQL, it is the convention used to separate batches. In other words you
can't send GO to SQL Server, you can do string processing to separate the
batches.

I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read
from a text file.
I want to sql to update a record in myTable, and alter a procedure.

The text file is like:
--------------------------------------------------------------------------------
UPDATE myTable set Col1='text'

ALTER procedure [dbo].[EmptyData]
as
Delete from Acc;
Delete from BodyMind;
Delete from Checks;
Delete from CheckTemp;
--------------------------------------------------------------------------------

when I execute the ExecuteNonQuery, it result into an error: The Alter
command must in the first line.

But if I put the Update command at last line, it become a part of the
ALTER procedure.

How can I udpate and alter procedure at once.