Error when calling stored procedure - Error SQLExtendedFetch: Function sequence error

[knhopper]

Hi,

I'm working on an Alteryx flow that needs to call a stored procedure. I have tried using both ODBC and OLE DB for the driver. The stored procedure call works perfectly in SQL Server Management Studio (SSMS) and SQL Server Integration Services (SSIS). I know my driver is working because I'm able to view all of the tables and stored procedures in the stored procedure tab of the input tool. The error I'm getting looks like this: 

Error: Input Data (3): Error SQLExtendedFetch: [Microsoft][ODBC Driver 17 for SQL Server]Function sequence error

Another relevant detail, this stored procedure calls other stored procedures in its runtime. I'm not sure if this is contributing to the issue. I checked Microsoft's documentation, if you scroll to the Function sequence error section, it looks like there are a few different reasons this particular error pops up. I'm just not sure which is the problem here. It seems like maybe the stored procedure was not done running when the SQLExtendedFetch function was called. 

https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlextendedfetch-function?view=sql-serve...

More about SQLExtendedFetch from the above link:

"Summary
SQLExtendedFetch fetches the specified rowset of data from the result set and returns data for all bound columns. Rowsets can be specified at an absolute or relative position or by bookmark."

Is there a way to delay when SQLExtendedFetch is run or force it to wait for the stored procedure to finish running? 

Thank you for any help you can offer!

[knhopper · Accepted solution]

Hi, wanted to share that I found a solution on my own. In case anyone ever runs into this problem and finds my post here, I want to share what worked for me. 

I referenced this page: https://community.alteryx.com/t5/Engine-Works/Troubleshooting-Stored-Procedures/ba-p/13355

It was two very straightforward settings that needed to be updated.

1) The driver for my SQL DB connection had to be set to SQL Server Native client driver

2) This was added in the pre sql statement field: SET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

That was it!

[tochoa]

Thank you for coming back to post the solution. 😎