Issue calling MySQL stored procedure (with parameters) through linked server (SQL Server 2005) using OPENQUERY syntax
I am having problems trying to call a MySQL stored procedure (5.0.77) with parameters through 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, for example ...
EXEC('CALL my_stored_proc(''2009-10-07'',''2009-10-07'');') AT MySQLSERVER;
The limitation on the use of "EXEC ... AT ..." means that I cannot insert the result set into a temporary table in SQL Server, which is what I ultimately want to do. This made me try the OPENQUERY syntax ...
SELECT * FROM OPENQUERY(MySQLSERVER,'CALL my_stored_proc(''2009-10-07'',''2009-10-07'');')
... But it 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 odd considering that the "EXEC ... AT ..." call didn't complain about permissions. The following calls are working 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 call a MySQL stored procedure through a linked server and store the result set in a temporary table in SQL Server? Either by using the "EXEC ... AT ..." syntax, or by eliminating the object / permissions error when using the OPENQUERY syntax.
Any help would be greatly appreciated!
source to share
You need to enable " Special Distributed Queries " on SQL Server. For security reasons this is disabled by default. In most cases, "not allowed" errors refer to this.
Run this on SQL Server and then try the code again:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
Hope this helps.
source to share
I tried to fix the "Ad-hoc Distributed Queries" setting but still got the same error:
- "The OLE DB Provider" MSDASQL "for the linked server" MyMySQL "indicates that either the object has no columns, or the current user does not have permission on the object."
I am trying to execute a simple remote SELECT query against a linked MySQL database. In my case, the request I was trying looked like this. Pay attention to the number of lines used:
SELECT * FROM OPENQUERY
([MyMySQL],
'
SELECT
ID as ISSUE_ID, etc..
The fix was to remove the newline after the first single quote. Simple, huh? But not obvious.
Working code shown below:
SELECT * FROM OPENQUERY
([MyMySQL],
'SELECT
ID as ISSUE_ID, etc..
Hope this helps! Rob
source to share
I realize this is a pretty old post, but my problem was similar to Rob in that SQL inside OPENQUERY does not use line breaks as spaces in the same way that SQL Server does.
i.e. By laying out my request like this:
SELECT
*
FROM
OPENQUERY(LINKEDSERVER, '
SELECT
SomeField
FROM
SomeTable
')
I needed to have spaces as if they were written on one line (using _ as the visible space for demonstration):
SELECT
*
FROM
OPENQUERY(LINKEDSERVER, '
SELECT_
SomeField_
FROM_
SomeTable
')
source to share