Aliasing servername

Question

Aliasing servername

SkyBVI

SSCoach

Points: 15128
More actions
July 21, 2010 at 3:03 pm

#221508

is there a method to avoid writing the server name in a query..
like xyz.dbname.dbo.tablename1 A
xyz.dbname.dbo.tablename2 B
xyz.dbname.dbo.tablename3 C
so in this case xyx(servername) comes as many times as we want to alias the table
Can i avoid writing xyz?
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

sushantkumar1984 (7/21/2010)
is there a method to avoid writing the server name in a query..
like xyz.dbname.dbo.tablename1 A
xyz.dbname.dbo.tablename2 B
xyz.dbname.dbo.tablename3 C
so in this case xyx(servername) comes as many times as we want to alias the table
Can i avoid writing xyz?
Sushant

If you're logging into server xyz:

use dbname

go

dbo.tablename1 A

dbo.tablename2 B

dbo.tablename3 C

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SkyBVI SSCoach Points: 15128 More actions · Answer 2

no im not logging into xyz server..

im using it as a linked server as my query takes output from 2 servers..

is there a way?

Regards
Sushant Kumar
MCTS,MCP

Lowell SSC Guru Points: 323518 More actions · Answer 3

sushantkumar1984 (7/21/2010)
is there a method to avoid writing the server name in a query..
like xyz.dbname.dbo.tablename1 A
xyz.dbname.dbo.tablename2 B
xyz.dbname.dbo.tablename3 C
so in this case xyx(servername) comes as many times as we want to alias the table
Can i avoid writing xyz?
Sushant

the only thing close to what you are after is synonyms...but a synonym must be related to an object...TABLE/FUNCTION/VIEW/PROC, which the server is not.

so you can create a synonym for the specific tables on xyz.dbname.dbo.....

CREATE SYNONYM A FOR xyz.dbname.dbo.tablename1

CREATE SYNONYM B FOR xyz.dbname.dbo.tablename2

CREATE SYNONYM C FOR xyz.dbname.dbo.tablename3

SELECT * FROM A

INNER JOIN B ON A.ID = B.ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 4

@ lowell

thanks it worked...

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ lowell

So the synonyms A,B and C which i created will be local to database level or server level..?

and how to deallocate those synonyms once the use is over?

thanks

Sushant

Virgin Islands

Regards
Sushant Kumar
MCTS,MCP

Lowell SSC Guru Points: 323518 More actions · Answer 6

sushantkumar1984 (7/22/2010)
@ lowell
So the synonyms A,B and C which i created will be local to database level or server level..?
and how to deallocate those synonyms once the use is over?
thanks
Sushant
Virgin Islands

I know you can have synonyms that are wider in context that the current database, but i'd have to google it up; generally if you were in a specific database and ran that ADD SYNONYM script, then they are available only in that database...so if you went to the "SandBox" database and ran the drop code below, it would probably fail since it's not in that database.

since a synonym is an object you have to drop it:

drop synonym A

drop synonym B

drop synonym C

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ lowell

got it....

thanks a lot

Sushant

Virgin Islands

Regards
Sushant Kumar
MCTS,MCP