StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIssue calling a MySQL stored procedure (with params) via a linked server (SQL Server 2005) using OPENQUERY syntax
text
Body
copied!<p>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.</p> <p>The MySQL stored procedure returns a result set, and when I use the 'EXEC ... AT ...' syntax the call works fine, e.g...</p> <pre><code>EXEC('CALL my_stored_proc(''2009-10-07'',''2009-10-07'');') AT MySQLSERVER; </code></pre> <p>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...</p> <pre><code>SELECT * FROM OPENQUERY(MySQLSERVER,'CALL my_stored_proc(''2009-10-07'',''2009-10-07'');') </code></pre> <p>...But this fails, and returns...</p> <pre><code> 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. </code></pre> <p>Which is strange, given that the 'EXEC ... AT ...' call didn't complain about permissions. The following calls all work fine...</p> <pre><code>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; </code></pre> <p>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.</p> <p>Any help would be greatly appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload