2

I have a SQL script with GO batch separators that I am trying to execute from C# code. When I use Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery, it works great. It blows up on Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader or Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults, though.

System.AggregateException: One or more errors occurred. --->
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. --->
System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.

It's a very simple script that looks like this:

SELECT
  DB_NAME()
AS
  'Database Name';
GO

EXEC [schema].[MyStoredProcedure];
GO

SELECT
  [Column]
AS
  'Friendly Column Name'
FROM
  [schema].[Table];
GO

I have tried script.Replace("\n", "\r\n") and I get the same results. Does anyone know how I can execute a script like this and get the results back?

Dan Forbes
  • 2,734
  • 3
  • 30
  • 60
  • 1
    GO isn't an actual SQL statement. It's a batch separator used by some sql utilities. See [here](http://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio). So basically GO isn't valid SQL. You should put all those queries, stored procedure calls in a single stored procedure – Dustin Hodges Nov 03 '15 at 16:25
  • Thanks for your response, @DustinHodges! Please see [John Galloway's blog post entitled "Handling "GO" Separators in SQL Scripts - the easy way"](http://weblogs.asp.net/jongalloway//Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way) to learn more about how the SMO library can help obfuscate the problem you describe. – Dan Forbes Nov 03 '15 at 16:32
  • Checked out that response. The key there is he is using ExecuteNonQuery which doesn't return any results. As you saw in your problem, ExecuteNonQuery can work with a batch separator but if you want to get results back, you are out of luck with a batch separator – Dustin Hodges Nov 03 '15 at 16:40
  • I'm not sure if that's correct. Have you seen [the Microsoft Connect feedback page entitled "Error when using ExecuteWithResults SMO Database Object Method"](http://connect.microsoft.com/SQLServer/feedbackdetail/view/192409/error-when-using-executewithresults-smo-database-object-method)? – Dan Forbes Nov 03 '15 at 16:50
  • Per my comment above, the `Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults` method results in the same error. – Dan Forbes Nov 03 '15 at 16:51
  • 1
    Workarounds would be to create multiple scripts and execute them individually, or something like this ps example, splitting the string on GO. http://robinosborne.co.uk/2014/10/13/getting-past-powershell-sqls-incorrect-syntax-near-go-message/ String splitting is a bit agricultural though. The main reason to use GO is if you have DDL statements, and you don't really want results with those, hence why ExecuteNonQuery works. I think the main reason ExecuteWithResults doesn't work with GO is because the datasets returned would be unpredictable, so c# can't be expected to handle every case. – Davos Jan 13 '16 at 00:36

1 Answers1

-2

ExecuteReader and ExecuteWithResults don't recognize the batch separator GO. The easiest thing to do would be just remove the GOs in the script and call ExecuteWithResults

Script

SELECT
  DB_NAME()
AS
  'Database Name';

EXEC [schema].[MyStoredProcedure];

SELECT
  [Column]
AS
  'Friendly Column Name'
FROM
  [schema].[Table];

Code

DataSet ds = Database.ExecuteWithResults(script);
DataTable dbNameTable = ds.Tables[0]; //Select DB_NAME()
DataTable storedProcedureResults = ds.Tables[1]; //Exec [schema].[MyStoredProcedure]
DataTable queryResults = ds.Tables[2];  //Select [Column] From [Schema].[Table]
Dustin Hodges
  • 4,110
  • 3
  • 26
  • 41
  • According to [this Microsoft Connect feedback page](http://connect.microsoft.com/SQLServer/feedbackdetail/view/192409/error-when-using-executewithresults-smo-database-object-method), the `Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults` method should recognize the `GO` batch separator, however, I'm still having trouble getting it to work. – Dan Forbes Nov 03 '15 at 16:53
  • Firstly, that issue is from 2006 and there isn't hardly any discussion and the bug says he only gets the error when using nolock. Secondly, I don't think it applies because only one datatable is being returned. Thirdly, the article you linked in your comment to your question specifically states that the batch separator only works with ExecuteNonQuery which your results show. You seem to desperately want to keep the non-SQL GO batch separator in a SQL query for reasons that are not clear to me. Did you try my solution? Did it not work? – Dustin Hodges Nov 03 '15 at 17:00
  • The issue indicates that `GO` batch separators should be handled. In fact, the `BatchSeparator` property [is well documented](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.batchseparator). I'm not sure why the number of data tables affects the example. I'm not sure if you read the comments on Galloway's blog post, but one specifically states `ExecuteWithResults` should work. When you say "solution" are you referring to your answer above? That is not a solution to my problem, "Execute SQL Script with GO Batch Separators and Read Results." – Dan Forbes Nov 03 '15 at 17:08
  • I guess there's no help for you. The error you are getting is pretty clear, Incorrect syntax near GO. You provide documentation to a property and provide no indication of how that is supposed to apply here. You reference an article from 9 years ago with a comment saying it should work. However the only mention of ExecuteWithResults doesn't say that it works with the batch separator, only that ExecuteImmediate "seems to work". Good luck with trying to get this to work, but you could have saved yourself a whole bunch of time just removing the GO's and getting on with your life. – Dustin Hodges Nov 03 '15 at 17:17