SQL Instance Naming Conventions for Linked Servers

Question

SQL Instance Naming Conventions for Linked Servers

SQLUserC

SSCrazy

Points: 2345
More actions
January 8, 2012 at 9:23 pm

#391260

Hey all,
Recently, I have been considering implementing linked servers' at several clients. In my tests, I noticed that you can't remotely reference tables on linked servers whose instance names contain dashes (although it seems that you can add the instances as linked servers in the first place).
I was wondering what aspect of T-SQL is preventing the referencing of such instances. Several of our clients' instances are named using dashes. Are there any other characters to avoid in instance names so that aliases can be avoided? I'd appreciate any advice anyone can give.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

cjohn5552 (1/8/2012)
... I noticed that you can't remotely reference tables on linked servers whose instance names contain dashes ...

Try this. May work. Not tested.

Write the instance name inside the sqare bracket.

For example: SELECT * FROM [INSTANCE-NAME].[DB-NAME].[SCHEMA-NAME].[table-name]

jnuqui Default port Points: 1475 More actions · Answer 2

of course you may not. 😀

put [ ] in their names. same as reply above. 🙂 sample [North-wind].[Employee].[User-Names] something like that. 😛

===============================================================

"lets do amazing" our company motto..

SQLUserC SSCrazy Points: 2345 More actions · Answer 3

Adding brackets around the name actually doesn't work in my experience, although it changes the error message I get. Without brackets, it picks up the first parts of the server name as columns that it doesn't recognize.

E.g. if the server instance name is sql-serv1 and I try to run:

select * from sql-serv1.DBName.dbo.TableName

then it will say:

Invald column name 'sql'

And then it says:

The multi-part identifier "serv1.DBName.dbo.TableName" could not be bound.

If I add brackets around the server name, I just get:

The multi-part identifier "sql-serv1.DBName.dbo.TableName" could not be bound.

Does anyone have any other ideas? The issue is, for servers without dashes in the name, such a statement will run fine. Do I have to use an alias?