Sql-server – Transaction with multiple SQLCMD calls in a batch file

sql serversql server 2014sqlcmdtransaction

Is there a good way to do something like the following?

SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION"
FOR %%f in (script1 script2 script3) do (
    SET CURRENT_SCRIPT=%%f
    SQLCMD -S Localhost -E -b -V 1 -i %%f.sql -o %%f.log
    IF !ERRORLEVEL! NEQ 0 GOTO ERROR
    SET CURRENT_SCRIPT=
)
SQLCMD -S %SERVER% -E -Q "COMMIT TRANSACTION"

Best Answer

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions. You're going to want to combine your scripts into a single script for you to do what you want.

If you can properly order your scripts, the following approach in Powershell will work for you:

$scripts = Get-ChildItem "C:\temp\*.sql" |Sort-Object
$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

foreach($script in $scripts){
    $fullbatch += Get-Content $script
}

$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S localhost -d test -Q "$fullbatch"

Essentially, I read in each script (assuming they are ordered correctly) into a single batch variable, append a BEGIN TRANSACTION and END TRANSACTION to the batch, then execute that back as a query within my sqlcmd execution.

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)
}

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements   
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
      $ret
    }
} catch [system.exception] {
  $_.Exception.ToString()
}

Sql-server – Sql Server 2005 – sqlcmd with output to shell and file

In order to implement what you want you need something functionally equivalent to tee or grep on unix. Windows does ship with a utility called find.exe that has similar functionality to grep, so you may be able to use that to extract relevant lines from your output file. As far as I am aware there is no equivalent to tee shipped with base windows builds.

This sort of thing is trivial in a unix shell scripting environment but can cause issues on a raw Windows build. If you can't implement what you want using find.exe, three basic approaches come to mind:

cmd.exe actually has significant pipelining capabilities, although not as rich as a unix shell. You can get Win32 ports of tee and grep from the GNU Win32 collection, or from Microsoft Services for Unix (SFU). The GNU Win32 ones are better because they use msvcrt.dll and understand native Windows paths. SFU places an emulation layer over the base O/S in much the same way as cygwin, so it emulates /cygdrive/c style paths that behave in a more unix-like manner.

An alternative to this would be to use WSH, powershell or even a small .Net console app to implement a small utility to read the output from the log file and present your prompt to the user.
Reimplement the process in a way that allows the control to be automated. If you can agree business rules for this control it can be run automatically and simply log the result, generating a status report when the process has finished running.
It seems quite strange that you are developing a server-side process that requires interactive input from an operator. If this process has to be run interactively, is there any reason why the interactive parts could not simply be run from somebody's PC? This would probably allow you considerably more flexibility in installing third party components or bespoke client-side code.

Best Answer

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

Sql-server – Sql Server 2005 – sqlcmd with output to shell and file

Related Question