When you execute TSQL queries that use linked servers definitions or adhoc queries that access remote data sources (via OPENROWSET or OPENDATASOURCE functions), you may encounter error messages similar to the following:Error message 1:
The OLE DB provider "provider name" has not been registered. (Microsoft SQL Server, Error: 7403)
Error message 2:
Cannot create an instance of OLE DB provider "provider name" for linked server "linked server name". (Microsoft SQL Server, Error: 7302)
Note: You may also encounter these messages after you migrate your SQL Server from one server to another or when you restore the master database from one server on another server.
Symptoms
These errors indicate that SQL Server is not able to initialize the OLEDB provider that is specified in the definition of the linked server. These messsages may occur if any of the following conditions is true for the system that is running SQL Server where the linked server is defined:
-
The OLEDB provider is not yet installed.
-
The installed OLEDB provider's bitness does not match the bitness of SQL Server. For example, you may have installed 32 bit version of the provider but SQL Server is running in 64 bit mode.
-
The OLEDB provider is not registered.
Note: When you create linked server using sp_addlinkedserver, SQL Server does not report any error messages even when one or more of the above conditions is true.
Cause
Review the list of linked servers defined on the SQL Server and find out the OLEDB provider associated with it. Then install the OLEDB provider using the software from the corresponding vendor. Ensure that you are also installing the provider that corresponds to the platform [x86 or x64] of SQL Server.To get a list of installed OLEDB providers, use the SQL Server Management Studio and navigate to the "Server Objects" node and expand the "Linked Server" node. You could also use the catalog view sys.servers to find the list of all defined linked servers and their associated OLEDB providers.
