CODEWITHSUNDEEP
sqlsql-server
JM.
JM.

Reputation: 688

SQL Server FQDN : Use of IP/Instance unrecognized

Can someone point me to the relevant BOL info for this (odd to me) behavior?

I had to join the prod & staging copies of the same table (same db name, diff servers), for a quick query. So I simply needed a fully-qualified join.

This one errors:

SELECT top 10 *
FROM [172.26.196.105\Staging].[DbName].[dbo].[TableName]

This one works:

SELECT top 10 *
FROM [USCASQL01\STAGING].[DbName].[dbo].[TableName]

Edit: clarification ... obviously not a join, these selects were simply me assembling the fully qualified name of my staging db/table. This is not a linked server, but the one I'm connected to.

These, of course, refer to the same instance. I used the IP address\InstanceName since that is what displays in my Object Explorer, it returned an error - "Could not find server 'IP address\InstanceName' in sys.servers." True enough, sys.servers stores the computerName\InstanceName, which works.

Why would one work and not the other? IOW, Why can't it resolve the IP/Instance name in TSQL when it resolves it just fine in the Object Explorer? BTW, we frequently are storing IP's in sys.servers, this one just happened not to be.

Also, I recall from SQL Server7 (way back in the day), a utility that would allow you to create friendly-name aliases for ip's. Can't find it now, does it still exist?

TIA

Upvotes: 2

Views: 3923

Answers (1)

Matt Whitfield
Matt Whitfield

Reputation: 6574

This is because the first part of a four part qualified name is a reference to a linked server by name, not by an IP or network name. So, if you don't have the linked server set up, then you get the error you encountered.

For adding linked servers, you want to be looking at sp_addlinkedserver.

This is the BOL Page that describes how the four part names work.

Upvotes: 2

Related Questions