PS Invoke-SqlCmd output to file is empty

Article
2009-12-11

Question

_{Friday, December 11, 2009 7:29 PM}

I'm trying to run a script using the following:

invoke-sqlcmd -serverinstance PC01 -database DEV -inputfile "D:\BuildDatabase.sql" -verbose | out-file -filepath "D:\SqlLog.txt"

The script generates an error (in red), as well as some print statements (in yellow), that are displayed on the powershell console when run. However, no output is written to the log file.

The SqlLog file is created successfully, but it is empty.

The messages I want to put into the file are displayed on the console screen. I just can't get them there.

All replies (9)

_{Saturday, December 12, 2009 10:48 AM ✅Answered}

Like Tome said, please post the error so it is easier to be able to help.
Probably a problem with your script. Sql happened.
If you want to delete the mensens error (red) and warning (yellow) use
-OutputSqlErrors $false -WarningAction SilentlyContinue
only so that you will not have any information of problems ocurred.
Then you can try (PS 2.0)

$Error.Clear()
try {
    invoke-sqlcmd -serverinstance $env:computername -database DBA -inputfile "C:\temp\teste.sql" -WarningAction SilentlyContinue  -OutputSqlErrors $false
}
 catch {
    $Error | out-file -filepath "C:\temp\SqlLog.txt"
}

Type : Help Invoke-sqlcmd -full for more details !!!

www.laertejuniordba.spaces.live.com

_{Tuesday, December 15, 2009 2:01 PM ✅Answered}

Thanks. I wound up using a combination of Laerte's idea and this: http://keithhill.spaces.live.com/blog/cns!5A8D2641E0963A97!6926.entry

I put my DB script running related stuff into a separate file, and call that from my main file. This way, Adding the snapins for SQL nicely separated too.

I call it in my mains script like this, redirecting all the output to my log file:
powershell.exe -noprofile -nonInteractive -NoLogo -file $executingScriptDirectory\RunDBScript.ps1 -targetDatabaseServer $targetDatabaseServer -targetDatabaseName $targetDatabaseName -sqlInputFile $sqlInputFile > $sqlLocalLogFile

Then I use this in the RunDBScript.ps1:
param($targetDatabaseserver, $targetDatabaseName, $sqlInputFile)

$WarningPreference = "Continue"
$VerbosePreference = "Continue"

Invoke-Sqlcmd -ServerInstance "$targetDatabaseServer" -Database "$targetDatabaseName" -InputFile $sqlInputFile -verbose -outputsqlerrors $true

if ($error.count -gt 0)
{
    write-output "SCRIPT ERRORS:"
    write-output ""
    write-output $error
}

exit $error.count

This returns the number of errors to the calling script, so that I can easily report the number of errors that happen, by checking the $LASTEXITCODE variable, and keep the full log in the log file.

In order to properly get error information AND warnings, I seemed to have to use -outputsqlerrors $true and -verbose when I called Invoke-SqlCmd.

_{Friday, December 11, 2009 8:32 PM}

In the future please post the exact error you are getting in red. It will help get to the root of the problem more quickly.

Chances are it's because you are in the SQL context. Try typing the following:

c: <enter>

Then run your command.

The strange thing is that this works:

invoke-sqlcmd -serverinstance PC01 -database DEV -inputfile "D:\BuildDatabase.sql" -verbose > "d:\sqllog.txt"

I'm not sure if someone else can explain this better than I, but it looks like the sql shell will give you an unauthorizedAccessException if you attempt to do outfile from the SQL server context.

_{Friday, December 11, 2009 8:35 PM}

Okay, this is strange. Once I got it to work I can't seem to duplicate the problem. Now I run the same command from SQLSERVER: and it is working fine. I've tried closing my powershell sessions and disconnecting from sql then reconnecting, but I can't duplicate the problem.

_{Monday, December 14, 2009 2:48 PM}

Thanks for the suggestions... the error message looks like this... typical errors that are reported by SQL server if I run the same script in enterprise manager.

============================
Invoke-Sqlcmd : The CREATE UNIQUE INDEX statement terminated because a duplicat
e key was found for the object name 'dbo.MyTable1' and the index name 'PK_MyTable1'.
The duplicate key value is (17, 19).
Could not create constraint. See previous errors.
The statement has been terminated.
At line:1 char:14

invoke-sqlcmd <<<< -serverinstance DEVDB01 -database TestDEV -inputfile "
D:\Testing\BuildDatabase.sql" -verbose -warningaction silentlycontinue
-outputsqlerrors $true | out-file -filepath "D:\Testing\BuildDBTestCMDLog.txt"
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
   hellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
   ll.GetScriptCommand

VERBOSE: The module 'spMyCustomSproc' depends on the missing object 'master.dbo.sp_send_cdosysmail'. The module will still be created; however, it cannot run successfully until the object exists.

I am using -verbose in order to get warning/print messages as well. I'm not trying to solve the SQL error, that is another issue. I just want powershell to run the script and dump the errors into a file so we can use this as part of a CI build process.

I have been running the command from the PS D:\ context and not the SQLSERVER context.

I did find this: https://connect.microsoft.com/PowerShell/feedback/ViewFeedback.aspx?FeedbackID=491178
It could be the source of my problem, but I'm not sure. The script I have to run is very long and I can't easily go thru it to look for cases that match this reported problem.

I will try Laerte's suggestion using the try-catch.

_{Monday, December 14, 2009 3:47 PM}

According to that error your problem is with your sql script.

Can you run that script in SSMS, Query Analyzer, or via sqlcmd? You should get the same error there.

Specifically it means that you are creating a unique index where a duplicate key exists. You'll need to drop it first or add some logic to test that. If you experience the same problem with the above mentioned tools, and you cannot figure out how to fix it you will need to post a new question in one of the SQL forums. Make sure to post your script and the error.

_{Monday, December 14, 2009 3:56 PM}

Yes, I have created the failing SQL script on purpose to ensure my build scripts will properly capture output to a log file.

I'm not trying to get the script to run successfully. I am trying to get powershell to output the errors to a log file. I suspect the issue is related to this:

http://keithhill.spaces.live.com/blog/cns!5A8D2641E0963A97!6926.entry

https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=297055&SiteID=99&wa=wsignin1.0

It appears that for now, my best bet may be to use Start-Transcript

_{Monday, December 14, 2009 4:04 PM}

In My scripts I use (try catch ) and -OutputSqlErrors $false -WarningAction SilentlyContinue

In the catch I use a function to write into a .log or local eventlog, depends on the situation.www.laertejuniordba.spaces.live.com

_{Friday, July 27, 2012 12:15 PM}

There is no easy way to redirect the output like you see in the management studio with invoke-sqlcmd and out-file.

I worked around this in another way:

$SqlCmd = "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"

Start-Process -FilePath $SqlCmd -Wait -RedirectStandardOutput "D:\SqlLog.txt" -Passthru -ArgumentList " -S PC01 -d DEV -i "D:\BuildDatabase.sql"

You can use the -m parameter from sqlcmd to set error level.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

Share via

PS Invoke-SqlCmd output to file is empty

Question

All replies (9)

VERBOSE: The module 'spMyCustomSproc' depends on the missing object 'master.dbo.sp_send_cdosysmail'. The module will still be created; however, it cannot run successfully until the object exists.

Additional resources