Creating a Linked server to a server that has a hyphen in the name

Question

Creating a Linked server to a server that has a hyphen in the name

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
August 26, 2011 at 12:07 pm

#247085

I am needing to create a linked server to a server that has a hyphen in the name and I cant for some reason. This linked server is also a named instance and not on the standard SQL port. Is this even possible to do? I have tried different ways. I know that when querying a server with a hyphen in the name you have to surround it with square brackets etc...
Any and all help will be greatly appreciated.
Thanks

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

best solution... start over with a server that doesnt include a hyphen in the name...

a possible workaround... use the host file to create an alternative name for the server... not sure if this will work, and I dont have a server with a hyphenated name with which to test, but since you asked for any and all help, I thought I'd throw this out there...

so, on the server from which you are trying to connect, specify in the host file a non-hyphenated name which points to the ip addy of the hyphenated server. Then in your liked server specify nonhyphenatedname\instance. Ensure that the browser service is running on the host running the named instance... I think that should work.

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think you can specify the port by adding comma portname to the server? my examples are assuming you use port 14433 as the port.

for avoiding the dash and the issue it causes, i can think of a couple of ways: first use an aliased linked server instead of the real server name ; then you can do MyLinkedServer.database.schema.tablename a little easier.

you could also try switching to IP address as well;

EXEC master.dbo.sp_addlinkedserver

@server = N'MyLinkedServer',

@srvproduct = N'',

@datasrc = N'DBSQL-2K5\SQLEXPRESS,14433',

@provider = N'SQLOLEDB';

EXEC master.dbo.sp_addlinkedserver

@server = N'MyLinkedServer',

@srvproduct = N'',

@datasrc = N'192.168.1.55,14433',

@provider = N'SQLOLEDB';

-- Add Default Login (if applicable)

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'False',

@locallogin = NULL,

@rmtuser = N'sa',

@rmtpassword = 'NotARealPassword';

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!

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

NJ-DBA (8/26/2011)
best solution... start over with a server that doesnt include a hyphen in the name...
a possible workaround... use the host file to create an alternative name for the server... not sure if this will work, and I dont have a server with a hyphenated name with which to test, but since you asked for any and all help, I thought I'd throw this out there...
so, on the server from which you are trying to connect, specify in the host file a non-hyphenated name which points to the ip addy of the hyphenated server. Then in your liked server specify nonhyphenatedname\instance. Ensure that the browser service is running on the host running the named instance... I think that should work.

Even better- create an alias....

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

Should work just fine with the hyphen (especially if you use the scripts provided by Lowell and others).

You can also create an ODBC connection on the OS-level (giving it a name/alias without the hypen)...then create a linked server using the System DSN you created.

Either will work.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience