Problem Executing Informix Stored Procedures in SQL Server and PowerBI

[BlairSeaford]

Problem Executing Informix Stored Procedures in SQL Server and PowerBI

Problem: Cannot execute Informix stored procedures in SSMS or PowerBI using a Linked Server in SQL Server.  We are using three methods to access the database and executing stored procedures does not work for our Microsoft reporting tools.  Technical details, execute script and error messages below.

Informix DB:  - UCCX database (db_cra) as ‘uccxhruser’. 

• Version: Informix Version 11.6(1) Build 11.6.2.10000.38
• Connection: JDBC
• Driver: v4.50.00.18512

RazorSQL: I am able to access the database as ‘uccxhruser’ successfully using RazorSQL via JDBC to query tables, views and execute stored procedures.  No issues here.

• Version: RazorSQL v10.0.3 (64-bit)
• Connection: JDBC
• Driver: * JDBC (Informix JDBC Driver) – shipped with RazorSQL
• Query table/view: Yes
• Execute Stored Procs: Yes

SQL Server Management Studio (SSMS): We have a Linked Server in SQL Server to the UCCX database and we are connecting to it via ODBC using SSMS.  Problem not being able to execute stored procs.

• Version: SQL Server v13.0.6404.1 & SSMS v18.8
• Connection: ODBC
• Driver: v2017.175.01.01
• Query table/view: Yes
• Execute Stored Procs: NO

PowerBI: We have a Linked Server in SQL Server to the UCCX database and we are connecting to it via ODBC using PowerBI. Problem not being able to execute stored procs.

• Version: PowerBI v2.105.664.0 64-bit (May 2022)
• Connection: ODBC
• Driver: v2017.175.01.01
• Query table/view: Yes
• Execute Stored Procs: NO

Script used in RazorSQL, SSMS & PowerBI:

• execute procedure sp_aborted_rejected_call_detail ('2022-05-02 12:01:00','2022-05-06 11:59:00','0',null)

PowerBI/SSMS Error: Unable to connect.  We encountered an error while trying to connect. Details: "Microsoft SQL: Incorrect syntax near '2022-05-02 12:01:00'."

PowerBI Error:

SSMS Error:

[v-kkf-msft]

Hi @BlairSeaford ,

In order to execute stored procedure in Power BI:

• Go to SQL Server “right-click” on stored procedure and select “Execute”. After execution of your cod, a new query window opens up which was responsible for execution. Copy that Query.

• Go to Power BI Query Editor, Click on New Source and select SQL Server. After giving the server and database, click on "Advanced Options", paste the query in the "SQL Statement" . Navigate using full hierarchy  and click ok.

• You will see data for the parameters you passed in Stored procedures only.

For example:

EXEC GetProductDesc_withparameters2 @PID = 706, @Des = 'afs'

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.