August 20, 2008 at 12:34 pm
We have 3 MS SQL servers: S1, S2 and S3.
S1 and S2 are our application servers and contain identical tables. S3 is our BI platform.
From S3 we want to run a single query - sometimes against S1 and other times against S2
I'd like to refer to the server name in the query using a variable but get an error: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '.'.
declare @svr varchar(10)
set @svr='S1'
SELECT PATIENTS.SEX, COUNT(REFERRALS.REFNO) AS Expr1
FROM @svr.pas.dbo.PATIENTS
INNER JOIN @svr.pas.dbo.REFERRALS ON patients.PATNO = referrals.PATNO
GROUP BY patients.SEX
Any ideas - please/thank you.
Sat
August 20, 2008 at 1:54 pm
Hi.
I don't know of any direct way to accomplish what you are trying without using dynamic SQL. You cannot use variables in place of table names, column names, etc in queries. Those all have to be literal strings.
You could recode the query to conditionally use one of two select statements each of which stored the results into a local Temp table. The rest of the query could then reference the temp table.
You could also try "Distributed Partitioned Views". That allows you to Union tables from linked servers into a single view. BOL has a good description to get you started.
Good luck.
August 21, 2008 at 6:02 am
I like the distributed partitioned views idea as long as there is something valid in the data to partition on.
The simple step is to use an if and query the appropriate server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply