Executing Stored Procedure in Alteryx filtering Records.

[UpendraPandey]

Hello Guys,

I am running SQL Server Stored Proc in alteryx Post SQL but its not loading the all the records. Not sure why.

Same Stored Proc if i am running directly through SSMS Or any UI tools its producing full results and loading all the records.

My Flow is like

Input has SQL statement which reads data from Oracle DB and Load into SQL Server table in Output.

and In Output Post SQL I am calling Stored Proc which reads data from Output table and do some calculation and load into another table.

Input row count : 43000

Output row count : 43000

Stored Proc row count into another table is Around 36000. Not sure why not all the 43000 is getting loaded into Another table.

In Below Example Not sure Why Stored Procedure is getting executed before Writing data into Out put tables.

[UpendraPandey]

Hello Guys,

I figured it out the dependency party by using "Block until Done " tool. Now my stored Procedure is running once the Output target is load completed.

But my other problem is still there.

Flow: 

Input has SQL statement which reads data from Oracle DB(43211 records)) and Load into SQL Server table in Output(43211).

and In another Output Post SQL I am calling Stored Proc which reads data from above Output table and doing some calculation and load into another output table.

Input row count : 43211

Output row count : 43211

Stored Proc row count into another table is 36201. Not sure why not all the 43211 is getting loaded into Another table.

Though the same Stored Procedure when i am running directly on database without Alteryx, its producing 43211 Records.

[ramesh_neel]

hey

have you tried running the sql statement that populates your final table outside stored procedure  and see how many records are returned? 

[UpendraPandey]

Yes I did run the same stored proc directly through SQL Server Management Studio and its giving the correct number for Records. But when using the Same stored proc with same parameter in Alteryx its not returning the same number of record.  To identify the where is the actual problem here is what I did.

Actual Input count as 44K, Run through SSMS  Stored Proc(SP) returns 44k but when I run through Alteryx it returned only 36K

Then I reduced the Input count as 30K , Run through SSMS , SP returns 30 K and run through Alteryx returned 30K

Then I increased the Input count to 37K , Run through SSMS, SP returns 37 K and when I run through Alteryx it only return 36K

so I think there is some where memory limitation which is creating this issue. But I am not sure what is happening exactly.

[mlingama]

Did you get this issue resolved?

I am having an odd issue where I call a Stored Proc after a table load and the Alteryx workflow dialog shows that the process called my Stored Proc but the stored proc did not actually run and do it's processing.  Then I have to manually run the process in SSMS to get my stored procs to run.

[UpendraPandey · Accepted solution]

@mlingama 

Yes i have resolved this. Actually what i did , I have added SET NOCOUNT ON; 

in my Stored Procedure. Actually now Stored Proc is not returning back the count to the Client(Alteryx) and its loading all the records.

Not sure if this is bug or limitation but its working for me.

[mlingama]

Thanks this seemed to have worked for me as well.