Troubleshooting problems with linked servers in SQL Server 2014
If you have problems with linked servers in SQL Server 2014, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 linked servers bugs were fixed in SQL Server service
packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. Check that you have appropriate permissions to create linked servers.
To create a linked server, you must have ALTER ANY LINKED SERVER permission.
So, if you have no this permission, you cannot create linked servers using the
sp_addlinkedserver system stored procedure or GUI interface.
3. If you have problem with running a distributed query, run this query directly
on a linked server.
If the distributed query works when you run it directly on a linked server, check
the network connection between servers, the linked servers options and provider
options.
4. Check that the servers can communicate with one another by name, not just
by IP address.
You should verify that network name resolution works before you run the distributed
queries.
5. The error 7302 may occur when you execute linked server queries.
This is the error message text: “Cannot create an instance of OLE DB provider
“provider name” for linked server “linked server name”.” This problem occurs when
you execute linked server queries without the required providers installed and the
OLEDB provider is not yet installed or the OLEDB provider is not registered.
To resolve this problem, you can install the OLEDB provider using the software from
the corresponding vendor.
6. Executing a DMX query that uses a linked server to query data from a SQL Server
Analysis Services server may cause the “Logon failure” error message.
This problem occurs in SQL Server 2014 when the user is not a member of the local
Administrators group. To work around this problem, include the user account into
the local Administrators group.
7. The error 7403 may occur when you execute linked server queries.
This is the error message text: “The OLE DB provider “provider name” has not been
registered.” This problem occurs when you execute linked server queries without the
required providers installed and the OLEDB provider is not yet installed or the
OLEDB provider is not registered. To resolve this problem, you can install the
OLEDB provider using the software from the corresponding vendor.
8. You may receive the “Transaction context in use by another session” error message
when you insert into the local table the execution of the stored procedure that
references a linked server.
This problem occurs in SQL Server 2014 when a linked server is linked to the local
server itself. This configuration is known as a loopback linked server. To work around
this problem, avoid using loopback linked servers or rewrite the INSERT statement.
9. The linked server returns no results when you use a linked server that was
created by the SQL Native Client provider.
This problem occurs when you use the SQL Native Client provider with “Server type”
as “Other data source”. To work around this problem, you can delete the failing
linked server and create a new linked server by using the SQL Server Native
Client 10.0 (SQLNCLI10) provider.
10. When you run query to select data from Oracle linked server by using merge
join, the query executes successfully, but some rows are missing in the result.
This problem occurs when there is a NUMBER type column that does not have a defined
precision in the Oracle linked server. This bug was first fixed in Cumulative Update
package 4 for SQL Server 2014. You can download the Cumulative Update package 4 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197
11. When you execute a query to select data from an Oracle-linked server by using
OLE DB provider in SQL Server 2014, the values in the NUMBER type column may be
truncated in the query result.
This problem occurs because the Oracle NUMBER type with non-declared precision/scale
may not have a full mapping to a SQL Server data type. This bug was first fixed in
Cumulative Update package 8 for SQL Server 2014. You can download the Cumulative
Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
To work around this problem, you can convert the Oracle field from NUMBER type to
a data type that is fully supported by SQL Server first.
12. Error occurs when you try to create a linked server together with remote logins.
This is the error message text: “An error occurred during decryption”. This bug was
first fixed in Cumulative Update package 10 for SQL Server 2014. You can download
the Cumulative Update package 10 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3094220