0

I would like to call tables from a different server. However, there is a problem since the server name appears in the following format: 

10.52.49.22/Name

Now if I do something like this, I get the error, "

The object name contains more than the maximum number of prefixes

SELECT t1.[UserId]
FROM [10.52.49.22].Name.[TargetDatabase].[dbo].[TableOne] t1
JOIN [10.52.49.22].Name.[TargetDatabase].[dbo].[TableTwo] t2
on t1.UserId = t2.UserId

How to use this server name correctly against the syntax specified in the following answer?

Community
  • 1
  • 1
usefulBee
  • 9,250
  • 10
  • 51
  • 89
  • Can you try something like [10.52.49.22\Name].[TargetDatabase].[dbo].[TableOne]? You can also create [Synonyms](https://msdn.microsoft.com/en-us/library/ms187552.aspx) to wrap details. – Shishir Oct 19 '16 at 18:32
  • @Shishir, I tried but I get the following error, "Could not find server '10.52.49.22/Name' in sys.servers." – usefulBee Oct 19 '16 at 18:56
  • 1
    Please create [linked server](https://msdn.microsoft.com/en-us/library/ff772782.aspx) then execute query for remote server. Please see this http://stackoverflow.com/questions/10402197/how-to-create-the-linked-server-for-sql-server-2008-where-we-have-the-database-f – Shishir Oct 19 '16 at 19:10

2 Answers2

0

You can't just toss the name of another server in the middle of a query like that. To reference an external server you need to use a link server. And the syntax you have there is not ever going to work. The longest you can have is 4 part naming. You have 5....not sure what Name is there.

https://msdn.microsoft.com/en-us/library/ms188279.aspx

Your query would be something like this.

SELECT t1.[UserId]
FROM [YourLinkServerName].[TargetDatabase].[dbo].[TableOne] t1
JOIN [YourLinkServerName].[TargetDatabase].[dbo].[TableTwo] t2
on t1.UserId = t2.UserId
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • In a connection string, Name would look like this while 53909 is a port number: Data Source=10.52.49.22,53909\Name; – usefulBee Oct 19 '16 at 19:05
  • Not sure what your connection string has to do with this. If you are already connecting to that server you don't need to specify the server again. – Sean Lange Oct 19 '16 at 19:28
  • I know this is strange. But any way, I found that a linked server is already created on the server I am trying to call from, and the LinkServerName is like this: Name\Name. So I just added it as it is and it worked: FROM [Name\Name].[TargetDatabase].[dbo].[TableOne] t1 – usefulBee Oct 19 '16 at 19:35
0

Name.[TargetDatabase] should be one.

[10.52.49.22].[TargetDatabase].[dbo].[TableOne]
Joe C
  • 3,925
  • 2
  • 11
  • 31
  • This returns an error, "Could not find server '10.52.49.22' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers." – usefulBee Oct 19 '16 at 18:58
  • You need to use the name as it appears in the linked servers list. From the other posts, it seems you know that now. Cheers – Joe C Oct 19 '16 at 19:39