0

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
  • 25,246
  • 15
  • 42
  • 71
user5544
  • 131
  • 2
  • 9
  • 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

6

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
  • 80,331
  • 6
  • 39
  • 67
  • 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