Running .sql file using C#

Hi,

I'm trying to run .sql file with C# code, but i have systematically an error
message with the "GO" instruction. When i test the script in SQL Server
Management Studio, it work fine !!!

First part of the error message
----------------------------------------
{System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
etc...

SQL file to be played (SET NOEXEC ON is needed !!!)
--------------------------------------------------------------------------------
SET NOEXEC ON
GO
DROP PROCEDURE spr_gr_test
GO
CREATE PROCEDURE spr_gr_test
as
begin
PRINT 'titi'
end
GO

C# code
----------------------------------------
SqlCommand __cmd = myConnection.CreateCommand();
StreamReader __streamReader = null;
string __myQuery = "";
int __rc = 0;

try
{
__streamReader = new
StreamReader(@"C:\Data\Dvlp\SQL.2005\CheckScript\SQLQuery1.sql");

__myQuery = __streamReader.ReadToEnd();
__cmd.CommandType = CommandType.Text;
__cmd.CommandText = __myQuery;
__rc = __cmd.ExecuteNonQuery();
Debug.WriteLine(__rc.ToString());
}
catch (Exception exp)
{
Debug.WriteLine(exp.ToString());
}
finally
{
if (__streamReader != null)
__streamReader.Close();
}

Where is the solution ?
Where is the mistake .....

Thank's by advance

Gislain

John J. Hughes II

Guest

n/a Posts

July 14th, 2006
01:45 PM
#2

Re: Running .sql file using C#

The SQL server does not know what "GO" is. Query analyzer uses the "GO" to
separate the statements but does not send them.

I normally do something like

string[] cmds = command.Replace("GO", "~").split(new Char[] {'~'} );
foreach(string cmd in cmds)
/// exec cmd...

Regards,
John

"Gislain" <nospam@nospam.comwrote in message
news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

Hi,
>
I'm trying to run .sql file with C# code, but i have systematically an
error
message with the "GO" instruction. When i test the script in SQL Server
Management Studio, it work fine !!!
>
>
First part of the error message
----------------------------------------
{System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
etc...
>
SQL file to be played (SET NOEXEC ON is needed !!!)
--------------------------------------------------------------------------------
SET NOEXEC ON
GO
DROP PROCEDURE spr_gr_test
GO
CREATE PROCEDURE spr_gr_test
as
begin
PRINT 'titi'
end
GO
>
>
C# code
----------------------------------------
SqlCommand __cmd = myConnection.CreateCommand();
StreamReader __streamReader = null;
string __myQuery = "";
int __rc = 0;
>
try
{
__streamReader = new
StreamReader(@"C:\Data\Dvlp\SQL.2005\CheckScript\SQLQuery1.sql");
>
__myQuery = __streamReader.ReadToEnd();
__cmd.CommandType = CommandType.Text;
__cmd.CommandText = __myQuery;
__rc = __cmd.ExecuteNonQuery();
Debug.WriteLine(__rc.ToString());
}
catch (Exception exp)
{
Debug.WriteLine(exp.ToString());
}
finally
{
if (__streamReader != null)
__streamReader.Close();
}
>
>
>
>
Where is the solution ?
Where is the mistake .....
>
Thank's by advance
>
Gislain
>
>
>
>

$Ignacio Machin $ .NET/ C# MVP $'s Avatar$

Ignacio Machin $ .NET/ C# MVP $

Guest

n/a Posts

July 14th, 2006
02:05 PM
#3

Re: Running .sql file using C#

Hi,

Do you know if the proc exist before running the script?
If not you better do a IF exist ... before the DROP
other than that it seems ok the script

--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gislain" <nospam@nospam.comwrote in message
news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

Nicholas Paldino [.NET/C# MVP]

Guest

n/a Posts

July 14th, 2006
03:55 PM
#4

Re: Running .sql file using C#

This is really dangerous, as you end up replacing all of the instances
of "go" in the string, which isn't what you want. You might have table
names with the letters "go" in them and the queries will fail as a result.

My recommendation to the OP is to make sure that your GO statements are
on lines by themselves. Then, read the file line for line. If you detect a
"go" all by itself, then you can execute the last batch of commands that you
have.

--
- Nicholas Paldino [.NET/C# MVP]
- Join Bytes!

"John J. Hughes II" <no@invalid.comwrote in message
news:OMEXDv0pGHA.1548@TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

The SQL server does not know what "GO" is. Query analyzer uses the "GO"
to separate the statements but does not send them.
>
I normally do something like
>
>
string[] cmds = command.Replace("GO", "~").split(new Char[] {'~'} );
foreach(string cmd in cmds)
/// exec cmd...
>
Regards,
John
>
>
"Gislain" <nospam@nospam.comwrote in message
news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

>Hi,
>>
>I'm trying to run .sql file with C# code, but i have systematically an
>error
>message with the "GO" instruction. When i test the script in SQL Server
>Management Studio, it work fine !!!
>>
>>
>First part of the error message
>----------------------------------------
>{System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
>Incorrect syntax near 'GO'.
>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
>Boolean breakConnection)
>etc...
>>
>SQL file to be played (SET NOEXEC ON is needed !!!)
>--------------------------------------------------------------------------------
> SET NOEXEC ON
> GO
> DROP PROCEDURE spr_gr_test
> GO
> CREATE PROCEDURE spr_gr_test
> as
> begin
> PRINT 'titi'
> end
> GO
>>
>>
>C# code
>----------------------------------------
> SqlCommand __cmd = myConnection.CreateCommand();
> StreamReader __streamReader = null;
> string __myQuery = "";
> int __rc = 0;
>>
> try
> {
> __streamReader = new
>StreamReader(@"C:\Data\Dvlp\SQL.2005\CheckScript\SQLQuery1.sql");
>>
> __myQuery = __streamReader.ReadToEnd();
> __cmd.CommandType = CommandType.Text;
> __cmd.CommandText = __myQuery;
> __rc = __cmd.ExecuteNonQuery();
> Debug.WriteLine(__rc.ToString());
> }
> catch (Exception exp)
> {
> Debug.WriteLine(exp.ToString());
> }
> finally
> {
> if (__streamReader != null)
> __streamReader.Close();
> }
>>
>>
>>
>>
>Where is the solution ?
>Where is the mistake .....
>>
>Thank's by advance
>>
>Gislain
>>
>>
>>
>>

>
>

John J. Hughes II

Guest

n/a Posts

July 17th, 2006
08:15 PM
#5

Re: Running .sql file using C#

Thanks for the insight, sound like a good idea :)

Regards,
John

"Nicholas Paldino [.NET/C# MVP]" <mvp@spam.guard.caspershouse.comwrote in
message news:OzQdK51pGHA.4996@TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

This is really dangerous, as you end up replacing all of the instances
of "go" in the string, which isn't what you want. You might have table
names with the letters "go" in them and the queries will fail as a result.
>
My recommendation to the OP is to make sure that your GO statements are
on lines by themselves. Then, read the file line for line. If you detect
a "go" all by itself, then you can execute the last batch of commands that
you have.
>
--
- Nicholas Paldino [.NET/C# MVP]
- Join Bytes!
>
"John J. Hughes II" <no@invalid.comwrote in message
news:OMEXDv0pGHA.1548@TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

>The SQL server does not know what "GO" is. Query analyzer uses the "GO"
>to separate the statements but does not send them.
>>
>I normally do something like
>>
>>
>string[] cmds = command.Replace("GO", "~").split(new Char[] {'~'} );
>foreach(string cmd in cmds)
> /// exec cmd...
>>
>Regards,
>John
>>
>>
>"Gislain" <nospam@nospam.comwrote in message
>news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

>>Hi,
>>>
>>I'm trying to run .sql file with C# code, but i have systematically an
>>error
>>message with the "GO" instruction. When i test the script in SQL Server
>>Management Studio, it work fine !!!
>>>
>>>
>>First part of the error message
>>----------------------------------------
>>{System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
>>Incorrect syntax near 'GO'.
>>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
>> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
>>Boolean breakConnection)
>>etc...
>>>
>>SQL file to be played (SET NOEXEC ON is needed !!!)
>>--------------------------------------------------------------------------------
>> SET NOEXEC ON
>> GO
>> DROP PROCEDURE spr_gr_test
>> GO
>> CREATE PROCEDURE spr_gr_test
>> as
>> begin
>> PRINT 'titi'
>> end
>> GO
>>>
>>>
>>C# code
>>----------------------------------------
>> SqlCommand __cmd = myConnection.CreateCommand();
>> StreamReader __streamReader = null;
>> string __myQuery = "";
>> int __rc = 0;
>>>
>> try
>> {
>> __streamReader = new
>>StreamReader(@"C:\Data\Dvlp\SQL.2005\CheckScript\SQLQuery1.sql");
>>>
>> __myQuery = __streamReader.ReadToEnd();
>> __cmd.CommandType = CommandType.Text;
>> __cmd.CommandText = __myQuery;
>> __rc = __cmd.ExecuteNonQuery();
>> Debug.WriteLine(__rc.ToString());
>> }
>> catch (Exception exp)
>> {
>> Debug.WriteLine(exp.ToString());
>> }
>> finally
>> {
>> if (__streamReader != null)
>> __streamReader.Close();
>> }
>>>
>>>
>>>
>>>
>>Where is the solution ?
>>Where is the mistake .....
>>>
>>Thank's by advance
>>>
>>Gislain
>>>
>>>
>>>
>>>

>>
>>

>
>