• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Linked server does not work on SQL server instance but work on another

Hi Experts

I have a linked server that works fine on the local (default) server istance, I have created a script from the linked server and run it on a different  server instance on the same box with the same credentials.

When I try and query the linked server on the different SQL server instance , I get the error message  that the table doesnt exsit or the current user does not have permissions on that table.

The table does exsit and I can use the same syntax on the default instance and it works as expected.

Has anybody else had this problem and do they know how to resolve it?
0
MrDavidThorn
Asked:
MrDavidThorn
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
Please keep in mind that an instance is addressed by ServerName\InstanceName where a SQL default (instance) is addressed just by ServerName.
You sould try and add the Linked server on the instance the same way or in SSMS instead and generate script from there to see the differences.
0
 
MrDavidThornAuthor Commented:
I used the script from the default instance, so they are exactly the same
0
 
MrDavidThornAuthor Commented:
It must be some sort of permissions issue, though I have also logged on as the sever admin account, could there be a port being blocked?
0
 
lcohanDatabase AnalystCommented:
"When I try and query the linked server on the different SQL server instance , I get the error message  that the table doesnt exsit or the current user does not have permissions on that table."


You MUST have/add a linked server on the " different SQL server instance" as well. You cant add a linked server on server A to server B then go on server C and try query server B(or A for that matter)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now