January 21, 2015 at 11:12 am
I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)
The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:
CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
SELECT * FROM test_function(1, @t);
When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)
The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:
- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)
- The SELECT statement has a CTE
- The TVP is accessed within the CTE
- The outer FROM clause references the CTE
- There is also a scalar parameter on the function (scalar type does not seem to matter).
- The scalar parameter precedes the TVP in the parameter list.
So I have an easy work-around: put the TVP first in the parameter list. But I believe this result may be a bug, and I am also interested in which versions of SQL Server have this problem. (and I don't have all of them available to me)
January 21, 2015 at 2:53 pm
Stephanie Giovannini (1/21/2015)
I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:
CREATE TYPE test_list AS TABLE(a int);GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
SELECT * FROM test_function(1, @t);
When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)
The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:
- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)
- The SELECT statement has a CTE
- The TVP is accessed within the CTE
- The outer FROM clause references the CTE
- There is also a scalar parameter on the function (scalar type does not seem to matter).
- The scalar parameter precedes the TVP in the parameter list.
So I have an easy work-around: put the TVP first in the parameter list. But I believe this result may be a bug, and I am also interested in which versions of SQL Server have this problem. (and I don't have all of them available to me)
FWIW: Running almost the same code on SQL Server 2014 without any errors, the only difference is the second last line, INSERT INTO instead of only INSERT and the column name. Runs fine even without the changes but I'm just slightly pedantic;-)
😎
USE tempdb;
GO
SET NOCOUNT ON
CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT INTO @t(a) VALUES(1);
SELECT * FROM test_function(1, @t);
January 21, 2015 at 4:24 pm
This is interesting. I tried the code in my computer and it just hangs indefinitely. If I remove the CTE it runs immediately. If I use a subquery it runs immediately.
I just can't imagine what's going on.
My version:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
January 21, 2015 at 5:36 pm
Runs fine on this version of SQL:
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Hangs when I run the SELECT from the function on this version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
Interesting indeed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2015 at 6:04 pm
I can also confirm that in SQL 2008 if you remove the CTE it works.
This also works, but sort of defeats the purpose.
CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION dbo.test_function (@theTable test_list READONLY)
RETURNS @Results TABLE (a INT)
AS BEGIN
WITH cte (a)
AS
(SELECT a FROM @theTable)
INSERT INTO @Results
SELECT cte.a
FROM cte;
RETURN
END
GO
DECLARE @a test_list;
INSERT @a (a) VALUES(1);
SELECT * FROM test_function(@a);
GO
DROP FUNCTION dbo.test_function;
DROP TYPE dbo.test_list;
Note that adding WITH SCHEMABINDING to the original also did not make any difference.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2015 at 7:42 am
I found a related bug report from 2008 RTM.
It looks like this issue wasn't fully fixed until version 2012 because I'm running 2008 R2 and it's still a problem. Although I have a work-around for 2008, I'm verifying that production, in this case, is 2012.
Thanks for looking into this and confirming that I'm not seeing things that aren't there!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply