none
[C# / SMO] ExecuteWithResults with GO Statement RRS feed

  • Question

  • Hello,

    I'm using SQL Server SMO v150.18208.0 (installed from Nuget) in my C# application.

    I've been using server.ConnectionContext.ExecuteNonQuery to run SQL scripts that contain GO statements, and it works great.

    Recently, I also discovered ExecuteWithResults methods which works (in order to get SELECT result sets), but only when the script doesn't have any GO statement, otherwise, I run into "Incorrect syntax near 'GO'" error.

    Is this something normal or is it a bug ? If not, is there a way to avoid this behavior ?

    Thanks.


    Tuesday, December 3, 2019 10:37 AM

All replies

  • One idea is to read in the file containing SQL, loop through each line, if the line doesn't contain a GO copy it to a List<string> and if contains GO do not copy it then save the list into a new file e.g. IO.File.WriteAllLines. Then execute the new script file.

    using System.Globalization;
    using System.IO;
    using System.Linq;
    
    namespace WindowsFormsApplication1
    {
        public class Operations
        {
            public void CreateNewScriptFile(string inFile, string outFile) 
            {
                var inContents = File.ReadAllLines(inFile);
    
                File.WriteAllLines(outFile, inContents
                    .Where(line => !string.IsNullOrWhiteSpace(line))
                    .Where(line => CultureInfo.InvariantCulture
                                       .CompareInfo.IndexOf(line, "go", CompareOptions.IgnoreCase) == -1)
                    .ToArray());
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, December 3, 2019 11:49 AM
    Moderator
  • Hi Karen,

    Thanks for your answer, but I'd prefer a more "SMO oriented solution" from the development team.

    Tuesday, December 3, 2019 2:12 PM
  • Hi Karen,

    Thanks for your answer, but I'd prefer a more "SMO oriented solution" from the development team.

    If the scripts were created via SMO then for the Scripter set .NoCommandTerminator to true for no GO e.g.

    Target a specific database named "School" and perform schema scripting along with insert statements.

    public List<string> ScriptDatabaseTables()
    {
        var fileNames = new List<string>();
        var server = new Server(ServerName);
    
        var scripter = new Scripter 
        {
            Server = server, Options =
            {
                ScriptData = true,
                ScriptSchema = true,
                ToFileOnly = true,
                NoCommandTerminator = true
            }
        };
    
    
        var database = server.Databases["School"];
    
        var tables = database.Tables.OfType<Table>().Where((tbl) => (!tbl.IsSystemObject));
    
        foreach (var table in tables)
        {
            scripter.Options.FileName = Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory, table.Name + ".txt");
    
            fileNames.Add(Path.GetFileName(scripter.Options.FileName));
            scripter.EnumScript(new SqlSmoObject[] { table });
        }
    
        return fileNames;
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, December 3, 2019 3:03 PM
    Moderator
  • Hi, 

    Sorry for getting back so late... I think there was a misunderstanding of my need. I don't want to script my dadabase using SMO, I want to execute a lot of scripts against my DB with SMO methods.

    Here is my example :

    - GetResultSet is a boolean that indicates wether if I want to get a DataSet from my stored procedure or not

    - fileContent is a string that contains the whole SQL code loaded from a file.

    - sw is my StreamWriter


    if (!GetResultSet)
    {
        server.ConnectionContext.ExecuteNonQuery(fileContent);
    }
    else
    {
        DataSet results = server.ConnectionContext.ExecuteWithResults(fileContent);
    
        if (results != null)
        {
            foreach (DataTable dt in results.Tables)    
            { 
                DataSetHelper.WriteToStreamWriter(dt, sw);
                sw.WriteLine();
            }
        }
    }

    When using ExecuteNonQuery with fileContent which has this at the beginning, it all goes well :

    "USE MyDB 

    GO

    --My Instructions
    --My SELECT statement"

    When I do the same with ExecuteWithResults, it crashes.

    So, that's why I think it's a bug, unless there is some technical limitation I'm not aware of...

    Thanks.


    • Edited by MushKPN Thursday, January 23, 2020 10:22 AM
    Thursday, January 23, 2020 9:35 AM
  • Hi, 

    Sorry for getting back so late... I think there was a misunderstanding of my need. I don't want to script my dadabase using SMO, I want to execute a lot of scripts against my DB with SMO methods.

    Here is my example :

    - GetResultSet is a boolean that indicates wether if I want to get a DataSet from my stored procedure or not

    - fileContent is a string that contains the whole SQL code loaded from a file.

    - sw is my StreamWriter


    if (!GetResultSet)
    {
        server.ConnectionContext.ExecuteNonQuery(fileContent);
    }
    else
    {
        DataSet results = server.ConnectionContext.ExecuteWithResults(fileContent);
    
        if (results != null)
        {
            foreach (DataTable dt in results.Tables)    
            { 
                DataSetHelper.WriteToStreamWriter(dt, sw);
                sw.WriteLine();
            }
        }
    }

    When using ExecuteNonQuery with fileContent which has this at the beginning, it all goes well :

    "USE MyDB 

    GO

    --My Instructions
    --My SELECT statement"

    When I do the same with ExecuteWithResults, it crashes.

    So, that's why I think it's a bug, unless there is some technical limitation I'm not aware of...

    Thanks.


    I need to know what the error message is, use a try-catch and place a console writeline in the catch, copy the information and post back here.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 23, 2020 11:59 AM
    Moderator
  • That's the message : 

    An exception occurred while executing a Transact-SQL statement or batch. - Incorrect syntax near 'GO'.

    If I remove the GO statement, the ExecuteWithResults methods works fine

    Thursday, January 23, 2020 12:10 PM
  • That's the message : 

    An exception occurred while executing a Transact-SQL statement or batch. - Incorrect syntax near 'GO'.

    If I remove the GO statement, the ExecuteWithResults methods works fine

    As I suspected and mentioned before, GO definition from the first line of it's documentation.

    SQL Server provides commands that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

    Which means you need to use my suggestion and write to a new file without the GO statements. Also it would be prudent to end each command with a semicolon which indicates the end of a command.

    How do I know this? from read the documentation and from doing a great deal with TSQL and SMO over the years.

    using System.Globalization;
    using System.IO;
    using System.Linq;
    
    namespace WindowsFormsApplication1
    {
        public class Operations
        {
            public void CreateNewScriptFile(string inFile, string outFile) 
            {
                var inContents = File.ReadAllLines(inFile);
    
                File.WriteAllLines(outFile, inContents
                    .Where(line => !string.IsNullOrWhiteSpace(line))
                    .Where(line => CultureInfo.InvariantCulture
                                       .CompareInfo.IndexOf(line, "go", CompareOptions.IgnoreCase) == -1)
                    .ToArray());
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 23, 2020 12:39 PM
    Moderator