passing server name as parameter

[habneh]

I wrote a query which can be run on different servers

I want to pass the server name and database name as a parameter to my query.

how can I do that

can I do

select * from @servername.@databasename.dbo.tbl

Thanks

 

[ptheriault]

you have to use dynamic SQL to build a string with your server and database names in it. then execute the string.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[habneh]

Thank you paul,

can you give me a sample code whcih describes this

Thanks,

 

[ptheriault]

Here you go.

declare @server_name  varchar(255),
        @database_name varchar(255),    
        @sql varchar(max)

SELECT @server_name = 'MydbServer',
       @database_name = 'myDb'

SELECT @sql = 'SELECT top 1 * FROM '+ @server_name +'.'+@database_name+'.'+'dbo.Mytable'

exec(@sql)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[habneh]

Thanks again
really appreciate that