Creating "alias" for linked server table 1

[snyderj]

I want to create an "alias" for tables on a linked server but have not been able to determine the proper syntax.

I thought that something such as what's below would work through Query Analyzer (while logged into server 1 with [VirtualServer\Instance] as a linked server), but the syntax below does not work.

DECLARE @ABC CHAR(70)
DECLARE @XYZ CHAR(70)

SET @ABC = '[VirtualServer\Instance].database.owner.BigLongTableName1'
SET @XYZ = '[VirtualServer\Instance].database.owner.BigLongTableName2'

SELECT @ABC.element1, @XYZ.element2
FROM @ABC LEFT JOIN @XYZ ON @ABC.element1 = @XYZ.element1

Any assistance is appreciated.


snyderj

 

[tlbroadbent]

You cannot use variables for object or column names. You can use dynamic SQL though that can cause problems.

DECLARE @ABC VARCHAR(70)
DECLARE @XYZ VARCHAR(70)
DECLARE @SQL VARCHAR(2000)

SET @ABC = '[VirtualServer\Instance].database.owner.BigLongTableName1'
SET @XYZ = '[VirtualServer\Instance].database.owner.BigLongTableName2'

SELECT @SQL=
'SELECT a.element1, b.element2 FROM ' +
@ABC + ' a LEFT JOIN ' + @XYZ +
' b ON a.element1 = b.element1'

EXEC(@SQL)

Why go to all the trouble? Just use the the aliases in the select list, ON clause, where clause, etc.

SELECT a.element1, b.element2
FROM [VirtualServer\Instance].database.owner.BigLongTableName1 a
LEFT JOIN
[VirtualServer\Instance].database.owner.BigLongTableName2 b
ON a.element1 = b.element1 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[snyderj]

Your "just use the aliases in the select list..." solution is where I started, but joining four or five tables makes the FROM more complex to read. I was searching for an alternate solution.

Thanks!