Executing MySQL Stored Procedures from SQL Server

If you ever need to call a MySQL procedure from SQL Server it’s fairly simple thanks to ODBC and Linked Servers. This will allow you to reuse any logic already invested in MySQL Stored Procedures saving you from rewriting them. Here’s a simple example on how you can do it;

  • Create the following procedure in your MySQL ‘test’ database.

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`usp_test`$$
CREATE PROCEDURE `test`.`usp_test`()    
	LANGUAGE SQL
	DETERMINISTIC
	SQL SECURITY INVOKER
	COMMENT 'Test Procedure returns the numbers 1-5'
BEGIN
	SELECT 1
	UNION ALL
	SELECT 2
	UNION ALL
	SELECT 3
	UNION ALL
	SELECT 4
	UNION ALL
	SELECT 5;
END$$
DELIMITER ;

If you get the following message  

Msg 7411, Level 16, State 1, Line 1

Server 'MYSQL' is not configured for RPC.

You need to change the Linked Server property "RPC Out" to true.

e3869711c83f74a9efcad187178a7718.124.111 Executing MySQL Stored Procedures from SQL Server

  • Once your Linked Server has been configured correctly you’re ready to go. Execute the following T-SQL in SSMS.

EXEC('CALL test.usp_test()') AT MYSQL;

8ea97d08e4738dc1899b67efc91f03a8.124.117 Executing MySQL Stored Procedures from SQL Server

In fact the EXEC can be used to run any MySQL specific command. All the following will work;

EXEC('SHOW TABLES') AT MySQL; -- Show the tables in the current database
EXEC('SHOW SLAVE STATUS') AT MySQL; -- Show Slave status info (if applicable)
EXEC('SHOW DATABASES') AT MySQL; -- Show the accessible databases
EXEC('SHOW CREATE TABLE mysql.user') AT MySQL; -- Show the SQL used to create mysql.users

MySQL Linked Servers work pretty solidly with SQL Server and allow complex integrations between systems to happen reasonably easily. So if your needs are simple there’s no need to resort to an additional layer like SSIS if it’s going to complicate your environment.

1 comment on this post.
  1. TimH:

    Thank you for this! It was tremendously helpful!!!

Leave a comment





Current day month ye@r *