Giving servername as a common value in Query

Question

Post reply

Giving servername as a common value in Query

Manokarnan

SSC Eights!

Points: 852
More actions
September 5, 2007 at 1:51 am

#197688

Hai all,
I need a solution to retrive a data from one server from another server. I need to put a simple direct select statement for this(no stored procedures allowed).
ex: Select * From server1.DB1.dbo.Table1
I give a linked server connection to the respective servers. What i need is that in the above qry i need to provide a server name common. Like it may be in the form of variables.
ex:
Declare @server Varchar(25)
@server = ServerName1
Select * From @server.DB1.dbo.Table1
I know the above qry doesn't work but i need a equivalent on this.
Thanks,
Mano Karnan

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

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

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

You could use dynamic SQL like:

DECLARE @query NVARCHAR(4000)

SET @query = 'Select * From ' + @server + '.DB1.dbo.Table1 '

EXECUTE ( @query )

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Manokarnan SSC Eights! Points: 852 More actions · Answer 2

I want to use the mixed server query. That is

Select * From server1.DB1.dbo.Table1 a , server2.DB2.dbo.Table2 b

Where a. no = b.no

something like the above. The query may be too long to write. I think that the given solution may be very complicated for me to write. I have more number of queries to write and i go create a task in SSIS and make to run it. I need some other solution needed if possible. Just i want to know this is the only solution for this. Any idea????

Thanks,

Mano Karnan

Manokarnan SSC Eights! Points: 852 More actions · Answer 3

I want to use the mixed server query. That is

Select * From server1.DB1.dbo.Table1 a , server2.DB2.dbo.Table2 b

Where a. no = b.no

something like the above. The query may be too long to write. I think that the given solution may be very complicated for me to write. I have more number of queries to write and i go create a task in SSIS and make to run it. I need some other solution needed if possible. Just i want to know this is the only solution for this. Any idea????

Thanks,

Mano Karnan

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

You are correct... things are going to get a little complex because you need a query to build a dynamic query from Master.dbo.SysServers.

--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)
Intro to Tally Tables and Functions