CASE level in linked server limitation

Connect to a sql server 2000 and run the following query, it will give right results with no errors.
select ProductID,ProductName,SupplierID,
CASE WHEN ProductID=1 THEN ‘P1’
 WHEN ProductID=2 THEN ‘P2’
 WHEN ProductID=3 THEN ‘P3’
 WHEN ProductID=4 THEN ‘P4’
 WHEN ProductID=5 THEN ‘P5’
 WHEN ProductID=6 THEN ‘P1’
 WHEN ProductID=7 THEN ‘P7’
 WHEN ProductID=8 THEN ‘P8’
 WHEN ProductID=9 THEN ‘P9’
 WHEN ProductID=10 THEN ‘P10’
 WHEN ProductID=11 THEN ‘P11’
 ELSE ‘PN’
END AS P
FROM Northwind.dbo.Products
 
 
On another SQL 2000 box, define a linked server (Test) that links to another SQL 2000, run the following query:
select ProductID,ProductName,SupplierID,
CASE WHEN ProductID=1 THEN ‘P1’
 WHEN ProductID=2 THEN ‘P2’
 WHEN ProductID=3 THEN ‘P3’
 WHEN ProductID=4 THEN ‘P4’
 WHEN ProductID=5 THEN ‘P5’
 WHEN ProductID=6 THEN ‘P1’
 WHEN ProductID=7 THEN ‘P7’
 WHEN ProductID=8 THEN ‘P8’
 WHEN ProductID=9 THEN ‘P9’
 WHEN ProductID=10 THEN ‘P10’
 –WHEN ProductID=11 THEN ‘P11’
 ELSE ‘PN’
END AS PN
FROM Test.Northwind.dbo.Products
 
It gives right result with no errors. However if remove the comment, it will give the following error:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
 
Case expressions can NOT be nested more than 10 levels if the query is against a linked server.
 
The linked server can be defined by select SQL server or SQL OLEDB provider, the same results.
 
Test on the following scenarios:
Link from 2000 to 2005, 10 level limit
Link from 2005 to 2000, 10 level limit.
Link from 2005 express to 2005, 10 level limit.
 
 
From 2000 to 2005, tested AdventureWorks database on table Production.Product, the query is as following and the error is the same as above mentioned.
select ProductID,Name,
CASE WHEN ProductID=1 THEN ‘P1’
 WHEN ProductID=2 THEN ‘P2’
 WHEN ProductID=3 THEN ‘P3’
 WHEN ProductID=4 THEN ‘P4’
 WHEN ProductID=5 THEN ‘P5’
 WHEN ProductID=6 THEN ‘P1’
 WHEN ProductID=7 THEN ‘P7’
 WHEN ProductID=8 THEN ‘P8’
 WHEN ProductID=9 THEN ‘P9’
 WHEN ProductID=10 THEN ‘P10’
 WHEN ProductID=11 THEN ‘P11’
 ELSE ‘PN’
END AS PN
FROM Test.AdventureWorks.Production.Product
 
To avoid this error, we can create a procedure or view to wrap all the code, and call the SP/view in th linked server.
 
In the linked server:
CREATE PROCEDURE dbo.usp_TestCases
AS
select ProductID,ProductName,SupplierID,
CASE WHEN ProductID=1 THEN ‘P1’
 WHEN ProductID=2 THEN ‘P2’
 WHEN ProductID=3 THEN ‘P3’
 WHEN ProductID=4 THEN ‘P4’
 WHEN ProductID=5 THEN ‘P5’
 WHEN ProductID=6 THEN ‘P1’
 WHEN ProductID=7 THEN ‘P7’
 WHEN ProductID=8 THEN ‘P8’
 WHEN ProductID=9 THEN ‘P9’
 WHEN ProductID=10 THEN ‘P10’
 WHEN ProductID=11 THEN ‘P11’
 ELSE ‘PN’
END AS P
FROM dbo.Products
GO
In the linking server:
exec Test.Northwind.dbo.usp_TestCases
Or in the linked server create a view:
CREATE VIEW dbo.TestCases AS
select ProductID,ProductName,SupplierID,
CASE WHEN ProductID=1 THEN ‘P1’
 WHEN ProductID=2 THEN ‘P2’
 WHEN ProductID=3 THEN ‘P3’
 WHEN ProductID=4 THEN ‘P4’
 WHEN ProductID=5 THEN ‘P5’
 WHEN ProductID=6 THEN ‘P1’
 WHEN ProductID=7 THEN ‘P7’
 WHEN ProductID=8 THEN ‘P8’
 WHEN ProductID=9 THEN ‘P9’
 WHEN ProductID=10 THEN ‘P10’
 WHEN ProductID=11 THEN ‘P11’
 ELSE ‘PN’
END AS P
FROM dbo.Products
 
 
in the linking server:
SELECT * FROM Test.Northwind.dbo.TestCases
 
Both of them will return the right result with no errors
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to CASE level in linked server limitation

  1. Navendu says:

    This was quite helpful in understanding why I was getting this error.
     
    Thanks,
    Navendu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s