Page 1 of 2

Capturing error from invoke-sqlcmd

Posted: Wed Aug 12, 2020 8:31 am
by gsaunders
Product, version and build: Powershell 2020, 5.7.179
32 or 64 bit version of product: 64bit
Operating system: Windows 10
32 or 64 bit OS: 64bit

I am doing an Invoke-Sqlcmd from the SQLServer module.

This is being called from a button on a form.

In the Output of the Studio I see the error I am causing on purpose.
What I can't seem to do is capture it and show to the user.

I tried using the -OutputSqlErrors #true, but in Studio doesn't seem to make difference.

From PS ISE it does make a difference on what shows up in console, but we are not doing this in console, but script of form.

I tried $SQLResult = Invoke-SqlCmd... and don't get any value to $SQLResult.
I tried the -OuputSqlErrors.
Ultimately I am not sure how to capture the results so I can display to the user.

I can use Try / Catch and force some sort of message that it failed, but how do I capture what the command itself returns.

Thanks in advance.

Greg

Re: Capturing error from invoke-sqlcmd

Posted: Thu Aug 13, 2020 4:08 pm
by jvierra
Without some idea of the code you are using it is not possible to guess at what you are trying to ask.

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 6:22 am
by gsaunders
Sorry for delayed response.

Let me give a specific example of something I am currently looking at. It is not the invoke-sqlcmd in this example, but the Restore-sqldatabase.

So here is the command:

Code: Select all

Restore-SqlDatabase -ServerInstance $SQLServer -Database $Database -BackupFile "DBBackup.bak" -ReplaceDatabase -verbose
In the console this returns:
VERBOSE: Performing the operation "Restore-SqlDatabase" on target "[TestServer]".
VERBOSE: RESTORE DATABASE [Test] FROM DISK = N'DBBackup.bak' WITH NOUNLOAD, REPLACE, STATS = 10
VERBOSE: 10 percent processed.
VERBOSE: 21 percent processed.
VERBOSE: 30 percent processed.
VERBOSE: 41 percent processed.
VERBOSE: 51 percent processed.
VERBOSE: 60 percent processed.
VERBOSE: 71 percent processed.
VERBOSE: 80 percent processed.
VERBOSE: 90 percent processed.
VERBOSE: 100 percent processed.
VERBOSE: Processed 368 pages for database 'Test', file 'Test' on file 1.
VERBOSE: Processed 2 pages for database 'Test', file 'Test_log' on file 1.
VERBOSE: RESTORE DATABASE successfully processed 370 pages in 0.093 seconds (31.081 MB/sec).
However I am running this in my Powershell Studio Form based project.

When it runs there I get a graphical progress bar in a dialog box that goes to 100 percent, but what I really want to also do is capture the above verbose information to display into a text box or list box.

I am not sure how to capture this. I tried assigning a variable like:

Code: Select all

$RestoreResults = Restore-SqlDatabase -ServerInstance $SQLServer -Database $Database -BackupFile "DBBackup.bak" -ReplaceDatabase -verbose
But that returns nothing.

I am not playing around with the command parameters -InformationAction and -InformationVariable thinking maybe they can capture the results.

I am fairly new to powershell so capturing what normally shows in a console when running it in Powershell ISE vs getting those results from script in a form based environment is proving a bit confusing.

Some cmdlets seem to return what I would expect to an assigned variable like above and some cmdlets don't.

I simply want to capture any results that would normally go to the console screen from any cmdlet I run.

Thanks for your help in advance.

Greg

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 9:18 am
by gsaunders
So I have found at least one solution, but wonder if this is the best way and if there is another method. But here is what worked with the restore:

Code: Select all

$Results = (Restore-SqlDatabase -ServerInstance $SQLServer -Database $Database -BackupFile "DBBackup.bak" -ReplaceDatabase -verbose | out-null) 4>&1
This one worked with the invoke-sql:

Code: Select all

$Results = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile '.\Test.sql' -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop -Verbose | out-null) 4>&1
Now I can spit the $Results to a textbox... unfortunately it doesn't seem to maintain the CRLF and wraps text, but I can live with that for now. If someone knows how to keep it from doing that and maintain the CRLF so it looks in the textbox just like it would in the console or what SQL shows.

One last command that I am not sure how to get the messages from as it is using foreach:

Code: Select all

Get-SqlDatabase -ServerInstance $ServerName | Where { ($_.Name -eq 'Test') } | foreach { Backup-SqlDatabase -DatabaseObject $_ -NoRewind -BackupFile "C:\Test\$($_.NAME).bak" }
I don't know how to do the above when using the foreach. I will play with it, but maybe someone has done this before.

Seems like the assignment would happen during the Backup-SqlDatabase section, but now sure how one would do that.

Thanks again and hopefully what I have found so far will help someone else trying to capture the messages.

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 9:25 am
by jvierra
Don't use Out-Null. All output of a command will end up in "$results" and it will be an array of lines that can be added to a textbox.

Test this at a prompt and not in PSS. When you get the results you want then it can be displayed in the Form. Until you can understand how to use the redirector and what it returns as a result object the form will just add confusion.

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 9:38 am
by jvierra
Why do you need a loop to backup a single database? Just run the backup on that database and the issue will not occur.

All I can say is that you request doesn't make any sense to me. The results can be output as needed in or out of a loop.\

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 11:21 am
by gsaunders
Actually I forgot to put back in the correct where clause that does go through multiple databases. Sorry about that.

Should have looked like:

Code: Select all

Get-SqlDatabase -ServerInstance $ServerName | Where { ($_.Name -ne 'TempDB') } | foreach { Backup-SqlDatabase -DatabaseObject $_ -NoRewind -BackupFile "C:\Test\$($_.NAME).bak" 
That's what I should have pasted.

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 11:27 am
by jvierra
First we need to format your code so that it is readable and maintainable. This also helps when debugging.

Code: Select all

$results = Get-SqlDatabase -ServerInstance $ServerName |
    Where-Object{$_.Name -ne 'TempDB'} |
    ForEach-Object{
        Backup-SqlDatabase -DatabaseObject $_ -NoRewind -BackupFile "C:\Test\$($_.NAME).bak" -Verbose 4>&1
    }

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 11:36 am
by gsaunders
jvierra wrote:
Wed Aug 19, 2020 9:25 am
Don't use Out-Null. All output of a command will end up in "$results" and it will be an array of lines that can be added to a textbox.

Test this at a prompt and not in PSS. When you get the results you want then it can be displayed in the Form. Until you can understand how to use the redirector and what it returns as a result object the form will just add confusion.
I think I got it without the out-null. The commands now look like:

Code: Select all

$results = Restore-SqlDatabase -ServerInstance $SQLServer -Database $Database -BackupFile "DBBackup.bak" -ReplaceDatabase -verbose 4>&1

Code: Select all

$results = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile '.\Test.sql' -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop -Verbose 4>&1
I am seeing the messages returned in $results now.

Do I have the above correct now?

Re: Capturing error from invoke-sqlcmd

Posted: Wed Aug 19, 2020 11:50 am
by jvierra
"Correct" depends on what you are trying to do.