Executing a text stream in SQL Server

[Guest]

Hi

I have an issue when executing a t-sql statement from a text file that includes go statements. It appears that when loaded into memory and the text is used as the CommandText property of the SqlCommand object, it doesn't like go statements
I think it does something like exec('use pubs go') and this will obviously fail and say incorrect statement near line ..
Is there a way to send the data through ADO.NET to bypass this.

Note: I know I can use isql.exe and run the text files individually through the command interpreter, but not everyone has the SQL Server client tools installed :

thanks in advance for any help

 

[Patrice Scribe]

Note sure but what if you try ; instead of go ?

Patrice

--

Hi,

I have an issue when executing a t-sql statement from a text file that

includes go statements. It appears that when loaded into memory and the
text is used as the CommandText property of the SqlCommand object, it
doesn't like go statements.

I think it does something like exec('use pubs go') and this will

obviously fail and say incorrect statement near line ...

Is there a way to send the data through ADO.NET to bypass this.

Note: I know I can use isql.exe and run the text files individually

through the command interpreter, but not everyone has the SQL Server client
tools installed

 

[Guest]

I figured out a workaround....since the go seperates everything into batches anyway using the same connection, I can execute them seperately

So I decided to use the following psuedo code to break the text stream into multiple sub batches
For example

string test = {get string from StreamReader.ReadToEnd()
char [] delimiter = ("\r\ngo").ToCharArray(); //use the enter plus g

[]string batches = test.split(delimiter

for( int i = 0; i != batches.length; i++

cmd.CmdText = batches
cmd.ExecuteNonQuery()
}

 

[Lloyd Sheen]

While what you did is most likely the best method, what provider are you
using?

I just tried a test with a little app I have that takes the input from an
editor (all the text) and executes using the SQL Server DotNet Provider
(SQLClient). This executes all batches without problems. I have yet to
test what happens when there is a syntax or execution error in one of the
batches.

Lloyd Sheen

I figured out a workaround....since the go seperates everything into

batches anyway using the same connection, I can execute them seperately.

So I decided to use the following psuedo code to break the text stream into multiple sub batches
For example:

string test = {get string from StreamReader.ReadToEnd() }
char [] delimiter = ("\r\ngo").ToCharArray(); //use the enter plus go

[]string batches = test.split(delimiter)

for( int i = 0; i != batches.length; i++)
{
cmd.CmdText = batches;
cmd.ExecuteNonQuery();
}