Linked server Cannot initialize the data source object of OLE DB provider

Question

I am trying to create linked server to a application which uses pervasive database i setup everything right but i kept getting Cannot initialize the data source object of OLE DB provider "MSDASQL" i tried both 32 and 64 ODBC Data source Administration. Here is SQL Server version infomation

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) 
    Jul  9 2008 14:17:44 
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

i tried a lot trying to fix this but the test fails. ODBC connection using Pervasive ODBC Interface tested and works great.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "serverName". OLE DB provider "MSDASQL" for linked server "serverName" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (.Net SqlClient Data Provider)

Have you tried setting up a DSN and then setting up your linked server based on that? I don't have experience with this particular service but in the past I have needed to setup DSNs for certain kinds of data sources. — mroach
@mroach yes that's what i did, i been working on this almost for a week, what thing do i have to check? — Joe

Time Attendance System Time Attendance System · Accepted Answer · 2018-03-13T05:10:25

if you try to create the linked server using studio and the various menus, a small detail is not saved in the connection string, which is the database! (see last bit of the connection string)

Try this:It works for me

EXEC master.dbo.sp_addlinkedserver @server='MYSQL',
   @srvproduct='MySQL',
       @provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
       Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb';

Linked server Cannot initialize the data source object of OLE DB provider

1 Answers