Issue calling a MySQL stored procedure (with params) via a linked server (SQL Server 2005) using OPENQUERY syntax

Question

Hello,

I'm having issues when trying to call a MySQL (5.0.77) stored procedure with parameters, via a linked server (SQL Server 2005) using the OPENQUERY syntax.

The MySQL stored procedure returns a result set, and when I use the 'EXEC ... AT ...' syntax the call works fine, e.g...

EXEC('CALL my_stored_proc(''2009-10-07'',''2009-10-07'');') AT MySQLSERVER;

The limitation of using 'EXEC ... AT ...' means I can't insert the result set into a temporary table in SQL Server, which is ultimately what I want to do. Which led me to trying the OPENQUERY syntax...

SELECT * FROM OPENQUERY(MySQLSERVER,'CALL my_stored_proc(''2009-10-07'',''2009-10-07'');')

...But this fails, and returns...

 Msg 7357, Level 16, State 2, Line 1
 Cannot process the object "CALL my_stored_proc(''2009-10-07'',''2009-10-07'');". The OLE DB provider "MSDASQL" for linked server "MySQLSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

Which is strange, given that the 'EXEC ... AT ...' call didn't complain about permissions. The following calls all work fine...

EXEC('SHOW TABLES;') AT MySQLSERVER;

SELECT * FROM OPENQUERY(MySQLSERVER,'SHOW TABLES;');

CREATE TABLE #tmpTest (
    [table] varchar(255) null
);
INSERT INTO #tmpTest ([table])
SELECT * FROM OPENQUERY(MySQLSERVER,'SHOW TABLES;');
SELECT * FROM #tmpTest;
DROP TABLE #tmpTest;

So my question is, how can I make a call to a MySQL stored procedure, via a linked server, and store the result set in a temporary table in SQL Server? Either by using the 'EXEC ... AT ...' syntax, or by solving the object/permissions error when using the OPENQUERY syntax.

Any help would be greatly appreciated!

Answer 1

You need to enable "Ad Hoc Distributed Queries" on the SQL Server. This is not enabled by default, for security reasons. Most of the time, the "do not have permission" errors are related to this one.

Execute this on the SQL Server, and then try again your code:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

I hope it helps.