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

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

PublicSub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

EndSub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

PublicClass ScriptMain

PublicSub Main()

Dim dtAsNew System.Data.DataTable

Dim oleadAsNew Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim drAs DataRow

ForEach drIn dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

EndSub

EndClass

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class