Catastrophic Failure, MSDASQL, Linked Servers and MySQL

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?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez

Catastrophic Failure, MSDASQL, Linked Servers and MySQL

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?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez

Cast problems with CLR

hi,

i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException:
at UserDefinedFunctions.FillRow(Object obj, String& PID)
.

Below is my code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections;

public partial class UserDefinedFunctions
{
const string SELECT =
@."SELECT pointData.PID
FROM pointData
INNER JOIN pointDevices ON pointData.PID = pointDevices.PID
WHERE pointDevices.UUID = @.UUID AND
DATEADDED >= @.DATE_START AND
DATEADDED <= @.DATE_STOP
ORDER BY DATEADDED";

[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read, TableDefinition="PID nvarchar(50)")]
public static IEnumerable createJourney(SqlString UUID, DateTime DATE_START,DateTime DATE_STOP, SqlDouble JOURNEY_DELAY, SqlDouble JOURNEY_DISTANCE)
{
ArrayList RAW_PID_LIST = new ArrayList();

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();

SqlCommand command = new SqlCommand(SELECT,conn);

command.Parameters.AddWithValue("@.UUID",UUID);
command.Parameters.AddWithValue("@.DATE_START",DATE_START);
command.Parameters.AddWithValue("@.DATE_STOP",DATE_STOP);

SqlDataReader reader = command.ExecuteReader();

using (reader)
{
while (reader.Read())
{
RAW_PID_LIST.Add(reader[0]);
}
}
}

return RAW_PID_LIST;
}

private static void FillRow(Object obj, out string PID)
{
object[] row = (object[])obj;

PID = (string)row[0];
}
};

could someone give me a clue as to why i might be getting this error please.

thank you.

FillRow gets only one object at a time. So, you should try this one:

private static void FillRow(Object obj, out string PID)
{
PID = (string)obj;
}