Getting the values from InnerException

Welcome Forums General PowerShell Q&A Getting the values from InnerException

Viewing 8 reply threads
  • Author
    Posts
    • #184380
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor

      I'm running a check on Constraints in databases and there are errors. Although I do get an error message, it's simply "Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'testdb'. "".

      What I want to get is the value of the System.Data.SqlClient.SqlException from the InnnerMessage, which in this case is "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK1BATCH_RUN". The conflict occurred in database
      "testdb", table "dbo.BATCH_DEFINITION"".

      I've tried these 3 solutions so far, none give me the SqlException message....any suggestions greatfully received!

      *Updated, added 1st 3 lines

      [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
      $server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
      $db = $server.Databases[$Database]
      try {
      $db.ExecuteNonQuery($Query);
      } catch {
      $_.Exception
      $_.Exception.GetType().FullName,$_.Exception.Message
      $_.Exception | fl -force
      }
      
      
    • #184389
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      Hi Iain,

      Can you please post the complete code? What is $db? Are you using SMOs or .Net Code to connect SQL Server?

      Thank you.

    • #184392
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor

      added the 3 lines that produce $Db

      • #184401
        Participant
        Topics: 2
        Replies: 135
        Points: 708
        Helping Hand
        Rank: Major Contributor

        What is the query?

    • #184404
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor
      
      ALTER TABLE [dbo].[BATCH_RUN] WITH CHECK CHECK CONSTRAINT [FK1BATCH_RUN]
      
      
    • #184425
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor

      I've also tried using .net but not even an error return by that....

      
      Result :
      Id : 37970
      Exception :
      Status : RanToCompletion
      IsCanceled : False
      IsCompleted : True
      CreationOptions : None
      AsyncState :
      IsFaulted : False
      AsyncWaitHandle : System.Threading.ManualResetEvent
      CompletedSynchronously : False
      
      

      Command used:

      
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = SSPI"
      $SqlConnection.open()
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand ($Query,$SqlConnection)
      $SqlCmd.BeginExecuteNonQuery()
      
      

       

       

    • #184491
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      Yes, there shouldn't be an error, unless the objects don't exist or misspelled.

      Even I mimic the same scenario, and it worked fine in my lab.

    • #184506
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor

      Kiran, You've misunderstood.

      Forget the query, it's irrelevant really, although that one does throw an error in my test environment. I used that particular query as an example but it could be any query you can dream up, so long as it throws an error. What I'm trying to find out, is how to get the innerexception of the error, the portion of the error returned by the System.Data.SqlClient.SqlException.

      This bit of the full error message below

      
      The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK1BATCH_RUN". The conflict occurred in database "testdb", table "dbo.BATCH_DEFINITION"
      
      

      Full error message

      
      ErrorRecord : Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'testdb'. "
      WasThrownFromThrowStatement : False
      Message : Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'bwtestalkali'. "
      Data : {System.Management.Automation.Interpreter.InterpretedFrameInfo}
      InnerException : Microsoft.SqlServer.Management.Smo.FailedOperationException: ExecuteNonQuery failed for Database 'testdb'. --->
      Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. --->
      System.Data.SqlClient.SqlException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK1BATCH_RUN". The conflict occurred in database
      "bwtestalkali", table "dbo.BATCH_DEFINITION".
      at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean
      catchException)
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      --- End of inner exception stack trace ---
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
      at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
      --- End of inner exception stack trace ---
      at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
      at CallSite.Target(Closure , CallSite , Object , Object )
      TargetSite : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext, System.Exception)
      StackTrace : at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
      
      
    • #184512
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      Okay, basically what you are trying to say is with SMO you are seeing some errors, but not with the .Net approach, right?

       

    • #184550
      Participant
      Topics: 41
      Replies: 166
      Points: 571
      Helping Hand
      Rank: Major Contributor

      No Kiran, What I'm saying is "I want to get is the value of the System.Data.SqlClient.SqlException from the InnnerMessage"....

Viewing 8 reply threads
  • You must be logged in to reply to this topic.