Invoke-Sqlcmd get query output and messages

[ianmcwright]

Hi

From this simple sp I want to get the result of OutPutInt1 into a variable and the print statements in a text file.

CREATE PROC SP_TEST
AS
PRINT 'this is my output'
PRINT 'this is should be in the text file'
SELECT OutPutInt1 = 999

I'm trying things like:

$CommandQuery = "'EXEC SP_TEST'"
$OutPutInt1 = Invoke-Sqlcmd -AbortOnError -ServerInstance $SQLServerName -Database $Databasename -Query $CommandQuery -QueryTimeout 0 2>> $SQLLogFile

I can get the results output or the messages but not both, any ideas?

Thanks