3

I've written a powershell script to call sqlcmd.exe to execute a sql script against a remote sql server. The powershell script checks $LASTEXITCODE. If $LASTEXITCODE is non zero, I throw "Script failed. Return code is $LASTEXITCODE."

The script is used multiple times to execute different sql scripts and is part of a chain of powershell scripts that run during deployment.

The script runs fine most of the time but randomly fails with a return code of -1073741502.

This has only started happening after upgrading to SQL2008 and I cannot reproduce it by running either the single powershell script manually or the sql cmd script manually.

This is the powershell command:

& 'sqlcmd.exe' -S $databaseServer -r -b -E -i '$scriptFullPath'

if($LASTEXITCODE -ne 0) { throw "Script failed. Return code is $LASTEXITCODE." }

The seemingly random nature of the failure is causing a lot of pain. I can't determine if the error is SQL2008, SQLCMD (Although I get the same behaviour with osql.exe) or somehow coupled to powershell.

The actual sql that sqlcmd is executing appears to be unrelated to the problem since a sql script will execute ok for a while and then fail.

The same failure has been seen on many different Workstations and Servers (Win7, Win2003 and Win2008)

Any guidance on how to track this down would be much appreciated.

richard wadsworth
  • 131
  • 1
  • 2
  • 5
  • 1
    Might be a known problem: [see: connect.microsoft.com](http://connect.microsoft.com/PowerShell/feedback/details/496326/stability-problem-any-application-run-fails-with-lastexitcode-1073741502#details) – jon Z Jan 04 '12 at 14:08
  • @user978511 - it's not application specific. Note jon's link. – JNK Jan 04 '12 at 14:21
  • @jon Z. Thanks for the link. Well found. Glad I'm not alone in this madness. – richard wadsworth Jan 04 '12 at 14:52

2 Answers2

1

Don't use sqlcmd and command lines if you are using Powershell!

There are very good built-in tools in V2 that give you a lot more robust interaction with SQL Server, and don't require you to parse the text of return codes to check for errors - they actually return proper error codes.

Here's a technet article on Invoke-SQLCmd

Bear in mind you need to load the snapins first:

Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
JNK
  • 58,244
  • 14
  • 113
  • 132
  • It will only work if you have SQL server installed on the same box that is most of the time not the case – Andrey Marchuk Jan 04 '12 at 14:09
  • Not true. You need SSMS installed but not a full server. I use this every day on development machines that connect to remote servers. – JNK Jan 04 '12 at 14:10
  • For a solution that doesn't require SSMS or SQL, checkout the invoke-sqlcmd2 function: http://poshcode.org/2950 – Chad Miller Jan 05 '12 at 03:07
  • Still limited functionality. "**Much** of what you can do with sqlcmd can also be done using Invoke-Sqlcmd." – Monso Feb 15 '14 at 00:26
  • @Monso - try it. If you want to run SQL in PS, it can do whatever you need. – JNK Feb 15 '14 at 19:11
  • I did research and tried it, I couldn't get a lot to work. SqlCmd still gives more options. It can't do whatever **I** need. – Monso Feb 15 '14 at 19:46
  • @Monso Can you be more specific than "whatever"? – JNK Feb 17 '14 at 01:17
  • The only compelling feature of Invoke-Sqlcmd is that it returns result sets as objects rather than text. If you don't need to operate on a result set, use sqlcmd.exe instead because it is more robust and better-supported. – Iain Samuel McLean Elder Aug 20 '15 at 11:01
  • Just note that Microsoft avoids using invoke-sqlcmd in DSC because of it's incompatibility with workflows and other issues that have risen up. I had to change all my code to use sqlcmd or osql due to it's issues. – Ali Razeghi May 17 '16 at 14:27
0

The root-cause is actually explained elsewhere on here

Exit Code -1073741502 is 0xC0000142 in hex (status_dll_init_failed). Microsoft's KB2701373 addresses the leaky handles made by Console.Write in Microsoft.powershell.consolehost.dll.

Side-Note: Some "Fixes" on the Internet involve doing something differently, then restarting PowerShell. Yet the actual restart of PowerShell is what addresses the problem (temporarily)

Community
  • 1
  • 1
Signal15
  • 538
  • 5
  • 16