Bug #45149 Calling SP from MSSQL causes syntax error near '{?=call "my_proc;1(?,?)}'
Submitted: 28 May 2009 6:49 Modified: 17 Feb 2017 6:50
Reporter: Bogdan Degtyariov Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S4 (Feature request)
Version:3.51, 5.0 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: linked server, MSSQL, stored procedure

[28 May 2009 6:49] Bogdan Degtyariov
Description:
Microsoft SQL Server generates stored procedures calls that cannot be executed in MySQL. The resulting query looks as '{?=call "my_proc;1(?,?)}'.

How to repeat:
Create a linked MySQL server in MSSQL. Try to call a stored procedure using T-SQL syntax referring the linked server name as follows:

exec MYSQL_LINKED...my_proc 1,2

Suggested fix:
parse the query and transform {?=call "my_proc;1(?,?)} into call my_proc(?,?)
[17 Feb 2017 6:50] Bogdan Degtyariov
Posted by developer:
 
Attempted to re-verify the issue with the most recent version of Connector/ODBC 5.3.7 and MS SQL Server 2014.
It looks that the initial approach to the problem was not correct.
The correct T-SQL syntax for the stored procedures on the linked server would be as follows:

 1. If a procedure returns result-set:

    SELECT * FROM OPENQUERY("MYSQL_LINKED_SERVER", 'CALL my_proc(1, ''abc'')');

 2. If a procedure does not return result-set (it can be used with procedures returning
    result-sets as well, but 1 cannot be used without the result set):

    exec('call my_insert_proc(13)') at MYSQL_LINKED_SERVER;

It worked correctly both ways.