How to udpate and alter procedure at once.

  • Thread starter Thread starter ad
  • Start date Start date
A

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.
 
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.
 
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.
__________________________________

Cor Ligthert said:
AD,

I assume with a ";" between it.

Cor

ad said:
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.
Click to expand...
Click to expand...
 
Thanks
Could you tell me how to use stirng processing?


Jim Hughes said:
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.

ad said:
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.
Click to expand...
Click to expand...
 
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.
__________________________________

ad said:
Thanks
Could you tell me how to use stirng processing?


Jim Hughes said:
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.

ad said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top