Sql-server – Link Server Error – From SQL2008r2 to SQL2014

linked-serversql serversql server 2014sql-server-2008-r2

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?

Best Answer

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.

Related Question