Home Dashboard Directory Help
Search

Batch using non-existent SEQUENCE runs with no error by wBob


Status: 

Closed
 as Won't Fix Help for as Won't Fix


9
0
Sign in
to vote
Type: Bug
ID: 765560
Opened: 10/1/2012 9:00:30 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

When running a batch of SQL in SQL 2012 which references a non-existent SEQUENCE object the batch completes erroneously with message "Command(s) completed successfully".

Update: expected 208 error does appear when tracing session in Profiler but not with Extended Events.
Details
Sign in to post a comment.
Posted by Erland Sommarskog on 3/2/2013 at 9:31 AM
There are two ways this bug can strike:
1) The sequence is missing when you attempt to create the procedure. You will not get an error, but the procedure will not be created. This cause problems in a deploy script, where the failure to create the procedure could go unnoticed which could cause problems later.
2) The sequence is dropped after the procedure is created. When you try to execute the procedure, there is no error, but the procedure does not run. If the call is inside another stored procedure, that procedure will continue with the next statements with default settings.

Both cases are mitigated by the fact that if you follow best practice and use TRY-CATCH and SET XACT_ABORT ON, execution will not continue. You will not get an error message from SQL Server, but you may get an error from the client API. The repro below demonstrats:

USE tempdb
GO
SET XACT_ABORT OFF
SET NOCOUNT ON
go
CREATE SEQUENCE loopCounter START WITH 1 INCREMENT BY 1;
go
CREATE PROCEDURE inner_sp AS
DECLARE @tables TABLE ( objectId INT PRIMARY KEY,
                        objectName char(1) UNIQUE NOT NULL,
                        x INT DEFAULT NEXT VALUE FOR loopCounter )

PRINT 'Entering inner_sp'
INSERT @tables (objectId, objectName) VALUES (1, 'x')
SELECT * FROM @tables
go
CREATE PROCEDURE tryless_sp AS
PRINT 'Calling inner_sp from tryless'
DECLARE @ret int
EXEC @ret = inner_sp
SELECT @ret as "@ret", @@error as "@@error"
PRINT 'Call to inner from tryless completed'
go
CREATE PROCEDURE try_sp AS
BEGIN TRY
PRINT 'Calling inner_sp from try_sp'
EXEC inner_sp
PRINT 'Call to inner from from try_sp completed'
END TRY
BEGIN CATCH
PRINT 'Caught error in try_sp, attempting re-throw'
; THROW
END CATCH
PRINT 'Exiting outer_sp'
go
EXEC try_sp
PRINT '--------------------------------'
go
DROP SEQUENCE loopCounter
go
EXEC tryless_sp
EXEC try_sp
go
DROP PROCEDURE inner_sp, tryless_sp, try_sp

The output in SSMS is:

Calling inner_sp from try_sp
Entering inner_sp
objectId    objectName x
----------- ---------- -----------
1         x         2

Call to inner from from try_sp completed
Exiting outer_sp
--------------------------------
Calling inner_sp from tryless
@ret        @@error
----------- -----------
NULL        0

Call to inner from tryless completed
Calling inner_sp from try_sp
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Note that with tryless_sp, execution continues (this changes if XACT_ABORT is ON). The error message comes from SSMS or SqlClient. You don't get it with SQLCMD that uses ODBC.

Posted by Matija Lah on 2/28/2013 at 6:13 AM
At the very least, please document this completely unexpected behavior.
Posted by Microsoft on 2/27/2013 at 3:04 PM
Hello,
We investigated the problem and unfortunately the fix is not an easy one based on the current implementation. Given that the scenario is not common enough, I am closing the bug as "won't fix".

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 10/10/2012 at 2:07 PM
Hello,
Thanks for reporting the issue. We will take a look at the problem & get back to you.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Non-existent SEQUENCE Bug.sql 10/1/2012 603 bytes
Non-existent SEQUENCE Bug.sql 10/1/2012 603 bytes