PIVOT operator is not allowed in the recursive part of a recursive common table expression

Question

PIVOT operator is not allowed in the recursive part of a recursive common table expression

msimone

SSCommitted

Points: 1846
More actions
December 10, 2018 at 8:56 am

#376592

Hello, i have a little problem.
I create an in lined table function and with SQL Server 2008 work fine, with SQL Server 2016 or 2017 the next error:
"Msg 4190, Level 16, State 1
PIVOT operator is not allowed in the recursive part of a recursive common table expression"
I don't understand, is it posible?
Thanks for all.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

Yup. That functionality was broken in the upgrade from 2008 -> 2012. https://docs.microsoft.com/en-gb/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-2014#SQL14 There was a workaround - set the compatibility level of the database to 100 - but that is probably not an option for you now (in SQL 2016), and, if it is, then the code may well have been removed from the engine anyway.

Perhaps there's a better way of dealing with your problem? What are you trying to achieve?

Thomas Rushton
blog: https://thelonedba.wordpress.com

msimone SSCommitted Points: 1846 More actions · Answer 2

Hello Thomas, thanks, i didn't know that,
I will rewrite the function, i don't want but...

I try this (in 2008 I do...):
I have a table with the next columns:
DB -> Database name
date -> day captures data
proc -> procedure name
TTexec -> total execution time of the procedure in a day
TTcpu -> total CPU time of the procedure in a day
Texes -> number executions in a day

There are three databases names, DMS00109, DMS00176 and DMS00280
With a simple function in SQL 2008 i can to put in a single row the information of the three databases by day and order by total execution time by database and day with a row-number(), so i can to retrieve the procedure that was in first place in a day in the three databases in a single row, the procedure in second place in the three databases in a single row,, etc, etc, using a variable called @ntop, that it indicates if i want only the first place, the two first places, etc, etc.

I hope that you understand me, my english is very bad, i'm sorry.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

This might be as simple as this:
DECLARE @ntop AS int = 3;

WITH RAW_DATA AS (

SELECT DB AS DatabaseName, [proc] AS ProcedureName, [date] AS TheDate, MAX(TTExec) AS MaxExecutionTime FROM YourTable WHERE [date] = CONVERT(date, GETDATE()) AND DB IN ('DMS00109', 'DMS00176', 'DMS00280') GROUP BY DB, [proc], [date] ) SELECT TOP (@ntop) * FROM RAW_DATA ORDER BY MaxExecutionTime DESC;

You'll have to adapt it to your environment and actual table name, and correct any column names that don't actually match. I'm at a loss to understand why you would need a recursive CTE to get this information.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

msimone - Monday, December 10, 2018 8:56 AM
Hello, i have a little problem.
I create an in lined table function and with SQL Server 2008 work fine, with SQL Server 2016 or 2017 the next error:
"Msg 4190, Level 16, State 1
PIVOT operator is not allowed in the recursive part of a recursive common table expression"
I don't understand, is it posible?
Thanks for all.

I'd like to recommend that the PIVOT operator is usually a part of the "Presentation Layer". Calculate the data you need using the Recursive CTE and then Pivot the data outside the Recursive CTE. I think you find the code will run faster and require fewer resources, as well. If you plan a bit, you could use a CROSSTAB instead, which is typically about twice as fast as a PIVOT. Please see the following article for more on that...
http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

msimone SSCommitted Points: 1846 More actions · Answer 5

Hello sgmunson, hello Jeff Moden, thanks for yours answers.
I wrote the new function 4-5 days ago and i didn't use the CTE recursive and it is faster than the function with CTE.
With the table of the example, in the file is the image of the result set.
Thanks for all.