Variables to refer to server

Question

Variables to refer to server

prash.dark

SSC Enthusiast

Points: 167
More actions
May 27, 2009 at 10:21 am

#384664

Hi,
I have created a linked server to access a remote server.i.e. the command
select * from myserver.mydatabase.dbo.mytable
will give all rows from mytable from mydatabase located on server myserver. Now the problem is
- i want to use a variable name for server myserver so that even if my linked server changes from myserver to myserver123 i do not have to modify the query i have written.
Any help is appreciated. Thanks in advance.
Reards,
Prash..

Viewing 15 posts - 1 through 15 (of 20 total)

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

I would recommend using synonyms for this. You have to define each object that you are going to reference, but it is very easy to create and modify later if needed. For example:

CREATE SYNONYM MyDatabase.Object FOR remoteserver.MyDatabase.dbo.Object;

GO

Reference the object using something like:

SELECT ... FROM MyDatabase.Object;

If you change the linked server to MyLinkedServer, then all you have to do is drop the synonym and recreate it. For example:

DROP SYNONYM MyDatabase.Object;

GO

CREATE SYNONYM MyDatabase.Object FOR MyLinkedServer.MyDatabase.dbo.Object;

GO

And your code does not need to be changed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 2

I'd use Jeffrey's suggestion. You can't use a variable in the 3 or 4 part naming scheme.

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 3

U can use variable name, but the thing is u will have to use execute statement to run that SQL.

DECLARE @ServerNamevarchar(20)

DECLARE @Sqlvarchar(300)

SET @Servername = 'YourServerName'

SET @Sql = 'select * from '+@servername+'.'+'DatabaseName'+'.'+'dbo'+'.'+'TableName;'

print @sql

execute (@sql)

prash.dark SSC Enthusiast Points: 167 More actions · Answer 4

Thansk a lot everyone for reply. i will surely cosider Synonyms. Is there any other option?

Thanks..

Regards,

Prashant Chavan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

prash.dark (5/27/2009)
Thansk a lot everyone for reply. i will surely cosider Synonyms. Is there any other option?
Thanks..
Regards,
Prashant Chavan

Besides using Dynamic SQL as was also suggested, not really. I think I'd go with the synonym solution myself.

prash.dark SSC Enthusiast Points: 167 More actions · Answer 6

I have to use hudreds of select queries. So i can not create Synonyms for every table.

I tried creating a synonym for database object

i.e.

Create synonym MySourceDatabase For MyServer.MyDatabase

It created a Synonym but following statement did not work

Select * from MySourceDatabase.dbo.MyTable

Please advise..

Regards,

Prashant Chavan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

prash.dark (5/27/2009)
I have to use hudreds of select queries. So i can not create Synonyms for every table.
I tried creating a synonym for database object
i.e.
Create synonym MySourceDatabase For MyServer.MyDatabase
It created a Synonym but following statement did not work
Select * from MySourceDatabase.dbo.MyTable
Please advise..
Regards,
Prashant Chavan

Would help if you provide the complete error message. Without that how are we supposed to know what is wrong?

prash.dark SSC Enthusiast Points: 167 More actions · Answer 8

Error msg is

Msg 208, Level 16, State 1, Line 1

Invalid object name 'MySourceDatabase.dbo.MyTable.

When i used this commands

Create synonym MySourceDatabase For MyServer.MyDatabase

select * from MySourceDatabase .dbo.MyTable

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Per Books Online (BOL), which should be your best friend next to SSC, synonyms can only be created for views, tables, functions (CLR and T-SQL), and stored procedures. Can't create a synonym for a server, database, or schema.

prash.dark SSC Enthusiast Points: 167 More actions · Answer 10

I read the same. So is there any solution to my problem?

Thanks and Regards,

- Prash..

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 11

Or for a query.

You can create view for the query and encapsulate things. However, if you are planning on hundreds of tables for a linked server, you are architecturally thinking about this wrong. Set up stored procedures for your queries on the remote side.

prash.dark SSC Enthusiast Points: 167 More actions · Answer 12

Here is some more description..

I have a job that run overnight. It fetches the data from remote server to , say my server. I can't backup whole database since i dont need all data. I need to fetch around 60% of data, which includes lot of select queries.

Please advise..

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 13

How does the job work? Why not just use Integration services to move the data.

prash.dark SSC Enthusiast Points: 167 More actions · Answer 14

I am new to SSIS. Can you pass me a good link to create SSIS packages to move the data?

Thanks in advance.