Getting the values from InnerException

PowerShell Help
1

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
}
2

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.

3

added the 3 lines that produce $Db

4

What is the query?

5 
ALTER TABLE [dbo].[BATCH_RUN] WITH CHECK CHECK CONSTRAINT [FK1BATCH_RUN]
6

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()

 

 

7

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.

8

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)
9

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

 

10

No Kiran, What I’m saying is “I want to get is the value of the System.Data.SqlClient.SqlException from the InnnerMessage”…