osql vs Invoke-Sqlcmd-- redirecting output of the latter

Question

We're moving from a batch file that calls osql to a Powershell script which uses the Invoke-Sqlcmd cmdlet.

Would anyone know what the equivalent steps are for redirecting the output in the latter case, to using the -o flag in osql? We have some post-processing steps that look at the osql output file and act accordingly (report an error if those logs are greater than X bytes). I would very much like it if Invoke-Sqlcmd could duplicate the same output information given the same SQL commands going in.

Right now in my script I'm planning to call Invoke-Sqlcmd <...> | Out-file -filepath myLog.log. Anyone know if this is ok or makes sense?

score 1 · Answer 1 · answered Aug 29 '12 at 17:52

1

From the documentation for the cmdlet itself:

Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

The above is an example of calling Invoke-Sqlcmd, specifying an input file and piping the output to a file. This is similar to specifying sqlcmd with the -i and -o options.

http://technet.microsoft.com/en-us/library/cc281720.aspx

answered Aug 29 '12 at 17:52

manojlds

259,347
56
440
401

Thanks but does osql follow the same pattern as sqlcmd? I ask because in my powershell script I've swapped out osql -o for the Invoke-Sqlcmd Out-File bit and the resulting output files (same SQL commands) are different. The osql version appears to only output errors whereas Invoke-Sqlcmd returns info like the result of SELECT statements. – larryq Aug 29 '12 at 19:20

score 1 · Answer 2 · answered Aug 30 '12 at 01:07

I think you'll find it's difficult to reproduce the same behavior in invoke-sqlcmd as I have.

osql and sqlcmd.exe will send T-SQL PRINT and RAISERROR and errors to the output file.

Using Powershell you can redirect standard error to standard output with the standard error redirection technique (2>&1):

Invoke-Sqlcmd <...>  2>&1 | Out-file -filepath myLog.log

However this still won't catch everything. For example RAISERROR and PRINT statements only output in Invoke-sqlcmd when using the -verbose parameter as documented in help invoke-sqlcmd. In Powershell V2 you can't redirect verbose output. Although you can with Powershell V3 using 4>

For these reason and others (like trying to recreate all the many different options in sqlcmd) I switched back to using sqlcmd.exe for scheduled job in my environment. Since osql.exe is deprecated, I would suggest switching to sqlcmd.exe which supports the same options as osql.

score 1 · Answer 3 · answered Sep 04 '12 at 01:44

You can still call osql from PowerShell. I would continue to do just that. Invoke-SqlCmd returns objects representing each of the rows in your result set. If you aren't going to do anything with those objects, there's no reason to upgrade to Invoke-SqlCmd.

osql vs Invoke-Sqlcmd-- redirecting output of the latter

3 Answers3