0

I am trying to get the output of the queries executed in SQLSErver database using sql script through Powershell. I am using Invoke-SQLCMD to get this done with verbose parameter. Its my understanding that using of "verbose" should capture all messages, error messages if any and all query results in a output log file . But when I check the log file, I get only the output of the first query from the SQL Script.

I even checked the script commands are executing successfully but the output is not logged. Can anyone help on this.

My Invoke-sqlcmd Command is

Invoke-Sqlcmd -ServerInstance MSSERVER -Database BikeStores -InputFile testsql.sql -Verbose *>> "test.log"

My sql File contains following code

use[BikeStores]

select name from sys.databases; GO

select * from [BikeStores].[production].[brands]; GO

SELECT brand_id,brand_name INTO [BikeStores].[production].[test_table2] FROM [BikeStores].[production].[brands]; GO select * from [BikeStores].[production].[test_table2];

update production.test_table2 set brand_name='xxx' where brand_id=1;

select * from [BikeStores].[production].[test_table2];

delete from BikeStores.production.test_table where brand_id=1; drop table BikeStores.production.test_table2;

My Output is as below.

name      
----      
master    
tempdb    
model     
msdb      
BikeStores

I expect the code to put the following lines. Ideally speaking, the script can contain any DML or DDL statements, it should capture the query and output messages, results.

    name      
    ----      
    master    
    tempdb    
    model     
    msdb      
    BikeStores     
    
    brand_id    brand_name
        1   Electra
        2   Haro
        3   Heller
        4   Pure Cycles
        5   Ritchey
        6   Strider
        7   Sun Bicycles
        8   Surly
        9   Trek



SELECT brand_id,brand_name INTO [BikeStores].[production].[test_table2] FROM [BikeStores].[production].[brands];
GO
(9 rows affected)

Completion time: 2022-08-13T17:03:51.1934168+10:00


select * from [BikeStores].[production].[test_table2];


brand_id    brand_name
        1   Electra
        2   Haro
        3   Heller
        4   Pure Cycles
        5   Ritchey
        6   Strider
        7   Sun Bicycles
        8   Surly
        9   Trek

update production.test_table2
set brand_name='xxx'
where brand_id=1;

(1 row affected)

Completion time: 2022-08-13T17:04:40.6506035+10:00


select * from [BikeStores].[production].[test_table2];



brand_id    brand_name
        1   xxx
        2   Haro
        3   Heller
        4   Pure Cycles
        5   Ritchey
        6   Strider
        7   Sun Bicycles
        8   Surly
        9   Trek


delete from  BikeStores.production.test_table where brand_id=1;
(0 rows affected)

Completion time: 2022-08-13T17:05:11.4045005+10:00


drop table BikeStores.production.test_table2;

Commands completed successfully.

Completion time: 2022-08-13T17:05:37.9947206+10:00
Thom A
  • 88,727
  • 11
  • 45
  • 75
sandy
  • 1
  • Which version of PowerShell are you actually using? Have you read the [Invoke-Sqlcmd](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd) online documentation yet? `-Verbose` only outputs `PRINT` output, `-OutputSqlErrors` will output error messages. – AlwaysLearning Aug 13 '22 at 08:07
  • Powershell version - 5.1.19041.1320. – sandy Aug 13 '22 at 09:51
  • Powershell version - 5.1.19041.1320. Yes. I read the docs about Invoke-Sqlcmd but I didn't get output of other queries even for select statements., so. I used verbose as I was not getting any output . My actual query was **Invoke-Sqlcmd -ServerInstance MSSSERVER -Database BikeStores -verbose -ErrorVariable sqlerror -OutputSqlErrors $true -ErrorAction stop | Out-File test.log** . Is there any alternative apart from Invoke-SQLCMD if this is not desired to produce the output of multiple queries. – sandy Aug 13 '22 at 10:00

0 Answers0