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, Commented Mar 19, 2017 at 2:00
@mroach yes that's what i did, i been working on this almost for a week, what thing do i have to check? — Joe
– Joe, Commented Mar 19, 2017 at 2:14

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

4

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';

answered Mar 13, 2018 at 5:10

Time Attendance System

3191 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Weles · Accepted Answer · 2022-01-19 17:04:40Z

First I Downloaded the last ODBC Driver from: ODBC Driver Link and Installed it.

After in SQL Management Studio I inserted the code below:

"I changed driver's description to: "MySQL: 8.0" and setting RPC to allow Remote Procedure Call if was important".

EXEC master.dbo.sp_addlinkedserver 
        @server = N'LKD_MYSQL', 
        @srvproduct=N'MySQL', 
        @provider=N'MSDASQL', 
        @provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};DATABASE=database;OPTION=3;PASSWORD=****;USER=user;SERVER=host'

GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LKD_MYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LKD_MYSQL', @locallogin = NULL , @useself = N'False'
GO

sample of select:

select * from openquery(LKD_MYSQL,'select * from database.table')

mahender singh · Accepted Answer · 2022-07-05 13:26:45Z

0

We also facing the same kind of issue and resolved by changing mysql login password as it's using ; in end of password, hope this will resolve your issue.

answered Jul 5, 2022 at 13:26

mahender singh

1

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related