run mssql stored procedure from PS script and capture output

Question

Post reply

run mssql stored procedure from PS script and capture output

Bruin

SSCrazy

Points: 2071
More actions
July 22, 2020 at 12:51 am

#3771986

Can someone share a PS script that will execute a mssql stored procedure and capture all of the output messages in the stdout?
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

Here you go:

$who = Invoke-Sqlcmd -ServerInstance "Aristotle\SQL2017" -Database "master" -Query "EXEC sp_Who"

foreach ($row in $who)
{
    Write-Host ("SPID: " + $row.spid + " Status: " + $row.Status)
}

Bruin SSCrazy Points: 2071 More actions · Answer 3

Nice..

I want to have a common module that from a command file have be able to supply

RunSP -Instancename localhost -Database stagingdatabase -Sp 'usp_sp_import_csv' -SpOutput 'C:\importcsv.log'

Then from command file be able to check output. I have try-catch in the SP's, but doesn't seem to catch return code when

running thru PS.

IF %ERRORLEVEL% EQU 0 GOTO step2

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

Invoke-SqlCmd will do this for you. If you really want a RunPS, alias it, but there's no reason to have some other cmdlet.

If you want to capture the output in a file, you can pipe this to Out-File.

I'm not sure what you mean with checking the try catch. Is there a return code from the proc? If you're looking to see if the proc returns an error, does the CATCH block throw something? The catch should ensure the proc still returns a 0 code.

Lowell SSC Guru Points: 323518 More actions · Answer 5

I hope this example helps.

in this case, i am looping through all *.sql files in a given folder.

the output to a log file creates a set pf Starting $scriptname / Ending $Scriptname,

and also outputs whatever the output is: whether info messages, rows of returned data, etc.

if there was an error, the catch outputs the error details.

###################################################################################################
## Loop through each file in current folder, and also each sub folder
###################################################################################################
#$SQLServer = $env:COMPUTERNAME ##the local machine / default MSSQLSERVER?
#$SQLServer = '(LocalDB)\WIN-05LQX24A-Prod'
$SQLServer = 'StormNuc'
$SQLDatabase = "master"
$BasePath ="D:\Data\StandardTakeOver\AutomatedInstall"
$LogPath = [System.IO.Path]::Combine($BasePath,"SQLScriptLogs_" + (Get-Date).ToString("yyyy-MM-dd") + ".txt")

if(![System.IO.Directory]::Exists($BasePath)){[System.IO.Directory]::CreateDirectory($BasePath)}

#$LogPath = [System.IO.Path]::Combine($BasePath,"ScriptResults.txt")
Clear-Host
if($SQLServer -ne '' -and [System.IO.Directory]::Exists($BasePath))
{
###################################################################################################
## any files in current folder?
###################################################################################################
    $AllSQLFiles =  [System.IO.Directory]::GetFiles($BasePath,'*.sql') | Sort-Object
    foreach($sqlfile in $AllSQLFiles)
    {
        try
        {
            $StagingFileName = "--Begin " + [System.IO.Path]::GetFileName($sqlfile)
            Write-Host $StagingFileName -ForegroundColor Green
            Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
            Invoke-SqlCmd -InputFile $sqlfile  -ServerInstance $SQLServer -Database $SQLDatabase  -Querytimeout 600 | Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
            $StagingFileName = "--End   " + [System.IO.Path]::GetFileName($sqlfile)
            Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999

        }
        catch
        {
            $ErrorMessage = $_.Exception.Message
            $FailedItem = $_.Exception.ItemName
            $StagingFileName = "--Error " + [System.IO.Path]::GetFileName($sqlfile)
            Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
            Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
            Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
        }

    }
###################################################################################################
##foreach folder single level only.
###################################################################################################
    $AllDirectories = [System.IO.Directory]::GetDirectories($BasePath) | Sort-Object
    foreach($CurrentDirectory in $AllDirectories)
    {
        $AllSQLFiles =  [System.IO.Directory]::GetFiles($CurrentDirectory,'*.sql') | Sort-Object
        foreach($sqlfile in $AllSQLFiles)
        {
                try
                {
                    $StagingFileName = "--Begin " + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                    Write-Host $StagingFileName -ForegroundColor Green
                    Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                    Invoke-SqlCmd -InputFile $sqlfile  -ServerInstance $SQLServer -Database $SQLDatabase -Querytimeout 600 | Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                    $StagingFileName = "--End   "  + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                    Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
                catch
                {
                    $ErrorMessage = $_.Exception.Message
                    $FailedItem = $_.Exception.ItemName
                     $StagingFileName = "--Error "  + $CurrentDirectory + " | " + [System.IO.Path]::GetFileName($sqlfile)
                    Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                    Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                    Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                }
            }
    }

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bruin SSCrazy Points: 2071 More actions · Answer 6

Instead of reading .sql could it read a .txt where multiple sp's are defined and stop if anyone of the procedures has an error?

spload.txt --> PS script reads

content:

usp_sp_import_ac_conv -- if fails stop and don't continue remaining sp's in list

usp_sp_updt_ac_conv

Thanks.

Bruin SSCrazy Points: 2071 More actions · Answer 7

Any examples or ideas to make this requirement?

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 8

you would need to add some code to specifically stop execution in the loop if an error is encountered.

you might want to change the logic to be all-or-nothing, instead of stop at first error.

sqlcmd and Invoke-SQLcmd neither have a built in feature asking for what you want: stop on first error.

here's a post on using a transaction:

https://stackoverflow.com/questions/13977650/multiple-invoke-sqlcmd-and-sql-server-transaction

and the logic would be something like if ($Error -gt 0) {break}

i am not sure on the syntax above without testing, which you can do and refine on your side to get more familiar with the code you have to support.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bruin SSCrazy Points: 2071 More actions · Answer 9

Any to trap if errors before running next SP.

$ServerInstance = "MyServer"
$Database = "MyDB"
$ConnectionTimeout = 30

$Query = "selectrow","truncateid","selectrow"

$QueryTimeout = 120

foreach ($Q in $Query){

    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Q,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables
}

Bruin SSCrazy Points: 2071 More actions · Answer 10

any way to trap errors in SP's so if one fails the process stops?

Thanks.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 11

look at the examples supplied here - some do have error handling which you should have cared to use.