Copy Linked Server to Another SQL Server

Question

Hi All,

We are currently migrating SQL server 2012 to 2019. we have migrated (copy) linked server using DBA tools.
However, when we test the connection linked server on the new server (2019) an error message appears as follows:

Has anyone experienced the same thing?

Really appreciate for your answer.

Answer 1

Hi @Arief Hardiansyah ,
Try downloading the latest version of OLE DB and restart the SQL. See if the error can be solved.And you can download from this link:
https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

@Arief Hardiansyah

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.

If you are using SQL Server Enterprise Manager, open Server Objects -- Linked Servers -- Providers, right click on the "MSOLEDBSQL" provider, select properties and check the "Allow inprocess" option. Recreate your linked server and test again.

You can also execute the following query if you don't have access to SQL Server Management Studio :

EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL', N'AllowInProcess', 1

If needed, restart the MSSQL Service in order for the "allow inprocess" option to take effect.