CODEWITHSUNDEEP
t-sqlpowershellsql-server-2008-r2powershell-2.0
Michael Sync
Michael Sync

Reputation: 4994

How do you handle the exception in bat and *sql files

I'm using powershell script to run a few *.sql file. I can get the output value by using the script below.. 

$return_value = sqlcmd -S ServerName -i "MyAwesome.sql" -v parameter1="par1"

The problem is that I will have to extract out the output text to determine whether there is any error in SQL file or not..

Is there any best practice to handle the exceptions in powershell file and *.sql file?

Do I have to catch the error in each and every SQL files to produce the pre-defined output?

Upvotes: 1

Views: 757

Answers (2)

David Brabant
David Brabant

Reputation: 43499

Might not be an option for you, but the Invoke-SqlCmd cmdlet has a parameter called "-ErrorVariable" to trap error messages.

Upvotes: 1

Andy Arismendi
Andy Arismendi

Reputation: 52577

You can use sqlcmd's exit code to determine whether there was an error or not. 

$output = sqlcmd -S ServerName -i "MyAwesome.sql" -v parameter1="par1"
if ($LASTEXITCODE -ne 0) {
    Write-Error $output
}

If the exit code is not 0 you will usually find the error message either in stdout or stderr. You can put both in the output variable like this:

$output = sqlcmd -S ServerName -i "MyAwesome.sql" -v parameter1="par1" 2>&1

If there was anything in stderr it will be combined with whatever is in stdout.

In your SQL script you will also want to implement best practice error handling techniques such as using try/catch constructs and using transactions where warranted.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

If you catch an exception in your SQL script print the error message and set the return code so you can handle the error in PowerShell.

Upvotes: 0

Related Questions