0

I'm getting the following error when I try to query a table using a link server. The link server is created on SQL2008r2 instance to connect to an SQL2014 instance.

The OLE DB provider "SQLNCLI10" for linked server "server\SQL2014" does not contain the table ""db_name"."dbo"."table_name"". The table either does not exist or the current user does not have permissions on that table.

Here is the TSQL i'm using to create the link server

EXECUTE sp_addlinkedserver @server = N'server\SQL2014', @srvproduct=N'sql_server', @provider=N'SQLNCLI10'

EXECUTE sp_addlinkedsrvlogin @rmtsrvname='server\SQL2014', @useself='false',@rmtuser='sa', @rmtpassword='sa_password'

When I test the connection that succeeds

The linked server login is sa so permissions can't be an issue.

sa user is enabled and table exists i can connect to and query all the tables directly on the SQL2014 instance using the sa user.

Where am I going wrong?

8
  • have you verified that there is 'sa' enabled, the table exists?
    – George K
    Jun 22, 2017 at 13:47
  • You should avoid using any user that is part of the sysadmin fixed server role as your linked server user. This is going to open up all manner of security holes. I know it's not your question, but I bring it up since you're troubleshooting, I would suggest you create a new SQL Login on the destination server with permissions only as needed and use that account to establish the connection. Jun 22, 2017 at 13:49
  • This is on development. sa user is enabled and table exists i can connect to and query all the tables directly on the SQL2008r2 instance using the sa user
    – Mazhar
    Jun 22, 2017 at 13:50
  • Are you using the correct syntax [LINKED_SERVER].[DB_NAME].[SCHEMA].[TABLE]?
    – McNets
    Jun 22, 2017 at 14:01
  • yes I am @McNets
    – Mazhar
    Jun 22, 2017 at 15:44

2 Answers 2

1

Please try specifying the @datasrc parameter when you create a linked server, e.g.

EXECUTE sp_addlinkedserver 
@server = N'server\SQL2014', 
@srvproduct=N'sql_server', @provider=N'SQLNCLI10', 
@datasrc = N'server\SQL2014'

The confusion may lie with the @server parameter being a descriptive name of the linked server to use in your code. @datasrc is actually physical server name used by OLEDB to make the connection.

0

If you check the connection and use SA, It seems you don't use correct syntax (Linkserver.Database. Schema.TableName) or the part of syntax like table name don't exist or mismatch table name...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.