Ran into a case this week when a business superuser could not change other users' passwords. I checked and the superuser had the securityadmin role. Basically after sleuthing around, I came to the conclusion that the issue is caused by incompatible service pack levels between the database server (which is at SQL Server 2005 SP3) and the business superuser who had recently had to reinstall management studio (but installed a non SP version of the client).
This is not the first time something strange happens when using a lower service pack client against a higher service pack server. I've ran into weird maintenance plan issues before with this scenario...
I just wish Microsoft can just warn users when connecting to a higher service pack SQL Server to help track down issues or to remind to update management studio clients.
Saturday, August 6, 2011
Wednesday, August 3, 2011
coalesce() function translated to case expression in SQL Server ?
Error message in SQL Server Management Studio:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
I just wanted to note this down before I forget... a couple of weeks ago I was deploying a view to a test environment and had to re-point the table source to a linked server and received this message.
the original view was something like this (simplified):
select *
from t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null
the above worked when t is on same server.
after pointing t to linked server:
select *
from.dbT.dbo.t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null
I got the error msg:
Case expressions may only be nested to level 10.
Apparently, SQL Server translates the coalesce function internally to nested case expressions and ran into some limitations via the linked server interface.
To work around the issue, I rewrote the coalesce function to OR expressions like:
select *
from.dbT.dbo.t
where
(a is not null
or b is not null
or c is not null
or d is not null
.
.
.
or l is not null)
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
I just wanted to note this down before I forget... a couple of weeks ago I was deploying a view to a test environment and had to re-point the table source to a linked server and received this message.
the original view was something like this (simplified):
select *
from t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null
the above worked when t is on same server.
after pointing t to linked server:
select *
from
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null
I got the error msg:
Case expressions may only be nested to level 10.
Apparently, SQL Server translates the coalesce function internally to nested case expressions and ran into some limitations via the linked server interface.
To work around the issue, I rewrote the coalesce function to OR expressions like:
select *
from
where
(a is not null
or b is not null
or c is not null
or d is not null
.
.
.
or l is not null)
Subscribe to:
Posts (Atom)