'MSDASQL' Error On Linked Servers

Dec 16, 2003

I've seen several posts on linking an AS/400 to a SQL 2000 server.

I have created the link, I can use DTS packages to get data to/from the AS/400 to the SQL Server. However, I cannot write any SQL statements against the AS/400. Here is a basic one that doesn't work...

Select * from openquery(jdedwardspy, 'Select * from mhscrp.f0006')

If you look quickly enough, you can see that it does return a row or so, but then that is replaced by this error...

[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]

We're running Client Access V5R2, SQL Server 2000 SP3 and MDAC 2.7.1.

Any suggestions?

View 4 Replies

Catastrophic Failure, MSDASQL, Linked Servers And MySQL

Jun 21, 2007

When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".

This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).

Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.

I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.

I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine

I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.

Would anyone have any insights about what might be going on here, and if there is any possible resolution?

View 3 Replies View Related

MSDASQL Linked Server Error

Jan 7, 2008

Hi all,

I have created a System DSN to a MYOB database file. I have SQL 2000 and 2005 Express editions installed on the same machine. I am logged in to PC as Local Admin. I am also logged into 2005 Express using 'sa' to avoid any security problems.

Creating a New Linked Server using SQL 2000 works fine using the System DSN I created, however, If I use exactly the same settings (Microsoft OLE DB Provider for ODBC) I get an error message.

"The OLE Provider "MSDASQL" for linked server "sname" reported an error. The provider did not give an information about the error."
"Cannot initilize the data source object of OLE DB Provider "MSDASQL" for Linked Server "name". (Microsoft SQL Server, Error: 7399)"

I have installed SL 2005 Express SP2.

As another test I created a data source using the same System DSN in Excel. It worked fine and queries the MYOB database file.

Any ideas to assist me??

Thanks.

Alan

View 5 Replies View Related

SQl 2005 Express - Linked Server - Access ADP - MSDASQL Error

Jan 8, 2008

Hi everyone,

I'v finally managed to get a New Linked Server setup on SQL 2005 Express SP2 using the 'Microsoft OLE DB Provider for ODBC Drivers' MSDASQL. I have a Database setup on the SQL 2005 Express Database Engine which contains a query that OPENQUERY's the linked server. The query executes OK.

The problems in when I link an Access 2003 SP2 .adp file to the SQL 2005 Database Engine database. All objects, including the query containing the OPENQUERY (to the linked server) appear. However, when I run the same query within Access I get the following error:

"The OLE DB Provider for Linked Server "name" reported an error."
"The Provider reported an unexpected catastropic failure"

So the query works running within SQL 2005 Management Studio, but the same query doesn't work running within an Access 2003 SP2 ADP. I've closed down SQL 2005 Management Studio, but the same error is displayed.

I didn't know if the problem lies in Access 2003 SP2 or SQL 2005 Express SP2.

Any takers?? (I haven't logged this Thread in an Access forum)

Any assistance is appreciated.

Alan