Results from invoke-sqlcmd

Welcome Forums General PowerShell Q&A Results from invoke-sqlcmd

This topic contains 5 replies, has 4 voices, and was last updated by

1 year, 3 months ago.

  • Author
  • #83753

    Points: 0
    Rank: Member

    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)


    (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,

  • #83761

    Points: 0
    Rank: Member

    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?

  • #83767

    Points: 0
    Rank: Member

    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.

  • #83788

    Points: 214
    Rank: Participant

    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""


    • #83816

      Points: 0
      Rank: Member

      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,

  • #83813

    Points: 516
    Helping Hand
    Rank: Major Contributor

    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

The topic ‘Results from invoke-sqlcmd’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort