Results from invoke-sqlcmd

PowerShell Help
1

I am trying to do some automation, and using invoke-sqlcmd to do some SQL inserts. I am failing to be able to get the results of insert or update statements into a text file. For example in SSMS:

insert into dbo.Dep values (‘green’,3)
insert into dbo.Dep values (‘black’,4)

returns

(1 row affected)

(1 row affected)

I have tried out-file, and *>> to get this info into a file.

Any help will be greatly appreciated.
Thank you,

2

Can you post the code you are using to get to that point? If you set the command that runs your Inserts to a variable is your output captured there?

3

Here is example code (Demos the problem I am having)

Wanted output in Output.txt and/or Output1.txt is:

(1 row affected)

(1 row affected)

So far, actual output is empty file. incidentally if I change $query to Select getdate(), the current date/time is placed in output.txt and/or output1.txt file.

When the script is run, then entries of Green 3 and Black 4 are successfully entered into the table 2 times.

# $query = "select getdate()"
$query = "insert into dbo.Dep values ('green',3); insert into dbo.Dep values ('black',4)"
$server = Get-Content "C:\temp\Server.txt"
$userID1 = get-content "C:\temp\UserID1.txt"
$password1 = get-content "C:\temp\Password1.txt"

Invoke-Sqlcmd -ServerInstance $server -Database AdventureWorks2012 -Query $query -Username $userID1 -Password $password1 -Verbose |Out-File -FilePath "C:\Temp\Output.txt" -Encoding ASCII
# or
Invoke-Sqlcmd -ServerInstance $server -Database AdventureWorks2012 -Query $query -Username $userID1 -Password $password1 -Verbose *>>"C:\Temp\Output1.txt"

Get-Content "C:\Temp\Output.txt"
Get-Content "C:\Temp\Output1.txt"

Thank you for your time and assistance on this matter.
Dep

4

There is nothing returned by the insert per se. What you are experiencing is normal.
When you run in SSMS you’ll get the rows affected if nocount is off but that’s not returned by invoke-sqlcmd.
You could try adding “;Select @@ROWCOUNT” to your insert statement
Another option would be to use sqlcmd which does return the rows

BTW you can also reduce the typing required:

“insert into dbo.Dep values (‘green’,3),(‘black’,4);Select @@ROWCOUNT”"

.

5

Does it go to standard error, like (2>&1 redirects standard error to standard out):

ls foo4 >> out 2>&1
cat out

ls : Cannot find path 'foo4' because it does not exist.
At line:1 char:1
+ ls foo4 > out 2>&1
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (foo4:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
6

Iain, Thank you for the suggestion, I decided to go with the SQLCMD approach and that solved this problem.

All, Thank you for you help and assistance. I really appreciate a community that responds so quickly and has great knowledge.

With great appreciation,
Dep