none
How to execute a Multi Line Script (dynamic generated) with GO's in the SQL.

    Question

  •  

    Questions.

    > I have a variable with Multiline content. How do I write this to a File. Am I correct that I was restricted only with SQLcmd due to GO's in the content.

    > How do I execute a MultiLine Script with GO's.

     

     

    Brief description, if needed.

    My primary Goal is to build a script that runs a list of files in a single transaction.. So All can be rollbacked. Each file can contain transactions in them and GO's.

     

    Please confirm by below understandings

    > I cannot dynamically run each file because GO is recognized only by SQLCmd.But SQLCmd recognizes only if passed as a file with -i

    So I had create a single Master SQL Script file that has the content of each file.

     

    Wednesday, May 18, 2011 2:26 AM

Answers

  • Please confirm by below understandings

    > I cannot dynamically run each file because GO is recognized only by SQLCmd.But SQLCmd recognizes only if passed as a file with -i 


    To add on to what Naomi said, GO is not a T-SQL statement; it is a batch terminator recognized by tools lile SQLCMD and SSMS.  I believe the deprecated DMO API would honor GO batch terminators too.

    If your goal is to run the script (with batch terminators) in applciation code, you code read each line individually and execute the preceeding batch whenever a GO line is encountered.  This is basically what the tools do.  Simplified example without error, resultset or message handling:

     

     

     private void runScript(string sqlScript)
     {
      using (var connection = new SqlConnection("Data Source=localhost;Integrated Security=SSPI"))
      {
      connection.Open();
      executeSqlScript(sqlScript, connection);
      connection.Close();
      }
     }
    
     private void executeSqlScript(string sqlScript, SqlConnection connection)
     {
      var command = connection.CreateCommand();
      var sqlScriptReader = new StringReader(sqlScript);
      var sqlBatch = new StringWriter();
      while (sqlScriptReader.Peek() != -1)
      {
      var sqlScriptLine = sqlScriptReader.ReadLine();
      if (sqlScriptLine.Trim().ToUpper() == "GO")
      {
       //execute batch whenever a batch terminator is found
       command.CommandText = sqlBatch.ToString();
       executeBatch(command);
       sqlBatch = new StringWriter();
      }
      else
      {
       sqlBatch.WriteLine(sqlScriptLine);
      }
      }
      executeBatch(command); //in case last line is not a batch terminator
     }
    
     private void executeBatch(SqlCommand command)
     {
      if (command.CommandText.Trim() != string.Empty)
      {
      command.ExecuteNonQuery();
      }
     }
    

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    fix typo
    Wednesday, May 18, 2011 3:36 AM
  • Hello,

    I have an application that runs scripts generated by RedGate tools (with transactions and GOs).

    I use Microsoft.SqlServer.Management Namespace like this (VB.Net) :

     

      Dim ExecutionState As String = String.Empty
      Dim ExecutionMessages As String = String.Empty
    
      Private Sub RunScript(ByVal targetConnectionString As String, ByVal script As String)
        ExecutionState = String.Empty<br/>    ExecutionMessages = String.Empty
        Using cn As New SqlConnection(targetConnectionString)
          Dim server As New Smo.Server(New Common.ServerConnection(cn))
          AddHandler server.ConnectionContext.InfoMessage, AddressOf ServerConnection_InfoMessage
          AddHandler server.ConnectionContext.ServerMessage, AddressOf ServerConnection_ServerMessage
          server.ConnectionContext.ExecuteNonQuery(script)
        End Using
      End Sub
    
      Private Sub ServerConnection_InfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
        ExecutionMessages &= vbCrLf & e.Message
      End Sub
    
      Private Sub ServerConnection_ServerMessage(ByVal sender As Object, ByVal e As Common.ServerMessageEventArgs)
        If e.Error IsNot Nothing Then
          ExecutionMessages &= vbCrLf & e.Error.Message
        End If
      End Sub
    
    

    Wednesday, May 18, 2011 4:29 AM

All replies

  • Your understanding is correct. You can execute this file in SSMS, but not in other applications and not by using dynamic SQL.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, May 18, 2011 2:50 AM
  • Please confirm by below understandings

    > I cannot dynamically run each file because GO is recognized only by SQLCmd.But SQLCmd recognizes only if passed as a file with -i 


    To add on to what Naomi said, GO is not a T-SQL statement; it is a batch terminator recognized by tools lile SQLCMD and SSMS.  I believe the deprecated DMO API would honor GO batch terminators too.

    If your goal is to run the script (with batch terminators) in applciation code, you code read each line individually and execute the preceeding batch whenever a GO line is encountered.  This is basically what the tools do.  Simplified example without error, resultset or message handling:

     

     

     private void runScript(string sqlScript)
     {
      using (var connection = new SqlConnection("Data Source=localhost;Integrated Security=SSPI"))
      {
      connection.Open();
      executeSqlScript(sqlScript, connection);
      connection.Close();
      }
     }
    
     private void executeSqlScript(string sqlScript, SqlConnection connection)
     {
      var command = connection.CreateCommand();
      var sqlScriptReader = new StringReader(sqlScript);
      var sqlBatch = new StringWriter();
      while (sqlScriptReader.Peek() != -1)
      {
      var sqlScriptLine = sqlScriptReader.ReadLine();
      if (sqlScriptLine.Trim().ToUpper() == "GO")
      {
       //execute batch whenever a batch terminator is found
       command.CommandText = sqlBatch.ToString();
       executeBatch(command);
       sqlBatch = new StringWriter();
      }
      else
      {
       sqlBatch.WriteLine(sqlScriptLine);
      }
      }
      executeBatch(command); //in case last line is not a batch terminator
     }
    
     private void executeBatch(SqlCommand command)
     {
      if (command.CommandText.Trim() != string.Empty)
      {
      command.ExecuteNonQuery();
      }
     }
    

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    fix typo
    Wednesday, May 18, 2011 3:36 AM
  • Hello,

    I have an application that runs scripts generated by RedGate tools (with transactions and GOs).

    I use Microsoft.SqlServer.Management Namespace like this (VB.Net) :

     

      Dim ExecutionState As String = String.Empty
      Dim ExecutionMessages As String = String.Empty
    
      Private Sub RunScript(ByVal targetConnectionString As String, ByVal script As String)
        ExecutionState = String.Empty<br/>    ExecutionMessages = String.Empty
        Using cn As New SqlConnection(targetConnectionString)
          Dim server As New Smo.Server(New Common.ServerConnection(cn))
          AddHandler server.ConnectionContext.InfoMessage, AddressOf ServerConnection_InfoMessage
          AddHandler server.ConnectionContext.ServerMessage, AddressOf ServerConnection_ServerMessage
          server.ConnectionContext.ExecuteNonQuery(script)
        End Using
      End Sub
    
      Private Sub ServerConnection_InfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
        ExecutionMessages &= vbCrLf & e.Message
      End Sub
    
      Private Sub ServerConnection_ServerMessage(ByVal sender As Object, ByVal e As Common.ServerMessageEventArgs)
        If e.Error IsNot Nothing Then
          ExecutionMessages &= vbCrLf & e.Error.Message
        End If
      End Sub
    
    

    Wednesday, May 18, 2011 4:29 AM
  • Thanks Naomi, Dan, Foliede,

     

    Dan and Foilede,

    I have 'N' number of such SQL scripts with transactions and GO's.

    So How would you roll back Script 1 to X-1, if Xth Script failed in run-time ? Thats the reason, I am looking for more of a SQL script solution than an .Net executable.

     


    Related question (which would solve my problem).

    > I have a variable with Multiline content. How do I write this content to a single File.

    (I have tried using Xp_CmdShell, but only first line is being executed due to multiple lines in the command being sent.)

    Wednesday, May 18, 2011 5:07 PM
  • How about applying stored procedures?
    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Monday, May 23, 2011 4:49 AM