Capturing error from invoke-sqlcmd

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
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.
gsaunders
Posts: 9
Joined: Thu Aug 06, 2020 1:35 pm

Re: Capturing error from invoke-sqlcmd

Post by gsaunders »

jvierra wrote:
Wed Aug 19, 2020 11:50 am
"Correct" depends on what you are trying to do.
I think I am good now.

Basically wanted to capture the information messages you normally would see in the messages windows of SQL.

Many of the non-SQL cmdlets worked just fine with $results = get-somecommand where the $results show exactly what the console was showing but the SQL one was not which is why I went down this road. Wasn't familiar yet with the 4>&1.

Still a lot to learn in Powershell. Great amount of power and a lot to learn.

Thanks again!

jvierra
Posts: 14578
Joined: Tue May 22, 2007 9:57 am
Answers: 1
Been upvoted: 1 time
Contact:

Re: Capturing error from invoke-sqlcmd

Post by jvierra »

No one ever learns ALL of PS. That is both unnecessary and likely impossible.
PS gets to be great fun the more you learn. Be sure to read the book linked under the "Training" menu above. It is free.

Have fun.

gsaunders
Posts: 9
Joined: Thu Aug 06, 2020 1:35 pm

Re: Capturing error from invoke-sqlcmd

Post by gsaunders »

Yea... I get learning it all is impossible.

Definitely making use of the get-help the | gm and other tricks that give you a lot of info.

I definitely need to learn more about other core areas like how data is streamed and so forth.

Thanks again... will check out the book.

gsaunders
Posts: 9
Joined: Thu Aug 06, 2020 1:35 pm

Re: Capturing error from invoke-sqlcmd

Post by gsaunders »

jvierra wrote:
Wed Aug 19, 2020 12:06 pm
No one ever learns ALL of PS. That is both unnecessary and likely impossible.
PS gets to be great fun the more you learn. Be sure to read the book linked under the "Training" menu above. It is free.

Have fun.
LOL... just when I think I am getting the hang of it.

So now I am NOT getting a result back for a slightly different command:

Code: Select all

$results = Invoke-Sqlcmd -ServerInstance $LAB_SQLServer -Database $TestDB -InputFile '.\ReplaceText.sql' -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop -Verbose 4>&1
The actual SQL code looks like this:

Code: Select all

alter table aTable disable trigger ALL;
UPDATE aTable SET DocName = REPLACE(DocName,'\\Server1\','\\Server2\')
UPDATE aTable SET DocName = REPLACE(DocName,'\\Server1b\','\\Server2\')
alter table aTable enable trigger ALL;
Just disable trigger, update text with a replace and then enable trigger.

The command runs fine:

Code: Select all

$results = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile '.\Test.sql' -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop -Verbose 4>&1
BUT... the $results comes back as a System.Management.Automation.PSCustomObject on the first command listed above where as the other Invoke_SQLCmd referenced second with different sql statement comes back as System.Object holding the results.

The first script is doing the update and the second script that gives results is creating a table.

Why am I not getting $results in the first one and why are the types different?

Thanks

jvierra
Posts: 14578
Joined: Tue May 22, 2007 9:57 am
Answers: 1
Been upvoted: 1 time
Contact:

Re: Capturing error from invoke-sqlcmd

Post by jvierra »

SQL that modifies a database does not return a result. If you want output then you have to generate it from your SQL script.

jvierra
Posts: 14578
Joined: Tue May 22, 2007 9:57 am
Answers: 1
Been upvoted: 1 time
Contact:

Re: Capturing error from invoke-sqlcmd

Post by jvierra »

First run the command without decoration at a prompt and inspect what is returned. Remove the redirection.

gsaunders
Posts: 9
Joined: Thu Aug 06, 2020 1:35 pm

Re: Capturing error from invoke-sqlcmd

Post by gsaunders »

jvierra wrote:
Wed Aug 19, 2020 1:33 pm
SQL that modifies a database does not return a result. If you want output then you have to generate it from your SQL script.
So the invoke-sqlcmd that creates the table works fine. And another one that executes a system command works fine.

The one that does a simple update doesn't provide anything. In SQL Management studio in the messages you do see:

Code: Select all

(22 rows affected)

Completion time: 2020-08-19T21:51:38.5168233-04:00
I thought I would get the same thing back.

You can run this:

Code: Select all

Invoke-Sqlcmd -ServerInstance LABVP-02 -Database Viewpoint -Query "update aTable set aColumn = ''Test'" -Verbose 4>&1
Substitute any simple update. Like I said in SQL Management Studio you see the rows and completion time in the messages window.

I thought I would at least be able to get the rows affected come back by default with the -verbose.

I went ahead and added print commands in the sql script and am going again. Just wondering why I have to do that at all. Guess the command simply wasn't returning anything even though it looked like it would have been from the SSMS messages window.

Thanks again.

jvierra
Posts: 14578
Joined: Tue May 22, 2007 9:57 am
Answers: 1
Been upvoted: 1 time
Contact:

Re: Capturing error from invoke-sqlcmd

Post by jvierra »

You cannot compare what you see in SSMS to what you get in PowerShell. An update does not produce output. It either is successful or it fails.

Locked