none
Powershell Script to run T-sql files and capture messages from ssms RRS feed

  • Question

  • Hey,

    Im trying to execute T-sql files and extract query results and the number of affected rows information into a file. I also want see the information when "0 Rows are affected as well" Can someone help me with this?

    Thanks in advance!


    Friday, September 21, 2018 5:36 PM

All replies

  • see the options discussed here

    https://social.msdn.microsoft.com/Forums/en-US/b811c1b7-1fda-4027-ab97-9ecb1fabff36/how-to-execute-sql-script-using-windows-powershellusing-invokesqlcmd-or-any-if?forum=sqlsecurity


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, September 21, 2018 7:23 PM
  • Hi Mike,

    Please try the following PowerShell command and see if it works for you.

    Invoke-Sqlcmd -InputFile "C:\bulk\Input.sql" -ServerInstance WILLGO -Database CMS -verbose *> "C:\bulk\Output.txt"

    And the content of input file is like this.

    SELECT * FROM [dbo].[testA]
    
    PRINT cast(@@ROWCOUNT as varchar)+' Rows are affected'

    So the content of output file is like this.

    Task_Created_Time
    -----------------
    1:0              
    1:45             
    2:30             
    5:21             
    4 Rows are affected

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 24, 2018 7:32 AM
    Moderator
  • Hi Will,

    After adding the print command in the sql file and the verbose parameter along with *> , I am still unable to see the results in the file.

    Nitin.

    Monday, September 24, 2018 6:05 PM
  • Hi,

    before I start...
    Did you noticed that Visakh respond to your question as well and tried to help? Did you read his answer andcheck the link?!? I hate it when the OP (the person who ask the question) ignore all the responses and does not even say thanks to these who tried to help him! Even if something does not help you, you still got his time...

    Back to the question:

    Please try to these steps in roder to monitor where is the problem since in first glance Will_Kong's solution should work.

    1. For the sake of the discussion use the simplest query that you can in the file, and provide us the exact content of the file (using a simple query as I mentioned and not the real query)

    2. Execute the REAL query directly using SSMS and confirm the query is well executed by itself

    3. Try using Will_Kong's solution on the simple file

    Report your results :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Monday, September 24, 2018 6:16 PM
    Moderator
  • I have thanked everyone in advance if you haven't paid attention to my initial post and to my previous post!

    Additionally I can confirm that the query executed well in ssms.

    Content of the file: 

    SELECT    [ColumnName]
      FROM [DatabaseName].[dbo].[TableName]
      PRINT cast(@@ROWCOUNT as varchar)+' Rows are affected'

    Powershell script used:

    Invoke-Sqlcmd -InputFile "\\path\test.sql" -ServerInstance SERVERNAME -Database DB -verbose *> "\\path\testoutput.txt"

    To repeat: The script was executed with no errors, but the file had no info regarding the number of rows affected. 

    THANKS!!

    Mike

    Monday, September 24, 2018 8:46 PM
  • Hi,

    oh... I see :-)

    1. Don't use PRINT but SELECT (update 2018-09-25 08:00 GMT+3: I did not tested with PRINT, but I am sure that it is working with SELECT)

    2. Use a separator between the queries (for example add ";" at the end of each query)

    This should work for you (tested):

    I created new table:

    DROP TABLE IF EXISTS T;
    GO
    CREATE TABLE T(
    	ID INT,
    	Txt NVARCHAR(100)
    )
    GO

    Content of the file:

    select * from T;
    SELECT CONVERT(VARCHAR(10), @@ROWCOUNT) + ' Rows are affected';

    * Note! notice that I am not using PRINT but SELECT

    Command to execute:

    invoke-sqlcmd -inputfile "E:\MyScript.sql" -serverinstance ".\Your_Instance_Name" -database "tempdb" > "E:\MyScript.result"

    I hope that this solve your needs :-)

    -- update --

    By the way, instead of using ">" in order to move the output to the file, you can use "|" like bellow command:

    invoke-sqlcmd -inputfile "E:\MyScript.sql" -serverinstance ".\Your_Instance_Name" -database "tempdb" | out-File -filepath "E:\MyScript2.result"

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, September 24, 2018 9:17 PM
    Moderator
  • Hi Mike,

    Try below command and let us know:

    sqlcmd -d tempdb -s localhost -Q "SELECT TOP(3) name FROM sys.databases;" -o E:\Temp\SQL_output.txt

    NB, you will need to change the output file path.

    Cheers, Pedro

    Monday, September 24, 2018 9:44 PM
  • I have thanked everyone in advance if you haven't paid attention to my initial post and to my previous post!

    Additionally I can confirm that the query executed well in ssms.

    Content of the file: 

    SELECT    [ColumnName]
      FROM [DatabaseName].[dbo].[TableName]
      PRINT cast(@@ROWCOUNT as varchar)+' Rows are affected'

    Powershell script used:

    Invoke-Sqlcmd -InputFile "\\path\test.sql" -ServerInstance SERVERNAME -Database DB -verbose *> "\\path\testoutput.txt"

    To repeat: The script was executed with no errors, but the file had no info regarding the number of rows affected. 

    THANKS!!

    Mike

    Hi Mike,

    Thanks for your feedback.

    Per your reply, you need to modify the content of the SQL file like this.

    --Remove the database name part
    SELECT    [ColumnName]
       --FROM [DatabaseName].[dbo].[TableName]
       FROM [dbo].[TableName]
       PRINT cast(@@ROWCOUNT as varchar)+' Rows are affected'

    And adjust the Power shell command and see if it works.

       /* 
       1.Move the file "test.sql" to the local path
       2.You don't need to create the file "testoutput.txt", it would create the file automatically. Also, specify the file path to the local path
       3.Specify the Database Name that you're using in the content of SQL file
       */
       Invoke-Sqlcmd -InputFile "C:\path\test.sql" -ServerInstance SERVERNAME -Database [DatabaseName] -verbose *> "C:\path\testoutput.txt"

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 25, 2018 3:15 AM
    Moderator
  • I think that using SELECT instead of PRINT is simpler, but...

    Yes, this solution should work :-)
    Adding the parameter "-verbose" and using "*>" instead of ">" will return the the verbose including the query result to the file.
    There are more options to do this like using "4>&1>" instead of "*>" (4 is the "verbose" stream), and there are more options...

    * Explanation: The parameter verbose returns the result of the PRINT to the shell we are using, and use of *> move all to the file.

    * by the way, on old versions of PowerShell like version 3, I think that we need to use "*>>" instead of "*>"

    * The advantage of using "4>&1>" over using "*>" is that this will write the QUERY result and the "verbose" stream, but it will not write to the file ERROR that we get. Using "*>" will write errors as well and we will not know that an error raised. If we do want to write errors to the file we can use 2 which is the error stream...

    This gave me an idea for a short blog regarding more options that we have, for example we can return the PRINT to one file and the QUERY to another file, we can return the SELECT result to file and the PRINT result to the screen, we can return the SELECT result to the screen and the PRINT to the file, and more...

    Blog is in the way :-)

    * P.s.
    There is no problem in using Database name in the query, for example like this: SELECT * [tempdb].[dbo].[T]


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Tuesday, September 25, 2018 6:48 AM
    Moderator
  • Here it is :-)

    A special post based on this discussion, with all the relevant information
    How to control the output of a QUERIES including the PRINT statement and ERROR massages using PowerShell


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, September 25, 2018 7:13 PM
    Moderator
  • Hi Ronen,

    Thanks for the amazing blog! The "*>" operator works perfectly fine in redirecting all kinds of messages unless the number of rows returned are not equal to zero. But I also want the file to catch the message "((0) row(s) affected" from invoke-sqlcmd cmdlet i.e when the query returns no results in select query as well! Not sure if anyone covered this in the above posts.

    Appreciate your help!

    Mike.

    Thursday, September 27, 2018 6:26 PM
  • Hi Ronen,

    Thanks for the amazing blog! The "*>" operator works perfectly fine in redirecting all kinds of messages unless the number of rows returned are not equal to zero. But I also want the file to catch the message "((0) row(s) affected" from invoke-sqlcmd cmdlet i.e when the query returns no results in select query as well! Not sure if anyone covered this in the above posts.

    Appreciate your help!

    Mike.

    :-)

    Good question... I should add explanation in the blog on this case, which is again... very simple to explain but I don't think there is any documentation about it as well (I might be mistaken)

    The reason you do not get the result in the file is not related to the SQL Server or to the redirection of the result to the file, but to the PowerShell itself and to the command invoke-sqlcmd specifically. PowerShell simply does not return the stream after the empty SELECT.

    For example: Use the table from my blog and delete all the rows. Next execute this command:

    invoke-sqlcmd -Query "PRINT 'This you see'; select * from T; PRINT 'But What About Me?'" -serverinstance ".\serverinstanceName" -database "DatabaseName" -Verbose

    You can see the first PRINT, but not the one after the SELECT.

    Moreover, some errors are totally ignored if there is not success stream before (for the same reason). For example execute the next code:

    invoke-sqlcmd -Query "select 1/0" -serverinstance ".\serverinstanceName" -database "DatabaseName" -Verbose

    But check this magic :-)
    I will use the exact same query above but this time I will produce some Success Stream by adding simple SELECT query

    invoke-sqlcmd -Query "select ' ';select 1/0" -serverinstance ".\serverinstanceName" -database "DatabaseName" -Verbose

    By adding a simple SELECT query before the real query, we create some Success Stream and now the error appear :-)

    But what about the SELECT query?

    A simple solution is to add a SELECT with the same number of rows as our real select if you want to get the data, for example we can use:

    invoke-sqlcmd -Query "SELECT '','','','',''; select * from T; PRINT @@ROWCOUNT" -serverinstance ".\serverinstanceName" -database "DatabaseName" -Verbose

    Since the @@ROWCOUNT returns the number of rows effected in the last statement, it will return the right number. Try to add some rows and check this.

    Why it must be the same number of rows? Check this quote from the doc:

    "When this cmdlet is run, the first result set that the script returns is displayed as a formatted table. If subsequent result sets contain different column lists than the first, those result sets are not displayed."

    Same with our file. Simply add to the file at the beginning this simple query

    select '','','','',''<add column according to the table structure>;

    Another option is to use UNION ALL and add to the select another fake row. This way the select always return at least 1 rows and the value of @@ROWCOUNT includes this row so you need to reduce 1 from the value.

    =================

    I want to clarify that personally I would not use this solution probably (PS invoke-sqlcmd) unless this is a very simple case. This function includes a lot of strange behavior with you must familiar. For example the number of rows you can get in the client side and no need to get it from the file (from the SQL Server).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, September 28, 2018 2:33 AM
    Moderator