Calling MySQL stored procedures viua a linked server

  • dJeePe

    Say Hey Kid

    Points: 712

    Does anyone know if it is possible to call MySQL stored procedures via a linked server setup? I've searched around the net and had a play myself but have had no luck so far.

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    When you ran a stored procedure on a remote heterogeneous data server, did you include full name of the stored procedure, such as [ServerName].[DBName].[Owneship].[StoredProcedure]?

  • dJeePe

    Say Hey Kid

    Points: 712

    EXEC MYSQLLOCALHOST.rhys.root.usp_test

    /*------------------------

    EXEC MYSQLLOCALHOST.rhys.root.usp_test

    ------------------------*/

    OLE DB provider "MSDASQL" for linked server "MYSQLLOCALHOST" returned message "[MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?=call "rhys"."root"."usp_test";1' at line 1".

    Msg 7212, Level 17, State 1, Line 1

    Could not execute procedure 'usp_test' on remote server 'MYSQLLOCALHOST'.

    I've tried the above and so many variations. Is there a way of passing a string (or sql) directly for the linked server to execute?

  • dJeePe

    Say Hey Kid

    Points: 712

    Cracked it!

    EXEC('CALL usp_test') AT MYSQLLOCALHOST

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    Interesting solution.

  • jie_yuan

    Valued Member

    Points: 71

    Is there a way to get the value ( i.e. @rtn_cd) of the OUT varialbe in mysql stored procedure in MSSQL like the following ?

    EXEC('CALL usp_test(@rtn_cd)' ) AT MYSQLLOCALHOST

    If mysql procedure is created like this:

    create procedure usp_test(OUT return_code int)

    begin

    ..

    end;

    Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply