Powershell Try/Catch Exception to SQL

Question

Hello I have a little problem regarding my error logging to SQL from powershell scripts.

function Invoke-Sqlcmd {
    param(
    [Parameter(Position=0, Mandatory=$true ,ValueFromPipeline = $false)] [string]$ServerInstance,
    [Parameter(Position=1, Mandatory=$true ,ValueFromPipeline = $false)] [string]$Database,
    [Parameter(Position=2, Mandatory=$false ,ValueFromPipeline = $false)] [string]$UserName,
    [Parameter(Position=3, Mandatory=$false ,ValueFromPipeline = $false)] [string]$Password,
    [Parameter(Position=4, Mandatory=$true ,ValueFromPipeline = $false)] [string]$Query,
    [Parameter(Position=5, Mandatory=$false ,ValueFromPipeline = $false)] [Int32]$QueryTimeout=30
    )

    $conn=new-object System.Data.SqlClient.SQLConnection
    if ($UserName -and $Password)

        { $conn.ConnectionString="Server={0};Database={1};User ID={2};Pwd={3}" -f $ServerInstance,$Database,$UserName,$Password }
    else
        { $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database  }

    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$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[0]
}
try {
    ThisCommandDoesNotExist
}
catch {
    $Exception = $_.Exception.Message
    Invoke-Sqlcmd -Serverinstance mysqlserver -Database Mydatabase -Query "Insert into MyTable Values (GetDate(),'$Exception')"
}

The error I receive is:

Invoke-Sqlcmd: Exception calling "Fill with "1" argument(s): "Incorrect syntax near 'ThisCommandDoesNotExist'

I suppose it has something do to with the single quotes, I am no SQL expert. But I have tried doing a replace on all the "'" and "," and "." but still the same error is thrown.

If you comment out the line with "Invoke-Sqlcmd", does the error still occur? — Brent
– Brent, Commented Jan 27, 2014 at 23:08
Found the solution myself. At last it work when i did replace on .,:= — user2782999
– user2782999, Commented Jan 29, 2014 at 12:41

todd · Accepted Answer · 2014-09-26 18:54:45Z

1

You are correct, the single-quotes makes powershell treat your $variable as a string.

You can escape powershell special characters by using the backtick `.

answered Sep 26, 2014 at 18:54

todd

111 bronze badge

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

Comments

user2782999 · Accepted Answer · 2014-01-29 12:42:21Z

0

Found the solution myself. At last it work when i did replace on .,:=

answered Jan 29, 2014 at 12:42

user2782999

4251 gold badge9 silver badges24 bronze badges

Comments

Nico Botes · Accepted Answer · 2024-02-22 08:22:23Z

0

This was a killer, 3.43hours of my life I will never reclaim...

The fix for me:

'$($ErrorMessage -replace "'", "''")'

from the call in the Catch-part (you can see from comments I tried a couple of things :) ):

 catch{
        #do something on the catch
        #$errorvarclean = $error -replace '[#?\{]','_A'
        #$string_err = $_ | Out-String
        #$string_err = $_ | Format-List * -Force | Out-String
        #Write-Host($string_err)
        LogToTable-SQLConnectionTestResults $row.SrvLoginName $row.ServerName $row.DatabaseName $row.program_name "0" $Error[0]
        #$Error[0]
        #$_.Exception.Message
        #Write-Host($error)
        #Write-Host($errorvarclean)
    }

Good luck folks!

answered Feb 22, 2024 at 8:22

Nico Botes

411 silver badge3 bronze badges

Collectives™ on Stack Overflow

Powershell Try/Catch Exception to SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related