run mssql stored procedure from PS script and capture output

  • Bruin

    Ten Centuries

    Points: 1012

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

    Thanks.

  • Site Owners

    SSC Guru

    Points: 80373

    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: 720484

    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

    Ten Centuries

    Points: 1012

    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: 720484

    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: 323460

    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

    Ten Centuries

    Points: 1012

    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

    Ten Centuries

    Points: 1012

    Any examples or ideas to make this requirement?

    Thanks.

  • Lowell

    SSC Guru

    Points: 323460

    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

    Ten Centuries

    Points: 1012

    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

    Ten Centuries

    Points: 1012

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

     

    Thanks.

  • frederico_fonseca

    SSChampion

    Points: 14708

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

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

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