Results from invoke-sqlcmd

This topic contains 5 replies, has 4 voices, and was last updated by  Michael Deputy 1 week, 2 days ago.

  • Author
    Posts
  • #83753

    Michael Deputy
    Participant

    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,

  • #83761

    Steve Whitlock
    Participant

    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

    Michael Deputy
    Participant

    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

  • #83788

    iain Barnetson
    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

      Michael Deputy
      Participant

      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

  • #83813

    js
    Participant

    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
    

You must be logged in to reply to this topic.