Linked servers, how to query remote server instance with \ in it's name

Transact-SQL
1

Hello, I have two linked sql servers, of which the remote server has a \ in it's instance name,

I've tried brackets, no brackets, escape characters, however I can't seem to get T-sql to recognize the remote server name. Can someone provide some advice?

My T-SQL

Update WIP_DB.dbo.WIP_Table_Miss
SET TRM1 = (select count(*) from [DDSNPBK2\CUSQl2008].[BWR].[dbo].[history] where station like ('MITLB1%') and Convert(Char(10),h_time,126) like @Today )
Where WIP_DB.dbo.WIP_Table_Miss.id=1

2

If it's a named instance, that syntax should be correct.

If it's not a named instance, use a different linked server name, such as dropping the \ out of the name when you create the linked server. The linked server name does not have to match the physical server name.

3

Thanks for the response Scott, I believe it's a named instance since in SSMS I can connect to the SQL Server using the name with the slash in it. I'm no dba, could you elaborate a little more?

Thanks,

Rick

4

If it is a named instance, then if SQL can't connect to it, either the linked server is set up incorrectly or the user that the code is running under doesn't have permissions to use that linked server and/or to access that object on the linked server.

5

What is the error message you are getting? Your syntax appears correct, so perhaps that may not be the issue.

Connect to the local server using the login you are using to run the query. Then, in object explorer, under Server Objects -> Linked Servers, find the [DDSNPBK2\CUSQl2008]. Are you able to expand that node and see the Catalogs? If not, what is the error message you are getting?

Connect to SSMS as a login that has sysadmin server role, right click the server name under Server Objects -> Linked Servers, select properties, security tab. Examine that to see if the login you are using would have access would translate to a login on the remote server that has access to the server, database, and table.

You can see the details of your linked server by querying sys.servers.

6

Name and DataSource are the same [DDSNPBK2\CUSQl2008]. is_linked = 1, Under catalogs I see the database I am after and the tables.

7

I discovered the problem, in the table returned by sys.servers, one of the characters in the server name was lower case. This is the first time I've run across this in T-SQL

8

It is less common, in my experience, but it is possible to set up SQL, or just a specific database, or even just one column in a specific table, to be Case Sensitive. Maybe your remote server has COLLATION set to something Case Sensitive by default?