Symptoms
On a computer that is running Microsoft SQL Server 2008, consider the following scenarios.
Scenario 1
You specify a common table expression (CTE) by using the WITH statement. For example, you run the following query.
WITH common_table_expression AS
(SELECT *
FROM sys.objects)
(SELECT *
FROM common_table_expression)
Then, you run this query.
SELECT *
FROM sys.dm_exec_query_stats
WHERE statement_start_offset > statement_end_offset
AND
statement_end_offset <> -1
In this scenario, the cached query plan for this query stores incorrect statement_start_offset and statement_end_offset values. Specifically, you notice the statement_end_offset value is smaller than the statement_start_offset value when you run sys.dm_exec_query_stats dynamic management view (DMV).
Because of this problem, a performance report may fail when the report assumes that the statement_start_offset value is always smaller than the statement_stop_offset value.
Scenario 2
You specify a common table expression by using the WITH statement, and you use the RECOMPILE option. For example, you create a function in SQL Server 2008 by using the following script.
CREATE FUNCTION function1() RETURNS int AS
BEGIN
DECLARE @x int
SET @x=1;
WITH common_table_expression AS
(SELECT @x AS column1)
(SELECT @x=column1
FROM common_table_expression)
OPTION(RECOMPILE)
RETURN @x
END
When you run a “select dbo.function1()” query, you receive the following error message:
A severe error occurred on the current command. The results, if any, should be discarded.
Additionally, this error can occur without the RECOMPILE option if the SQL server is experiencing heavy server traffic.
Scenario 3
You specify a common table expression by using the WITH statement. In the WITH statement, you specify a table that does not exist. For example, you run the following query.
WITH computed_table (id) AS
(SELECT id
FROM this_table_does_not_exist)
(SELECT id
FROM dbo.computed_table)
GO
When you run this query, you do not receive an error for the missing table.
Cause
These problems occur because of a malfunctioning processing of a SELECT statement that follows a WITH statement. The WITH statement uses the following syntax.
WITH common_table_expression AS
(CTE_query_definition)
This statement then creates a result set that is assigned to the common table expression name that was specified. Then, you follow this statement with a SELECT statement. If this second statement is enclosed in parentheses, and then you try to perform an operation on the results or you try to run a sys.dm_exec_query_stats dynamic management view, you receive incorrect results or an error.
Resolution
Service pack information
To resolve this problem, obtain the latest service pack for SQL Server 2008. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
968382 How to obtain the latest service pack for SQL Server 2008
Workaround
To work around these problems, you have to remove the parentheses from the statement that follows the common table expression. For example, consider the following WITH statement that uses such parentheses.
WITH common_table_expression AS
(SELECT *
FROM sys.objects)
(SELECT *
FROM common_table_expression)
To work around these problems, change to the following WITH statement that does not use these parentheses.
WITH common_table_expression AS
(SELECT *
FROM sys.objects)
SELECT *
FROM common_table_expressio
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem was first corrected in SQL Server 2008 Service Pack 2 (SP2).
More Information
For more information about how to use the WITH statement with a common table expression, visit the following MSDN webpage:
WITH common_table_expression (Transact-SQL)For more information about sys.dm_exec_query_stats, visit the following MSDN webpage:
