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

Question

I am using a powershell code https://dba.stackexchange.com/a/254401/92058 that is looping through each database on an instance and checking for corruption using DBCC CheckDB sql command.

If a corruption detected (like in this case a page is corrupted on DB 50Ways), the DBCC CheckDB command will output a message (not an error!).

As you can see, processing database 50Ways the message popped up below, indicating that it found a corrupted page.

So how can I capture only this message (if any) and save it to a variable, so then, I can shoot an email notification to myself.

$ServerInstance = "myinstance" ## instance name 
$Databases = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"

foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."

    Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose
}
Write-Output "Complete."

Output looks like this:

Processing WideWorldImporters...
Processing tempdb...
Processing msdb...
Processing model...
Processing master...
Processing DBA...
Processing AdventureWorks2017...
Processing 50Ways...
Invoke-SqlCmd : Table error: Object ID 901578250, index ID 0, partition ID 
72057594043105280, alloc unit ID 72057594049527808 (type In-row data), page (1:312). 
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Object ID 901578250, index ID 0, partition ID 72057594043105280, alloc unit ID 
72057594049527808 (type In-row data): Page (1:312) could not be processed.  See other 
errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ToLeaveYourLover' 
(object ID 901578250).
CHECKDB found 0 allocation errors and 2 consistency errors in database '50Ways'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB 
(50Ways).
At line:11 char:5
+     Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Q ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlEx 
   ecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScr 
   iptCommand
 
Complete.

As on a picture, only a red message need to be captured in a variable.

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

See dba.stackexchange.com/questions/156287/…. But much better: try WITH TABLERESULTS, NO_INFOMSGS to get it as an actual table, much easier to work with than info messages. — Charlieface
– Charlieface, Commented Sep 15, 2021 at 1:41
@AbrahamZinala Yes. If there is an error (corruption) I want to capture the message. If not - do not capture anything — Serdia
– Serdia, Commented Sep 15, 2021 at 2:16
and this: Try { Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose } Catch { $myerror = $_ } didn't work? — Abraham Zinala
– Abraham Zinala, Commented Sep 15, 2021 at 2:44
Do you try to play with -AbortOnError and -ErrorLevel params ? — JPBlanc
– JPBlanc, Commented Sep 15, 2021 at 3:39

Daniel · Accepted Answer · 2021-09-15 04:39:21Z

4

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'

answered Sep 15, 2021 at 4:39

Daniel

5,1422 gold badges9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Serdia Over a year ago

Thank you @Daniel. That is exactly what I was looking for. Learned something new!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related