Invoke-Sqlcmd sometimes reports TSQL errors and sometimes it does not?

Question

I'm confused:

The following code produces an error:

try {
    invoke-sqlcmd -ServerInstance 'localhost' -Database 'tempdb' -Query 'CREATE TABLE foo (bar TINYINT IDENTITY(1,1) DEFAULT 1);;' -Verbose -ErrorLevel 0 -AbortOnError -ErrorAction Stop -OutputSqlErrors $true -ErrorVariable $err -OutVariable $err -SeverityLevel 0;
    "OK";
}
catch {
    "ERROR"
    $_
}

Result:

ERROR Invoke-Sqlcmd : Defaults cannot be created on columns with an IDENTITY attribute. Table 'foo', column 'bar'.

This piece runs apparently smoothly:

try {
    invoke-sqlcmd -ServerInstance 'localhost' -Database 'tempdb' -Query 'SELECT CAST(111111111111 AS TinyINT) AS Error' -Verbose -ErrorLevel 0 -AbortOnError -ErrorAction Stop -OutputSqlErrors $true -ErrorVariable $err -OutVariable $err -SeverityLevel 0;
    "OK"
}
catch {
    "ERROR"
    $_
}

Result:

OK

In both cases the catch-block must be used!

Chad Miller · Accepted Answer · 2013-04-21T12:54:19.727

3

It's a known bug with invoke-sqlcmd. I logged it on Connect, the item is marked as fixed but "fixed" in connect terms doesn't necessarily mean the fix has been released or is planned for release in the current version. It just means that it has been fixed internally and they'll release it someday.

As of 4/19/2013, the fix has NOT been released.

edited Apr 21 '13 at 12:54

answered Apr 20 '13 at 00:53

Chad Miller

40,127
3
30
34

Thanks Chad! Do you have workaround for this issue? How do you handle it? – eghetto Apr 22 '13 at 07:02
My workaround is not use invoke-sqlcmd or at least only use it when I don't care about error handling. I'll either use my own replacement or sqlcmd.exe. A few more details here http://sev17.com/2012/11/12/sqlcmd-is-dead-long-live-sqlcmd/ – Chad Miller Apr 22 '13 at 11:41
This issue is fixed in SQL 2014 CTP2. you can download SQL 2014 CTP2 from http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx – eghetto Feb 07 '14 at 13:34

score -2 · Answer 2 · answered Apr 19 '13 at 14:18

-2

I suspect this has something to do with the configuration of the connection initiated by Invoke-SqlCmd; in particular, having ANSI_WARNINGS set to OFF in SQL Server allows your TSQL block to be evaluated as a NULL.

SET ANSI_WARNINGS OFF
SELECT CAST(111111111111 AS TinyINT) AS Error

Returns NULL.

answered Apr 19 '13 at 14:18

Efran Cobisi

6,138
22
22

Thanks Efran, but I don't think that's the case here: _SET ANSI_WARNINGS ON; SELECT CAST(111111111111 AS TinyINT) AS Error_ does not help either. – eghetto Apr 22 '13 at 06:57

Invoke-Sqlcmd sometimes reports TSQL errors and sometimes it does not?

2 Answers2