Syntax Error, but Copy/Pasted Query Text Works in SSMS

Question

I am having an issue generating a SQL query using C#. To troubleshoot, I made the button that executes the query also display the query text in a textbox on the form. What's perplexing is that I get an error saying "Incorrect syntax near 'IF'" when the program tries to execute the query, but if I copy/paste the query from the textbox to SSMS it works fine.

The variable that stores the query looks like:

string myQuery = @"  
SELECT DISTINCT filter.id_column INTO #temp1  
FROM MasterDB.dbo.filter filter  
LEFT JOIN ClientDB.dbo.codes codetable  
ON filter.id_column=codetable.id_column  
WHERE codetable.name IS NULL  

DECLARE @code_id1 INT;  
SET @code_id1 = (SELECT MAX(code_num) FROM ClientDB.dbo.codes)+1  
EXEC('ALTER TABLE #temp1 ADD tempID INT IDENTITY(' + @code_id1 + ',1)')  
GO  

IF (SELECT COUNT(*) FROM #temp1)>0  
BEGIN  
DECLARE @code_id2 INT;  
SET @code_id2 = (SELECT MAX(tempID) FROM #temp1)+1  
UPDATE ClientDB.dbo.track  
SET next=@code_id2 WHERE [trackname]='account'  
END";

The C# code to populate the textbox with the query text and then run the query looks like:

using (SqlConnection myConnection = new SqlConnection(HostConnStr))  
using (SqlCommand myCommand = myConnection.CreateCommand())  
{  
    myCommand.CommandText = myQuery;   
    this.textBox1.Text = myCommand.CommandText;  
    myConnection.Open();  
    try { myCommand.ExecuteNonQuery(); }  
    catch (SqlException s) { MessageBox.Show(s.ToString()); }  
    myConnection.Close();  
}

Does anyone know why the query text can be copied to SSMS and run fine, but throws a SQL exception when executed from C#? And how do I make the query run?

Critique on the query design will be appreciated, but I am more concerned with simply getting the query to execute since it does what I need it to do as-is.

EDIT: This may be a duplicate (I was thrown off by the error being near 'IF' when it appears that 'GO' is the problem, so my searches were in the wrong direction. However, I am still not sure that the answers provided in similar questions will work since I am under the impression that splitting the query into multiple commands will fail due to the later part of the query referencing a temporary table in the earlier part (will the temporary table not become unavailable after the first command is finished?).

Possible duplicate of [Execute a large SQL script (with GO commands)](http://stackoverflow.com/questions/40814/execute-a-large-sql-script-with-go-commands) — BWA, Jan 26 '16 at 00:37

Preet Sangha · Accepted Answer · 2016-01-26T22:06:49.870

2

It's the GO statement. You can replace it with ; in most instances.

In TSQL it's OK to have multiple statements separated by GO. In the ADO.NET version you can't do this.

The way to do this would be spilt the string on the GO and execute each independently. Such as this example,

string scriptText = @"...."

//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter,  StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
  //execute commandText
}

edited Jan 26 '16 at 22:06

answered Jan 26 '16 at 00:37

Preet Sangha

64,563
18
145
216

Is there a way to make the query work without the GO statement? If I'm not mistaken the query will not function as intended without it (correct me if I'm wrong) EDIT: If I split the string after the GO statement, will I not lose my temporary table, which gets referenced in the section that follows the GO? – elmer007 Jan 26 '16 at 00:41
Try using a ; instead of the GO but I'm not 100% certain. – Preet Sangha Jan 26 '16 at 00:43
1

I believe that using the semicolon has done the trick- thanks! If you don't mind putting that option in your answer then I'll go ahead and mark it as accepted – elmer007 Jan 26 '16 at 16:19

Syntax Error, but Copy/Pasted Query Text Works in SSMS

1 Answers1