Storing MySQL Stored Procedure resultset in a SQL Server temp table

Question

Storing MySQL Stored Procedure resultset in a SQL Server temp table

ttsang

Old Hand

Points: 361
More actions
October 1, 2009 at 10:50 am

#136322

Hi All,
I have a MySQL 5.0 db server connected via a linked server (using MySQL ODBC DSN) and I can run a MySQL stored procedure and display the results using:
exec ('call hotkey_transfers_1_0(''2009-10-01'',''2009-10-01'');') at MySQL_Linked_Server
However, I am unable to hold the result of this SP call in a temporary table.
So, something like...
create table #test(...parameter...)
insert into #test(...parameter...)
exec ('call hotkey_transfers_1_0(''2009-10-01'',''2009-10-01'');') at MySQL_Linked_Server
returns the following error message:
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "MySQL_Linked_Server" does not support the required transaction interface.
I have tried messing around with the openquery syntax, Provider options and ODBS DSN settings, but no luck.
A search on google yeilded nothing much.
Has anyone successfully pulled data off a MySQL server using a MySQL Server SP and stored the resultant data in a SQL Server temp table or similar?
Thanks
Trevor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

ttsang (10/1/2009)
Hi All,
I have a MySQL 5.0 db server connected via a linked server (using MySQL ODBC DSN) and I can run a MySQL stored procedure and display the results using:
exec ('call hotkey_transfers_1_0(''2009-10-01'',''2009-10-01'');') at MySQL_Linked_Server
However, I am unable to hold the result of this SP call in a temporary table.
So, something like...
create table #test(...parameter...)
insert into #test(...parameter...)
exec ('call hotkey_transfers_1_0(''2009-10-01'',''2009-10-01'');') at MySQL_Linked_Server
returns the following error message:
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "MySQL_Linked_Server" does not support the required transaction interface.
I have tried messing around with the openquery syntax, Provider options and ODBS DSN settings, but no luck.
A search on google yeilded nothing much.
Has anyone successfully pulled data off a MySQL server using a MySQL Server SP and stored the resultant data in a SQL Server temp table or similar?
Thanks
Trevor

Try this:

CREATE TABLE #temp(...parameters...)

INSERT INTO #temp

SELECT * FROM OPENQUERY(MySQL_Linked_Server, 'exec DATABASE.SCHEMA.hotkey_transfers_1_0 ''2009-10-01'',''2009-10-01''')

SELECT * FROM #temp

When trying to insert into a temp table from a remote stored proc, something about distributed transactions prevents it from working properly.

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Watch for schema issues as well, I seem to remember having issues with that the last time I worked with MySQL.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ttsang Old Hand Points: 361 More actions · Answer 3

Hi There,

Thanks for the response. Can you clarify what you mean by DATABASE.SCHEMA from

SELECT * FROM OPENQUERY(MySQL_Linked_Server, 'exec DATABASE.SCHEMA.hotkey_transfers_1_0

In the MySQL world, AFAIK DATABASE and SCHEMA are equivalent, so if your db name is "mike", then the line should be exec mike.mike.hotkey_transfers_1_0.

Is this correct or have I completely misunderstood?

I have tried a number of variation on this theme, to no avail.

Any other suggestions/pointers are very much appreciated.

It is a frustration that you can display the resultset in the SSMS query editor window, but not be able to use it!

BTW I have also tried:

BEGIN DISTRIBUTED TRANSACTION

SELECT * FROM OPENQUERY(LEADGEN, 'exec hotkey_transfers_1_0 ''2009-10-01'',''2009-10-01''')

COMMIT

...which didn't work. The error message was:

The OLE DB provider "MSDASQL" for linked server "LEADGEN" indicates that either the object has no columns or the current user does not have permissions on that object.

Any ideas?

Thanks

Trevor