How to capture output message and save it to variable in powershell and SqlCmd-CodePudding

I am using a powershell code

Also tried using TRY CATCH block, but it simply not capturing an error.

BEGIN TRY
   DBCC CHECKDB ([50Ways]) WITH NO_INFOMSGS,PHYSICAL_ONLY
END TRY
BEGIN CATCH
   SELECT 
          ERROR_MESSAGE() AS [Error Message]
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_NUMBER() AS [Error Number]  
         ,ERROR_SEVERITY() AS [Error Severity]  
         ,ERROR_STATE() AS [Error State]  

END CATCH

CodePudding user response：

These are just standard exception errors that you can capture using the CommonParameter -ErrorVariable or by using the automatic variable $Error.

Below is an example of using ErrorVariable. We tell Invoke-SqlCmd to store any errors encountered in a variable called 'dbCheckErrors'. We later check if this variable $dbCheckErrors contains any ErrorRecords and if it does we collect all the Exception messages into our $errorCollection array for later access. Once done with our db checks we can either view the messages, save the messages into file, both, or whatever else.

# Create an empty array to later capture error message in our loop
$errorCollection = @()
foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."
    Invoke-SqlCmd -ErrorVariable 'dbCheckErrors' -ServerInstance '(localdb)\MSSQLLocalDB' -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose 

    if ($dbCheckErrors) {
        # Do something with any errors here

        # Or just add the exception message(s) to a collection to handle later
        $errorCollection  = $dbCheckErrors.Exception.Message
    }
}

# view the exception messages
$errorCollection

# save the exception messages to file
$errorCollection | Set-Content -Path '.\dbCheckErrors.log'