How to shorten four-part naming convention?

[fredscuba]

I have several SQL Server linked servers set up and was wondering whether there is a way to create an alias to substitute for the four-part naming convention?

So instead of having to write:

select *
from [server].database.tableowner.table

for a query on a table that is on the linked server, is there some way of specifying an alias or "shortcut" for this four-part naming convention?

something like:
select *
from a_db1..table

where a_db1 has been defined to be [server].database1.

I do not want to create views on the linked server, because it will only work for existing tables, not newly created ones, plus, I would have to create a view on the linked server for every table that exists on the other server...

 

[JayKusch]

I do not beleive you can pre-define the alias of a four part name until you use it in a statement. So you could, and I am sure you are already aware of this, do something like ...

SELECT *
from [server].database.tableowner.table AS t1
JOIN [server].database.tableowner.table as t2
ON (t1.SomeID = t2.SomeID)

So t1 and t2 are aliasing the 4 part references. Now you cannot use it in a follow up query like

Select *
From t1
Where t1.SomeID = xyz

and expect the query to pull the t1 alias from the previous query




Thanks

J. Kusch

 

[mrdenny]

SQL Server does not support global aliases.

The example that J gives you is the only aliasing that SQL supports.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[fredscuba]

That sucks!!!

So you are telling me that there is no way to alias a SQL Server instance. We have multiple databases on a physical server, so to distinguish between them, our SQL Server name includes the physical (Windows) server name *and* the SQL Server name.

So for instance we would have [WinServerName\SQL ServerName].databaseName.owner.tableName in the four-part name, which get *really* tedious to type.

I cannot believe that there is no way to shorten the [WinServerName\SQL ServerName] or even better the [WinServerName\SQL ServerName].databaseName section to a short string to use in queries.

Since we don't repeat a database name across multiple physical servers and SQL Server instances, it would have been great to just use the database name in a query from anywhere in the linked universe.

Has anybody else run into this problem before?

fredscuba

 

[mrdenny]

Nope, you can't shorten the server/instance.database name. You can setup the linked servers with aliases when you create them. That's the best you can do.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[fredscuba]

What do you mean with "You can setup the linked servers with aliases when you create them"?

What form would the alias take and would that shorten the server\instance.database string?

For example can I set up an alias between server1 and server2 and get an alias to shorten the [Server1\Instance1].database1.dbo.tableName string?


fredscuba

 

[masds]

If you use function openquery() instead of four part name, you can avoid the long qualified name, but the SQL gets a little lenthy,

select * from openquery(linkedServerName, '...')

The linkedServerName can be an alias rather than the real SQL Server name if you use oledb for SQL Server provider when you create the linked server. Is this what you need? thanks

 

[mrdenny]

If you alias the instance name when creating the linked server you could shorten [server1\Instance1].database1.dbo.tablename to Alias1.database1.dbo.tablename.

That's the most aliasing you can do with reguard to the linked server names.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[fredscuba]

OK, that would help a little, but how do I create an alias name when setting up the linked server? I used Enterprise Manager to add the linked servers.

fredscuba

 

[JamesLean]

In the linked server properties, specify the alias you want in the linked server name, choose "other data source" rather than "sql server" and specify "microsoft oledb provider for sql server". Then just put the actual server name in the "data source" field.

--James