Error trying to execute SQL Server script file via ExecuteNonQuery on VB.NET

Question

The script works great on SQL Management Studio, it just doesn't on the program. The script file checks if certain stored procedures exist, drops them if they do, and next declares them again, like this:

IF EXISTS(SELECT * FROM sys.objects WHERE type='P' AND name = 'myProc1')
  DROP PROCEDURE myProc1
GO
CREATE PROCEDURE myProc1
AS
BEGIN
  SELECT 
    [Field1] = ((*Some numeric value*)-(*Some numeric value*)),
    [Field2] = ((*Some numeric value*)-(*Some numeric value*)),
    [Field3] = ((*Some numeric value*)-(*Some numeric value*))
  FROM ...
  WHERE ...
END
GO

IF EXISTS(SELECT * FROM sys.objects WHERE type='P' AND name = 'myProc2')
  DROP PROCEDURE myProc2
GO
CREATE PROCEDURE myProc2
AS
BEGIN
  SELECT * FROM Table2
END
GO

IF EXISTS(SELECT * FROM sys.objects WHERE type='P' AND name = 'myProc3')
  DROP PROCEDURE myProc3
GO
CREATE PROCEDURE myProc3
AS
BEGIN
  SELECT * FROM Table3
END
GO

And when I try to run it on VB.NET I tried this:

sql.CommandText = File.OpenText("C:\test.sql").ReadToEnd()
sql.CommandType = CommandType.Text
sql.Connection = connects.con
sql.ExecuteNonQuery()

And I get this error:

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near ')'.
Incorrect syntax near '-'.
Incorrect syntax near ')'.
Incorrect syntax near '-'.
Incorrect syntax near ')'.
Incorrect syntax near '-'.
Incorrect syntax near '�'.

So at least 3 GOs, the 3 operations, and even some unknown character.

Any ideas on what I'm doing wrong?

score 1 · Answer 1 · answered Mar 11 '15 at 16:51

I know this is an old one but I do this alot and to get around it I generate scripte for the stored procedures form SQL Management studio and under advanced I tell it to "check for object existence" SQL 2010+ (pre-2010 was check object exist or something simular) and also set "Script DROP and CREATE" to "Script DROP and CREATE".

Then just take out the "GO"'s (I usually replace-all GO with blank with match case on or go through one by one clicking replace so I know it's the correct GO's being replaced) from the script and it'll run perfect in VB.NET/t-SQL.

This means it generates a script like this:

/****** Object:  StoredProcedure [dbo].[test3]    Script Date: 11/03/2015 16:47:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test3]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test3]

/****** Object:  StoredProcedure [dbo].[test2]    Script Date: 11/03/2015 16:47:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test2]

/****** Object:  StoredProcedure [dbo].[test1]    Script Date: 11/03/2015 16:47:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[test1]

/****** Object:  StoredProcedure [dbo].[test1]    Script Date: 11/03/2015 16:47:48 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[test1]

AS
BEGIN
    print ''dummy procedure 1''
END
' 
END

/****** Object:  StoredProcedure [dbo].[test2]    Script Date: 11/03/2015 16:47:48 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[test2]

AS
BEGIN
    print ''dummy procedure 2''
END
' 
END

/****** Object:  StoredProcedure [dbo].[test3]    Script Date: 11/03/2015 16:47:48 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test3]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[test3]

AS
BEGIN
    print ''dummy procedure 3''
END
' 
END

I started doing this around 5 years ago and never had any issues

Hope this helps people

Regards

Liam

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

The GO command is a Batch separator for SMSS and is not part of T-SQL command. Your script then will work in SSMS because it will execute each batch. On the other hand the .Net Framework pass the script to the underlying SQL engine and consider it as ONE big script not separate batches which should be and there therefore lies your error.

So creating three Stored Procedures in one Command would not work.

My suggestion is you create the script on your back-end separately and then your test.sql should look like this:

  EXEC myProc1
  EXEC myProc2
  EXEC myProc3

However, if you want to create three stored procedure on the fly then you probably just separate each CREATE PROCEDURE into three different sql text file, namely, test1.sql, test2.sql and test3.sql for myProc1, myProc2 and myProc3 respectively.

edited May 23 '17 at 11:50

Community

1
1

answered Dec 14 '13 at 00:59

Edper

9,144
1
27
46

Yeah, I'm not trying to execute them though, I need the program to create and 'update' them as necessary, the sql script is supposed to change over time, thus so will the SPs. And my main concern is the fact that I can succesfully run the whole script in Management Studio, there should be no problem as far as I believe because each SP is in a specific batch, I know they couldn't be declared together. – user1676874 Dec 14 '13 at 01:03
The `.Net framework` get the script and pass it to the underlying SQL engine right away while the `Management Studio` parse the script with each `GO` statement separately and thus you could do multiple `CREATE PROCEDURE`. Please don't confuse the `SSMS` with the `.Net Framework`. – Edper Dec 14 '13 at 01:39
Is there a `GO` substitute? – user1676874 Dec 14 '13 at 01:48
I think you could use `;` instead of `GO` but it will not work still if you call it from your program. Anyway, see my updated answer above. – Edper Dec 14 '13 at 02:04
I understand your answer, and it does make sense. The thing is this sql script is intended so we're able to modify the stored procedures in the database without having to update the program itself(like specifying the new scripts to execute when new ones were added), but rather just the sql. Plus if I declared each SP in one sql script each, I'd still have to check if they exist before creating them, thus not having only the SP on the script. – user1676874 Dec 14 '13 at 17:07

Error trying to execute SQL Server script file via ExecuteNonQuery on VB.NET

2 Answers2