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
Capturing error from invoke-sqlcmd
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Re: Capturing error from invoke-sqlcmd
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:
In the console this returns:
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:
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
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
However I am running this in my Powershell Studio Form based project.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).
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
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
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:
This one worked with the invoke-sql:
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:
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.
Code: Select all
$Results = (Restore-SqlDatabase -ServerInstance $SQLServer -Database $Database -BackupFile "DBBackup.bak" -ReplaceDatabase -verbose | out-null) 4>&1
Code: Select all
$Results = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile '.\Test.sql' -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop -Verbose | out-null) 4>&1
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" }
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
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.
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
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.\
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
Actually I forgot to put back in the correct where clause that does go through multiple databases. Sorry about that.
Should have looked like:
That's what I should have pasted.
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"
Re: Capturing error from invoke-sqlcmd
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
I think I got it without the out-null. The commands now look like: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.
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
Do I have the above correct now?