How to call MySQL Stored Procedure from SQL Server 2000?

Question

I have two databases, one MySQL 5, one SQL Server 2000. I've got the MySQL database mapped as a linked server in the MS SQL database. I'd like to call a stored procedure saved in the MySQL database from the MS SQL database. What's the correct syntax to do this? Is it even possible in SQL Server 2000?

Edit:

I've tried

EXEC webpush...clear_tables

but I just get this back:

Could not execute procedure 'clear_tables' on remote server 'webpush'.
[OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver]
[mysqld-5.0.46-enterprise-gpl-log]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 "clear_tables";1' at line 1]

Even when I fill in DB name and owner, I still get the same thing.

Answer 1

I haven't used linked servers in this way but can you not just call using the exec using the linked server.database.owner.procedure syntax?

Answer 2

I was looking to do something similar, so even though this is way out of date, I thought I'd post a reply for anyone else searching. I could not find a way to run stored procedures via the '...' notation, but this works fine

Here is asimple stored proc in MySQL to update the barcode field for a record in the test table of database xxx given the id :

DELIMITER $$

DROP PROCEDURE IF EXISTS xxx.STP_products_Update_barcode$$ CREATE DEFINER=root@localhost PROCEDURE xxx.STP_products_Update_barcode(IN xid INT,in xbarcode varchar(25)) BEGIN UPDATE test SET barcode = xbarcode
WHERE id = xid; END $$

DELIMITER ;

and this is a procedure in SQL Server to run it (I normally have TRY CATCH blocks and other error trapping, but have left that out for clarity.Note that MYSQL_XXX is a linked server that was set up from SQL Server to MySQL :

CREATE PROCEDURE [dbo].[STP_XXX_MySQLBarcode_Update]

@product_id int, @barcode varchar(20) AS BEGIN SET NOCOUNT ON DECLARE @SQL nvarchar(1000)

SET @SQL = 'CALL STP_products_Update_barcode(' + CAST(@Product_id as varchar) + ','''+ @barcode +''')' EXEC (@SQL) AT MYSQL_XXX SET NOCOUNT OFF END GO

Haven't got the hang of formatting code on here and am a bit rushed at the moment, but you get the idea...