CODEWITHSUNDEEP
sql-serverpowershelltransactions
Piotr Falkowski
Piotr Falkowski

Reputation: 1967

Running SQL batches separated by GO in transaction from Powershell

I need to run the contents of sql file against the server inside transaction. The file contains batches separated by GO statements, so T-SQL TRY-CATCH ROLLBACK is no go.

The Invoke-Sqlcmd -InputFile works perfectly, except while used in conjunction with System.Transactions.TransactionScope the exception states only that transaction was aborted, not outputing useful info (checked inner exceptions, nothing).

So I tried using System.Data.SqlClient.SqlConnection transaction in conjunction with System.Data.SqlClient.SqlCommand, which works great, but chokes on GO statements.

The optimal solution sems to be to use Microsoft.SqlServer.Management.Smo.Server object, but I have a weird problem, that it treats every line as separate batch.

The code:

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($databaseServer)
$server.ConnectionContext.ConnectionString = $connectionString


$script = Get-Content "$sqlScriptsPath\$scriptName.sql"
try
{
   $server.ConnectionContext.BeginTransaction()
   $server.ConnectionContext.ExecuteNonQuery($script)
   $server.ConnectionContext.CommitTransaction()
}
catch
{
   $server.ConnectionContext.RollBackTransaction()
   throw
}

When the file consists only of single line statements (i'm testing it with inserts), it works ok, but whenever the command is multiline, it chokes outputing something like "Incorrect syntax near ')'." or something, depending on the context.

ex.: 

DECLARE @msg varchar(50) = 'test'
PRINT @msg

will throw "Must declare the scalar variable "@msg"."

But when the file has both commands in one line separated by ';' it works.

I tried the obvious: $server.ConnectionContext.BatchSeparator = 'GO' but it changes nothing, as 'GO' is already the default separator.

Upvotes: 1

Views: 2766

Answers (2)

Piotr Falkowski
Piotr Falkowski

Reputation: 1967

The issue is that the cmdlet Get-Content used without -Raw flag returns an array of lines.

Solution:

$script = Get-Content "$sqlScriptsPath\$scriptName.sql" -Raw

Upvotes: 1

HAL9256
HAL9256

Reputation: 13483

What I've done in the past is to split out the statements on the PowerShell side of things and execute each statement individually. The easy way I've done is to split out the statements with the separator being the "GO" keyword e.g.:

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($databaseServer)
$server.ConnectionContext.ConnectionString = $connectionString


$script = Get-Content "$sqlScriptsPath\$scriptName.sql"

$ScriptArr = $script -split "GO"

$ScriptArr | foreach {
   try
   {
      $server.ConnectionContext.BeginTransaction()
      $server.ConnectionContext.ExecuteNonQuery($_)
      $server.ConnectionContext.CommitTransaction()
   }
   catch
   {
      $server.ConnectionContext.RollBackTransaction()
      throw
   }
}

This will split the input code by the "GO" statement, and because of the split statement, the split string will not contain the "GO" statement.

Upvotes: 1

Related Questions