How do you get the ouput from an EXEC statement into a variable (calling procedure on mysql linked server)?

I am able to call a procedure on a linked server (MySQL) from Sql2k5. The procedure returns a single row, single column:

declare @Statement nvarchar(200)
set @Statement = 'call database.procedure(''some string'');'
exec (@Statement) at [linkedserver] 

... results:

some string

What I need now is to set a variable to the value returned from the procedure

I found a somewhat related post here.

but when I try to i开发者_开发技巧nsert the executed results:

insert into sometable exec (@Statement) at [linkedserver] 

I get this error:

OLE DB provider "MSDASQL" for linked server "linkedserver" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "linkedserver" was unable to begin a distributed transaction.

Is there a way to get around this, or a much better way to get at those results?

Thanks,

Linked servers are a real pain, especially when metadata gets screwed up. An “insert” is a distributed transaction no matter if you use BEGIN/COMMIT TRAN though (inserts cause log usage). Have you tried OPENQUERY yet? Or try these ideas:

http://www.sqlservercentral.com/Forums/Topic714869-338-1.aspx#bm716699

Note the provider and the linked server options in the following link: http://www.infi.nl/blog/view/id/4/How_To_MySQL_as_a_linked_server_in_MS_SQL_Server