SQL CMD | <jco-blog/>

SQL CMD tokens are replaced with their literal values prior TSQL parsing, so they can be used in places where a @ variable wouldn’t be permitted. Typically used for running queries within the SQL Server Management Studio, but such queries can be processed externally by calling SQLCMD.EXE (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE). The literals are not scoped by GO statements like an @ variable and they can be re-defined later in the TSQL

Enable SQL CMD processing from the SQL Server Management Studio

Example SQL

:setvar DB "ADatabase"
:setvar NUMBER "10"

select top $(NUMBER) * from [$(DB)].sys.tables order by name
go
:setvar DB "AnotherDatabase"
select top $(NUMBER) * from [$(DB)].sys.tables order by name

:setvar DB "ADatabase"

:setvar NUMBER "10"

select top $(NUMBER) * from [$(DB)].sys.tables order by name

:setvar DB "AnotherDatabase"

select top $(NUMBER) * from [$(DB)].sys.tables order by name

Example C# – Using SQLCMD.EXE to run scripts

private bool processExited = true;
private StringBuilder sqlCmdStdError = null;
private StringBuilder sqlCmdStdOutput = null;
        
private void ExecuteScriptFileViaSqlCmdUtility(string connectionString, string scriptPath, string outputPath)
{
  const string exe = "sqlcmd.exe"; //C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE
  try
  {
      // open the sql script file
      SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(connectionString);

      System.Diagnostics.Debug.Write(string.Format("\t\t\tConnection = '{0}.{1}'", ssb.DataSource, ssb.InitialCatalog));
      string arguments = String.Format("-b {0} {1} -S {2} -d {3} -I -r1 -i \"{4}\"", //-o \"{5}\"
          !string.IsNullOrEmpty(ssb.UserID) ? "-U " + ssb.UserID : null,
          !string.IsNullOrEmpty(ssb.Password) ? "-P " + ssb.Password : null,
          ssb.DataSource,
          ssb.InitialCatalog,
          scriptPath,
          outputPath);

      using (Process proc = new Process())
      {
          string sqlout = string.Empty;
          proc.StartInfo.FileName = exe; 
          proc.StartInfo.Arguments = arguments;
          proc.StartInfo.UseShellExecute = false;
          proc.StartInfo.RedirectStandardOutput = true;
          proc.StartInfo.RedirectStandardError = true;
          proc.StartInfo.CreateNoWindow = true;
          proc.Exited += new EventHandler(ProcessExited);
          proc.EnableRaisingEvents = true;
          processExited = false;
          proc.OutputDataReceived += (sender, args) => WriteStdOutput(args.Data);
          proc.ErrorDataReceived += (sender, args) => WriteStdError(args.Data);
          proc.Start();
          proc.BeginOutputReadLine();
          proc.BeginErrorReadLine();

          // Synchronously read the standard output of the spawned process.
          if (!proc.WaitForExit(2000))
          {
              while (true)
              {
                  if (proc == null || proc.WaitForExit(100) || processExited) break;
              }
          }
          bool hasExited = proc.HasExited;

          int code = proc.ExitCode;
          proc.Close();

          if (sqlCmdStdOutput != null)
          {
              string results = sqlCmdStdError.ToString();
              if (!string.IsNullOrEmpty(results))
              {
                  System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");
                  System.Diagnostics.Debug.Write(string.Format(results));
                  System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");
              }
          }

          if (sqlCmdStdError != null)
          {
              string errors = sqlCmdStdError.ToString();
              if (!string.IsNullOrEmpty(errors))
              {
                  if (Regex.IsMatch(errors, "Msg .*, Level .*, State .*, Server .*")
                      || errors.IndexOf("Error") >= 0
                      || errors.IndexOf("Invalid") >= 0)
                  {
                      System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");
                      System.Diagnostics.Debug.Write(string.Format(errors));
                      System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");
                      throw new Exception(errors);
                  }
              }
          }
      }
  }
  catch (Exception ex)
  {
      throw new Exception(string.Format("Execute script file (via SqlCmd) failed @ '{0}'.\n\nError = {1}.\n\n", scriptPath, ex.Message), ex);
  }
}

void WriteStdError(string content)
{
  if (sqlCmdStdError == null) sqlCmdStdError = new StringBuilder();
  sqlCmdStdError.Append(content);
}

void WriteStdOutput(string content)
{
  if (sqlCmdStdOutput == null) sqlCmdStdOutput = new StringBuilder();
  sqlCmdStdOutput.Append(content);
}

private void ProcessExited(Object source, EventArgs e)
{
  processExited = true;
}

100

101

102

103

private bool processExited = true;

private StringBuilder sqlCmdStdError = null;

private StringBuilder sqlCmdStdOutput = null;

private void ExecuteScriptFileViaSqlCmdUtility(string connectionString, string scriptPath, string outputPath)

{

const string exe = "sqlcmd.exe"; //C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE

try

{

// open the sql script file

SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(connectionString);

System.Diagnostics.Debug.Write(string.Format("\t\t\tConnection = '{0}.{1}'", ssb.DataSource, ssb.InitialCatalog));

string arguments = String.Format("-b {0} {1} -S {2} -d {3} -I -r1 -i \"{4}\"", //-o \"{5}\"

!string.IsNullOrEmpty(ssb.UserID) ? "-U " + ssb.UserID : null,

!string.IsNullOrEmpty(ssb.Password) ? "-P " + ssb.Password : null,

ssb.DataSource,

ssb.InitialCatalog,

scriptPath,

outputPath);

using (Process proc = new Process())

{

string sqlout = string.Empty;

proc.StartInfo.FileName = exe;

proc.StartInfo.Arguments = arguments;

proc.StartInfo.UseShellExecute = false;

proc.StartInfo.RedirectStandardOutput = true;

proc.StartInfo.RedirectStandardError = true;

proc.StartInfo.CreateNoWindow = true;

proc.Exited += new EventHandler(ProcessExited);

proc.EnableRaisingEvents = true;

processExited = false;

proc.OutputDataReceived += (sender, args) => WriteStdOutput(args.Data);

proc.ErrorDataReceived += (sender, args) => WriteStdError(args.Data);

proc.Start();

proc.BeginOutputReadLine();

proc.BeginErrorReadLine();

// Synchronously read the standard output of the spawned process.

if (!proc.WaitForExit(2000))

{

while (true)

{

if (proc == null || proc.WaitForExit(100) || processExited) break;

}

bool hasExited = proc.HasExited;

int code = proc.ExitCode;

proc.Close();

if (sqlCmdStdOutput != null)

{

string results = sqlCmdStdError.ToString();

if (!string.IsNullOrEmpty(results))

{

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");

System.Diagnostics.Debug.Write(string.Format(results));

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");

}

if (sqlCmdStdError != null)

{

string errors = sqlCmdStdError.ToString();

if (!string.IsNullOrEmpty(errors))

{

if (Regex.IsMatch(errors, "Msg .*, Level .*, State .*, Server .*")

|| errors.IndexOf("Error") >= 0

|| errors.IndexOf("Invalid") >= 0)

{

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");

System.Diagnostics.Debug.Write(string.Format(errors));

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");

throw new Exception(errors);

}

catch (Exception ex)

{

throw new Exception(string.Format("Execute script file (via SqlCmd) failed @ '{0}'.\n\nError = {1}.\n\n", scriptPath, ex.Message), ex);

}

void WriteStdError(string content)

{

if (sqlCmdStdError == null) sqlCmdStdError = new StringBuilder();

sqlCmdStdError.Append(content);

}

void WriteStdOutput(string content)

{

if (sqlCmdStdOutput == null) sqlCmdStdOutput = new StringBuilder();

sqlCmdStdOutput.Append(content);

}

private void ProcessExited(Object source, EventArgs e)

{

processExited = true;

}