Copy Linked Server to Another SQL Server

Arief Hardiansyah 51 Reputation points
2022-10-31T07:58:04.803+00:00

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:

255448-microsoftteams-image-3.png

Has anyone experienced the same thing?

Really appreciate for your answer.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,342 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
516 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. PandaPan-MSFT 1,911 Reputation points
    2022-10-31T08:42:09.597+00:00

    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"


1 additional answer

Sort by: Most helpful
  1. Chandu_0124 11 Reputation points
    2022-10-31T17:48:12.02+00:00

    @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.

    1 person found this answer helpful.