Power BI Report Builder Cannot Execute MySQL Stored Procedure

[gfross]

I'm trying to call a stored procedure from MySQL via an System DSN/ODBC Connection (MySQL ODBC 8.0 ANSI Driver) and I'm getting error 'ERROR [42000] [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.27]You have an error in your SQL syntax'. I can pull data via a dataset using a 'Text' Query Type and typing in a SQL Statement. I would prefer using a stored procedure with parameters that I can pass to it. This way I can reduce the size of the dataset.

I even tried creating a dataset with a 'Table'  Query Type and I get error 'An error occured while connecting to the data source. Only the text-based query designer will be available.' I made sure the 'Disable Schema Support' is unchecked in the MySQL ODBC Connector. Any help is appreciated.

[gfross]

I realized that I needed to select a query type of Text and not Stored Procedure. I guess MySQL stored procedures don't like the Stored Procedure type.

[gfross]

The MySQL stored procedure I'm using requires three parameters, so in query designer I have the following:

call sp_Lock(?,?,?)

However I get the following error message.

[v-kkf-msft · Accepted solution]

Hi @gfross ,

Please use "?" to pass procedure parameters.

MySQL Stored Procedure Call from SSRS through ODBC 

Take SQL Server as an example.

• Use the ODBC connector.

• Call the stored procedure.

exec ProductCategory ?

• Result.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.