March 30, 2010 at 11:51 am
When testing. I get an error that I cannot access the production database, even though the case statement should only be accessing development. I guess it checks permissions even though I'm not actually doing a select against PROD ?
I want fld4 to be set to different values depending on which database I'm in.
select fld1, fld2, fld3,
CASE WHEN DB_NAME() not in
(select database_name from OurDatabases where active_ind = 'Y')
THEN Development.dbo.Function(@var)
ELSE PROD.dbo.Function(@var)
END as fld4
from MyTable
March 30, 2010 at 4:05 pm
homebrew01 (3/30/2010)
When testing. I get an error that I cannot access the production database, even though the case statement should only be accessing development. I guess it checks permissions even though I'm not actually doing a select against PROD ?I want fld4 to be set to different values depending on which database I'm in.
select fld1, fld2, fld3,
CASE WHEN DB_NAME() not in
(select database_name from OurDatabases where active_ind = 'Y')
THEN Development.dbo.Function(@var)
ELSE PROD.dbo.Function(@var)
END as fld4
from MyTable
Yes indeed, when the code is checked, it parses the entire query first for errors and to make sure all the objects exist and are accessible before running the query itself.
April 1, 2010 at 8:12 am
Use a synonym, view, or dynamic SQL?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 1:49 pm
And then it typically defaults to the first database when trying to do something like this.
As Paul suggested, dynamic SQL, SSIS package, nested stored procedures, synonyms or views could resolve the issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 11:12 pm
Using a synonym would be my default choice here, since they were pretty much invented just for this type of scenario.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 12:47 pm
Thanks for the feedback. Would a synonym work with a function that is in another database as I'm trying to do in my first post ?
April 2, 2010 at 12:52 pm
homebrew01 (4/2/2010)
Thanks for the feedback. Would a synonym work with a function that is in another database as I'm trying to do in my first post ?
Sure would - give it a go!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply