Passing DML to From .NET SQLClient?

Hi All,
I am trying to figure out why the following fails and how to fix it:
I have a .NET Client application that passes parameters to stored procedures
and uses dynamic SQL that is passed to the Server. Everything is great. My
problem is if I want to send over to the server several DML in one string.
e.g.
----
USE Master
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
DROP PROC sp_XXXXX'
GO
Create Procedure sp_XXXXX
..
..
..
etc...
GO
----
Passing this type of string to SQL fails. Get error regarding the "GO" key
words, "Create Procedure Must be the first statement, etc...
Is it not possible to pass Multiple DML statements to SQL Server in one
string? If I send each DML statement alone, without the "GO" key word it
does work; but not all together.
Thanks for any insight,
John.GO is not a Transact-SQL statement. It's a batch delimiter used by tools
like OSQL and Query Analyzer. One method to execute scripts containing GOs
in your client app is to parse the script and execute batches individually
when a GO is encountered. See the link below for an example. Another
method is to use SQL-DMO to execute scripts.
[url]http://groups.google.com/group/comp.databases.ms-sqlserver/msg/3e7809e7eeb4cc95[/u
rl]
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <jrugo@.patmedia.net> wrote in message
news:ujWlCQZzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to figure out why the following fails and how to fix it:
> I have a .NET Client application that passes parameters to stored
> procedures and uses dynamic SQL that is passed to the Server. Everything
> is great. My problem is if I want to send over to the server several DML
> in one string.
> e.g.
> ----
> USE Master
> GO
> IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
> DROP PROC sp_XXXXX'
> GO
> Create Procedure sp_XXXXX
> ..
> ..
> ..
> etc...
> GO
> ----
> Passing this type of string to SQL fails. Get error regarding the "GO"
> key words, "Create Procedure Must be the first statement, etc...
> Is it not possible to pass Multiple DML statements to SQL Server in one
> string? If I send each DML statement alone, without the "GO" key word it
> does work; but not all together.
> Thanks for any insight,
> John.
>sql