invoke-sqlcmd for multiple files in single transaction using powershell

Question

Found plenty of examples online how to do the following in powershell:

Get-ChildItem $databaseFolder -Filter *.sql -Recurse | ForEach-Object { sqlcmd -S  $databaseServer -d $databaseName -E -i $_.FullName }

However, I'd like to group this under one single transaction so if one of the sql files fails, then everything is rolled back.

One alternative that I am also trying is to put together all the file contents in to a full script variable:

foreach( $file in Get-ChildItem -path $databaseFolder -Filter *.sql | sort-object )
{
    Get-Content $file.fullName | Foreach-Object { $fullSqlScript = $fullSqlScript + $_ + "`n" } ;
}

And then execute that in the end like so:

 invoke-sqlcmd -ServerInstance $databaseServer -Database $databaseName -Query $fullSqlScript | format-table | out-file -filePath $outFile

The $fullSqlScript would also have the following inserted at the top:

:On Error Exit
 SET XACT_ABORT ON
 GO
 Begin Transaction

and end:

IF XACT_STATE() = 1
BEGIN
    PRINT 'Committing Transaction...'
    COMMIT TRANSACTION
END
ELSE IF XACT_STATE() = -1
BEGIN
    PRINT 'Scripts Failed... Rolling back'
    ROLLBACK TRAN
END

However, as soon as I make an intentional sql script fail then the entire database is locked up like the transaction cannot be rolled back. I'm assuming this has something to do with invoke-sqlcmd being used in a powershell script environment rather than sqlcmd from windows command prompt?

Dejan Vasic · Accepted Answer · 2012-09-19 23:27:44Z

1

I believe it has been solved. After reading the following article about having the On Error Exit only having ability to be executed using sqlcmd, I definitely had doubts about the powershell invoke-sqlcmd.

Here's some documentation I found here that I think may have something to do with it: http://technet.microsoft.com/en-us/library/cc281720.aspx

In particular the following: *

Not all of the sqlcmd commands are implemented in Invoke-Sqlcmd. Commands that are not implemented include the following: :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

*

In the end, I am still using the strategy of having to read the contents of each file and then having the:

:On Error Exit

at the top of the script that is finally written to an output file containing all the other sql from other files.

Then, I do the following to invoke the actual sqlcmd:

$command = "sqlcmd.exe -S " + $databaseServer + " -d " + $databaseName + " -i " + $outFile
Invoke-Expression $command

This seems to work just fine and everything does get rolled back :) If anyone can think of a better solution please let me know!

answered Sep 19, 2012 at 23:27

Dejan Vasic

6927 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yepeekai Over a year ago

I think having the script to throw an error when an error occur could be useful. So here is a slight change on the proposed answer:

& sqlcmd.exe -S $databaseServer -d $databaseName -i $inputFile       if ($LASTEXITCODE -ne 0) {         throw "script error"     }

The if needs to be on a new line but not sure how to get this done in a comment

Collectives™ on Stack Overflow

invoke-sqlcmd for multiple files in single transaction using powershell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related