Powershell Invoke-Sqlcmd doesn't capture all verbose output

Question

I created following powershell (v2) script to execute query on target database and build another query/queries to be run:

$sql = @"
DECLARE @Tables CURSOR;
DECLARE @TableName NVARCHAR(200);

BEGIN
    SET @Tables = CURSOR FOR
    SELECT TABLE_NAME
    FROM test.INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME LIKE 'x%'

    OPEN @Tables 
    FETCH NEXT FROM @Tables 
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'SELECT TOP 1 * FROM test.dbo.' + @TableName
        FETCH NEXT FROM @Tables 
        INTO @TableName 
    END; 

    CLOSE @Tables ;
    DEALLOCATE @Tables;
END;
"@
$ps = [PowerShell]::Create()
$ps.AddCommand("Invoke-Sqlcmd").AddParameter("Query", $sql).AddParameter("Verbose")
$ps.Invoke()

$sqlOutput = $ps.Streams.Verbose
$sqlOutputToRun = $nul
$sqlOutput | foreach {
    $sqlOutputToRun += ($_.ToString() + "`n")
}
$sqlOutputToRun = @"
$sqlOutputToRun
"@

$sqlOutputToRun

In the end of script I am printing queries that have to be executed, but unfortunately variable $sqlOutputToRun contains only about 3/4 expected output. I suppose that output is somehow cut because of the size, but I am not sure how I can extend it.

Do you know what is missing?

Why use a `CURSOR` to do this? Why not use the `STUFF` and `FOR XML PATH` method to do this in one hit? — Thom A, Jan 28 '19 at 15:10
I pasted you only part of the script. Script contains many more operations and cursors are more flexible for me to handle it, but it's redundant information for describing this issue. — bontade, Jan 28 '19 at 15:12
Why are you invoking PowerShell in a new environment, rather than just calling `Invoke-Sqlcmd` (or `sqlcmd.exe`, if you need pure textual output) directly and piping the output? — Jeroen Mostert, Jan 28 '19 at 15:14
Because I couldn't get output of query. Output contains text generated by PRINT command and not just results from database. This is the only solution I found (https://stackoverflow.com/questions/4511498/powershell-invoke-sqlcmd-capture-verbose-output) — bontade, Jan 28 '19 at 15:17
OK, then my obvious next question would by why you're using `PRINT` instead of `SELECT` to capture the output as a proper result set, which is actually what `Invoke-Sqlcmd` is designed to work with. `PRINT` has its own issues with strings getting cut off. (Having said that, `Invoke-Sqlcmd` may not handle multiple result sets correctly -- per Larnu, if you can't fit it all in one query, consider inserting intermediate results in a table variable, then selecting that at the end of everything, which is still easier than capturing `PRINT` statements.) — Jeroen Mostert, Jan 28 '19 at 15:21
Because I have to build a kind of dynamic query depending on multiple conditions, flags, table states, etc. It's a generic solution that avoids many manual steps. — bontade, Jan 28 '19 at 15:23
Fine, but even if you're married to a cursor loop, replacing `PRINT` with `INSERT @queries(query) SELECT 'MySuperDuperDynamicQuery'` and doing `SELECT * FROM @queries` at the end should be a valid approach in that case. In my experience, when you're having any kind of trouble with `PRINT`, the problem is usually that you're using `PRINT`. — Jeroen Mostert, Jan 28 '19 at 15:35
Well, I have a query that builds script for replication. This query is tested and works fine by long time. The benefits are: query builds the output query (that can be shared) and builds query dynamically based on some db conditions. I agree that it might not be the best solution, but for now I was just looking for the solution that let me live with it without any SQL script modification. — bontade, Jan 29 '19 at 08:50

score 1 · Accepted Answer · answered Jan 28 '19 at 15:57

If you can't live without your cursor why you can't do something like this?

$sql = "
DECLARE @Tables CURSOR;
DECLARE @TableName NVARCHAR(200);
DECLARE @Results TABLE (ResultQuery NVARCHAR(MAX)) 

BEGIN
    SET @Tables = CURSOR FOR
    SELECT TABLE_NAME
    FROM testdb.INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    --AND TABLE_NAME LIKE 'x%'

    OPEN @Tables 
    FETCH NEXT FROM @Tables 
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @Results (ResultQuery)
        SELECT 'SELECT TOP 1 * FROM test.dbo.' + @TableName

        -- PRINT 'SELECT TOP 1 * FROM test.dbo.' + @TableName
        FETCH NEXT FROM @Tables 
        INTO @TableName 
    END; 

    CLOSE @Tables ;
    DEALLOCATE @Tables;
END;
SELECT * FROM @Results;"
$sqlOutputToRun = Invoke-Sqlcmd -query $sql
$sqlOutputToRun

score 1 · Answer 2 · answered Jan 28 '19 at 16:05

And once you dump the print statements in favor of a resultset, you can dump the cursor too, and just run a simple query like:

    select 'SELECT TOP (1) * FROM '+ quotename(table_catalog) + '.' + quotename(table_schema) + '.' + quotename(table_name)
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME LIKE 'x%'

so in Powershell:

$sql = @"
        select 'SELECT TOP (1) * FROM '+ quotename(table_catalog) + '.' + quotename(table_schema) + '.' + quotename(table_name)
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE='BASE TABLE' 
       -- AND TABLE_NAME LIKE 'x%'
"@

$sqlOutputToRun = $nul

invoke-sqlcmd $sql | foreach { 
     $sqlOutputToRun += $_[0] + "`n"
}

$sqlOutputToRun

Powershell Invoke-Sqlcmd doesn't capture all verbose output

2 Answers2