Home Dashboard Directory Help
Search

invoke-sqlcmd does not return T-SQL Errors by cmille19


Status: 

Closed
 as Fixed Help for as Fixed


18
0
Sign in
to vote
Type: Bug
ID: 779320
Opened: 2/15/2013 10:53:35 AM
Access Restriction: Public
0
Workaround(s)
view
6
User(s) can reproduce this bug

Description

invoke-sqlcmd does not return errors. Settings with or without outputsqlerrors, abortonerror, severitylevel or errorlevel do not work as documented. This is unexpected behavior when compared to SSMS sqlcmd.exe which correctly return errors.

Details
Sign in to post a comment.
Posted by Sethu Srinivasan on 12/4/2013 at 12:02 PM
Kyle,
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

Powershell sample:
invoke-sqlcmd -ServerInstance . -Database tempdb -Query 'select 1/0' -IncludeSqlUserErrors;

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by Kyle Neier on 5/21/2013 at 5:22 AM
I'm interested - how do we proceed?
Posted by Sethu Srinivasan on 5/8/2013 at 3:47 PM
Hello Kyle,
We have a fix for this issue. Please let us know if you would be willing to help us validate this fix on your test machine



Thanks
Sethu Srinivasan [MSFT]
Posted by Kyle Neier on 5/8/2013 at 5:26 AM
Any update on where this is fixed? It appears to still be broken. However, If you put a select statement prior to the statement that is in error, it seems the errors are made available.

This returns nothing - regardless of what you put in as OutputSqlErrors or SeverityLevel:
invoke-sqlcmd -ServerInstance ".\SQL2012" -Query 'select 1/0'

This returns an error:
invoke-sqlcmd -ServerInstance ".\SQL2012" -Query 'select 1;select 1/0'

As does this:
invoke-sqlcmd -ServerInstance ".\SQL2012" -Query 'select 1;select 1/0' -OutputSqlErrors $true

This does not (which is expected behavior):
invoke-sqlcmd -ServerInstance ".\SQL2012" -Query 'select 1;select 1/0' -OutputSqlErrors $false

If you use InputFile, the leading select statement must be within the batch (between GO statements) that the error occurs. Running a select at the beginning of the file does not work for errors in a different batch.

Thanks!
Posted by cmille19 on 3/1/2013 at 7:56 PM
This connect was marked as fixed. What version and SP/CU is it fixed in? Can you provide any details?
Posted by lkh on 2/15/2013 at 11:23 AM
I am working with Powershell v3 ISE on Windows Server 2008 R2. I cannot figure out how to execute "only" a stored procedure specified in the -Query parameter of Invoke-Sqlcmd and get back any SQL error. This includes any use of -ErrorVariablea-OutputSqlErrors1-Verbose-AbortOnError-ErrorActionStop.

I am testing with divide by zero in a sproc. The error returns as expected in SSMS, SSIS, Sqlcmd from cmd prompt and '& Sqlcmd' in PowerShell. Invoke-Sqlcmd does return errors from a SELECT statement or from a stored procedure like this -Query "SELECT 1;EXEC dbo.usp_WithError".
Sign in to post a workaround.