Quick interesting one today, it’s come about as i was writing a script to export all the results from sp_help_revlogin, if you’ve used this before you know that it doesn’t write anything to a table. it writes everything to a PRINT statement. like below:

PowerShellPrint00

 

But if you try a query like this in PowerShell

IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location 
    }

Invoke-Sqlcmd -ServerInstance . -Database master -Query "PRINT 'Export This result'" | Out-File C:\Temp\Test.txt

The Test.txt file will be empty.

After playing around a bit, I found that if you use Invoke-Sqlcmd2, which you probably should just through the timeout issue of Invoke-Sqlcmd, it has a -Verbose option. which shows the messages output! Handy..

IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location 
    }

# Dot source Invoke-Sqlcmd2
. c:/PowerShellScripts/Invoke-Sqlcmd2

Invoke-Sqlcmd2 -ServerInstance . -Database master -Query "PRINT 'Export This result'" -Verbose | Out-File C:\Temp\Test.txt

PowerShellPrint01

So can we just do


Invoke-Sqlcmd2 -ServerInstance . -Database master -Query "PRINT 'Export This result'" -Verbose | Out-File C:\Temp\Test.txt

No, sadly this still leaves us with a blank output file, as the Verbose text isn’t caught in default steam, I’ve blogged about Verbose before here and here so we know we should get the results with!

IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location 
    }

# Dot source Invoke-Sqlcmd2
. c:/PowerShellScripts/Invoke-Sqlcmd2

Invoke-Sqlcmd2 -ServerInstance . -Database master -Query "PRINT 'Export This result'" -Verbose 4> Out-File C:\Temp\Test.txt

Answer! Yes we do! 🙂 so there we go. Hopefully you found this useful.

 

 

Advertisements