4

I have a set of sql scripts which I send to SQL server using a SqlCommand object in C#. These scripts create stored procedures and as long as I just create the procedures, everything works finde. If my scripts contain the usual "if exists ... drop XYZ; create procedure XYZ ..." block, I get an error which tells me, that create must be the first statement in a batch. Neither semicolon nor "GO" work as separator.

Any hint how to execute such a script using a single SqlCommand? I have expected to be able to set a property to "Batch" or something like that, but I did not found anything.

Scott W
  • 9,742
  • 2
  • 38
  • 53
Achim
  • 15,415
  • 15
  • 80
  • 144

3 Answers3

3

The conflicting statements must either be separated by a batch separator (default GO - which you say doesn't work), or, if possible from the point of view of your program logic executed in a different order. However, in most case restructuring of the order of statements will not be possible so that you have to resort to the separation in different batches so I would suggest running them in different batches.

ajdams
  • 2,276
  • 14
  • 20
  • I tried this a while ago, and essentially split the string on the word "GO", then looped over and executed the statements – amarsuperstar Jun 30 '10 at 23:18
  • Not the answer I wanted to here, but probably the correct one. Splitting at "GO" is the solution I'll use. – Achim Jul 01 '10 at 10:40
1

If you want to submit a large sql script with batches in it, you should use the SMO. This gives you a lot more control over how the scripts do their thing. And doesn't rely on custom sql script shredding logic (do I need to get into the issues with this?)

http://msdn.microsoft.com/en-us/library/ms212724.aspx

Casey
  • 11
  • 1
0

; should work fine, as seen in http://www.java2s.com/Code/CSharp/Database-ADO.net/ExecutemultipleSQLstatementsusingaSqlCommandobject.htm

But you could use a stored procedure for this task which would make things easier.

Scoregraphic
  • 7,110
  • 4
  • 42
  • 64