-1

I am writing a PowerShell script and one of the requirements is to store error messages in a SQL server table.

The problem here is that error messages can come in different forms. In this particular case, the error is as follows.

"Exception calling "open" with "0" argument(s): "login failed for user 'DOMAIN\USER'."

I have managed to store this in a PowerShell variable, the issue is how can I then insert it into a SQL Server table without errors. The double quote is resulting in errors with the insert.

I have tried to escape the double quotes by adding "$the_error". This did not help.

The way in which the error is generated is as follows.

$return_Value = PsFunction

PsFunction returns a custom object, of which error_message is one of the members together with other values.

Now, here is what I am doing

$query = "insert into table (error_message) values ('{0}') " -f $return_value.error_message

I am having to replace the single and double quotes, feels like a fudge, however would be keen to insert the errors as it appears during execution.

Dale K
  • 21,987
  • 13
  • 41
  • 69
user5544
  • 71
  • 8
  • Does this answer your question? [Escaping quotes and double quotes](https://stackoverflow.com/questions/18116186/escaping-quotes-and-double-quotes) – Dale K Jun 17 '21 at 20:44

1 Answers1

5

Don't escape the string. Use a parameter instead.

eg

$the_error = @"
"Exception calling "open" with "0" argument(s): "login failed for user 'DOMAIN\USER'."
"@


$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "server=localhost;database=tempdb;integrated security=true"
$con.Open()

$sql = "INSERT INTO ERRORS(message) values (@message)"
$cmd = $con.CreateCommand()
$cmd.CommandText = $sql

$cmd.CommandText
$pMessage = $cmd.Parameters.Add("@message", [System.Data.SqlDbType]::NVarChar,-1)
$pMessage.Value = $the_error

$cmd.ExecuteNonQuery()

$con.Close()
David Browne - Microsoft
  • 66,275
  • 5
  • 31
  • 57
  • 1
    It's a shame so many powershell sql examples just do string substitution. I'd love to see some kind of shortcut for powershell that makes query parameters less verbose. – Joel Coehoorn Jun 17 '21 at 21:36
  • Hi David, Thanks for the proposed answer. I added some more context to my question. I am inserting other fields, the error message just happens to be the field I am having issues with. – user5544 Jun 17 '21 at 22:10
  • 4
    Use parameters for the other fields too. – David Browne - Microsoft Jun 17 '21 at 23:08