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

pass variables to the sql

Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SMYou can do this by saving all your update statments to .sql or .txt file.
so when ever u need to run the update just call the .sql or .txt file from Sql prompt

SQl>@.xx.sql or
SQl>@.c:\orawin\bin\aa.txt; Make sure you call the file from correct place.

If you want the values to be changed every time.U can do this as follws

UPDATE PS_TEST_TBL
SET TESTFIELD1 = &TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A; &TEST1A for numeric values and '&TEST1A' for char

So the system will prompt :Enter value for TEST1A

Originally posted by meelagupta
Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SM|||Another way would be to create a stored procedure with the update statements, and pass the values in as parameters.