ExecutenonQuery and Error Handling

• Jason Bunn

  SSCommitted

  Points: 1713

  More actions

  January 30, 2012 at 12:27 pm

  #252126

  Hello. I have a question

  Consider

  # Open Connection to Server #

  $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$s;Database=$d;Integrated Security=SSPI;";

  $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };

  $conn.add_InfoMessage($handler);

  $conn.FireInfoMessageEventOnUserErrors = $true;

  $conn.Open();

  # Invoke Stored Procedrue #

  $cmd = $conn.CreateCommand();

  $cmd.CommandText = "execute TestProc DatabaseThatDoesNotExist";

  $res = $cmd.ExecuteNonQuery();

  $conn.Close();

  All of that is in a try...catch.

  If I run it as is, forcing an error (passing in DatabaseThatDoesNotExist to TestProc), I see the error text returned to the client, but control is not transferred to the catch block. If I remove the $handler lines (indented), then the error fires and control does go to the catch block, but I don't see the text of the SQL Server error message in the transcript. Is it possible to both see the error text, and have control pass to the catch block?

  Thank you.

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  January 31, 2012 at 12:25 am

  #1440362

  I think you are trying to utilise two different error handling mechanisms/techniques. Only one can "win" thus you end up with an Either/Or situation.

  Can you not achieve this by placing the Write-Host in the catch block?

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Jason Bunn

  SSCommitted

  Points: 1713

  More actions

  January 31, 2012 at 9:20 am

  #1440740

  Hi Gary, I tried adding a Write-Host $event.Message to the catch block, but when I include the MessageEventHandler, the catch is not invoked. When I remove the Handler, there is nothing in $event.Message.

  That said, I came up with something that works, but it's not very elegant. I added an error flag to the handler and thow an explicit error if that flag gets set:

  # Open Connection to Server #

  $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$s;Database=SQLDBA;Integrated Security=SSPI;";

  $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event)

  Write-Host $event.Message;

  $err = 1;

  };

  $conn.add_InfoMessage($handler);

  $conn.FireInfoMessageEventOnUserErrors = $true;

  $conn.Open();

  # Invoke Stored Procedrue #

  $cmd = $conn.CreateCommand();

  $cmd.CommandText = "execute usp_Proc dBThatDoesNotExist";

  $res = $cmd.ExecuteNonQuery();

  $conn.Close();

  if ($err -eq 1) {

  throw "Stored Procedure Error"

  }

  This seems to work using that $err flag. But, I'm more than happy to hear other suggestions...

  Jason

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  February 1, 2012 at 12:51 am

  #1441057

  I used the following:

  try

  {

  # Open Connection to Server #

  $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$s;Database=$d;Integrated Security=SSPI;";

  $conn.Open();

  # Invoke Stored Procedrue #

  $cmd = $conn.CreateCommand();

  $cmd.CommandText = "execute TestProc DatabaseThatDoesNotExist";

  $res = $cmd.ExecuteNonQuery();

  }

  catch [System.Exception]

  {

  Write-Host $_.Exception.Message

  }

  finally

  {

  $conn.Close();

  }

  I have omitted the setting of $s and $d like you did, however, I have shown the try/catch block in full in order to illustrate the technique.

  NOTE: I have used the finally block to ensure that the connection is closed.

  In summary, the above shows access to SQL Server with try/catch/finally being the only exceptional handling technique employed.

  HTH!!!

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• MG-148046

  SSCrazy Eights

  Points: 9946

  More actions

  February 2, 2012 at 6:35 am

  #1441765

  I think setting $ErrorActoinPreference = Stop will do it for you.

  MG

  "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
  Tony Hoare

  "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.