Don’t trust Invoke-SqlCmd and error results
I love to call Invoke-sqlcmd from powershell but one must beware of the risk of being burned when it comes to trust in error handling. I wish Microsoft could fix these known bugs soon.
Top 3 Pitfalls to watch out for:
1) Invoke-Sqlcmd does not return raised error if query was run in Single user mode
--1) RUN IN SSMS with SQLCMD mode
---------------------
:on error exit
PRINT 'Change database to single-user mode'
ALTER DATABASE [Test1]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
PRINT 'After database to single-user mode'
IF EXISTS (select 1 as res)
RAISERROR (N'First Error.', 16, 127) WITH NOWAIT
GO
IF EXISTS (select 1 as res)
RAISERROR (N'Second Error', 16, 127) WITH NOWAIT
GO
ALTER DATABASE [Test1]
SET MULTI_USER;
GO
PRINT 'After database to multi-user mode'
RESULT
Change database to single-user mode
After database to single-user mode
Msg 50000, Level 16, State 127, Line 12
First Error.
** An error was encountered during execution of batch. Exiting.
--2) RUN IN POWERSHELL
$ServerInstance=".\localhost"
$timeout =0
$database="Test1"
$Query= "
PRINT 'Change database to single-user mode'
ALTER DATABASE [Test1]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
PRINT 'After database to single-user mode'
IF EXISTS (select 1 as res)
RAISERROR (N'First Error.', 16, 127) WITH NOWAIT
GO
IF EXISTS (select 1 as res)
RAISERROR (N'Second Error', 16, 127) WITH NOWAIT
GO
ALTER DATABASE [Test1]
SET MULTI_USER;
GO
PRINT 'After database to multi-user mode'"
try
{
Invoke-Sqlcmd -ServerInstance "$serverInstance" -DataBase $database -Query $Query -QueryTimeout $timeout -Verbose -ErrorAction Stop
}
catch
{
write-host "Caught an exception:" -ForegroundColor Red
write-host "Exception type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
write-host "Exception message: $($_.Exception.Message)" -ForegroundColor Red
write-host "Error: " $_.Exception -ForegroundColor Red
}
RESULT
VERBOSE: Change database to single-user mode
VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
VERBOSE: After database to single-user mode
Caught an exception:
Exception type: Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException
Exception message: Cannot open database "Test1" requested by the login. The login failed.
Login failed for user 'XXXX'.
Error: Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: Cannot open database "Test1" requested by the login. The login failed.
Login failed for user 'XXXX'. ---> System.Data.SqlClient.SqlException: Cannot open database "Test1" requested by the login. The login failed.
Login failed for user 'XXXX'.
2) Invoke-Sqlcmd does not return SP name nor line when error occurs in a procedure
--1) RUN IN POWERSHELL
$ServerInstance=".\localhost"
$timeout =0
$database="Test1"
$Query1= "
CREATE OR ALTER PROCEDURE dbo.geterror
as
create table #t(n int not null)
insert into #t(n) values(null)
go
"
$Query2=
"EXEC dbo.geterror"
try
{
Invoke-Sqlcmd -ServerInstance "$serverInstance" -DataBase $database -Query $Query1 -QueryTimeout $timeout -Verbose -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance "$serverInstance" -DataBase $database -Query $Query2 -QueryTimeout $timeout -Verbose -ErrorAction Stop
}
catch
{
write-host "Caught an exception:" -ForegroundColor Red
write-host "Exception type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
write-host "Exception message: $($_.Exception.Message)" -ForegroundColor Red
write-host "Error: " $_.Exception -ForegroundColor Red
}
RESULT
Caught an exception:
Exception type: Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException
Exception message: Cannot insert the value NULL into column 'n', table 'tempdb.dbo.#t__________________________________________________________________________________________________________________000000004B81'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
Error: Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: Cannot insert the value NULL into column 'n', table 'tempdb.dbo.#t__________________________________________________________________________________________
________________________000000004B81'; column does not allow nulls. INSERT fails.
--2) RUN IN SSMS
CREATE OR ALTER PROCEDURE dbo.geterror
as
create table #t(n int not null)
insert into #t(n) values(null)
go
EXEC dbo.geterror
RESULT
Msg 515, Level 16, State 2, Procedure geterror, Line 5 [Batch Start Line 0]
Cannot insert the value NULL into column 'n', table 'tempdb.dbo.#t__________________________________________________________________________________________________________________000000004B81'; column does not allow nulls. INSERT fails.
The statement has been terminated.
3) Invoke-Sqlcmd does return error for Arithmetic overflow error
--1) RUN IN POWERSHELL
$ServerInstance=".\localhost"
$timeout =0
$database="Test1"
$Query= "SELECT convert(int,100000000000)"
try
{
Invoke-Sqlcmd -ServerInstance "$serverInstance" -DataBase $database -Query $Query -QueryTimeout $timeout -Verbose -ErrorAction Stop
}
catch
{
write-host "Caught an exception:" -ForegroundColor Red
write-host "Exception type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
write-host "Exception message: $($_.Exception.Message)" -ForegroundColor Red
write-host "Error: " $_.Exception -ForegroundColor Red
}
RESULT
NO ERROR!
--2) RUN IN SSMS
SELECT convert(int,100000000000)
RESULT
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Plea for help
Our only option when faced with these problems is to partition and plea Microsoft fixing these errors in Invoke-Sqlcmd and here is the way todo it:
Let’s hope we get these fix soon, they are only top 3. There are more Invoke-sqlcmd bugs out there!
