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