Hi,
There is an issue i am facing with query databse smart service, Where i am executing a stored procedure which fetches data from multiple tables and insert same data to a table.
- It works if i execute the stored proc with same parameters by SSMS(SQL Server Management Studio)
- By Query Database Smart Service it sometimes gives me error -
attached screenshot for reference
Thanks
Discussion posts and replies are publicly visible
First, are you using a Query Database node to execute the procedure, vs the Execute Stored Procedure service? Not that it cannot accomplish that, but based on your screen shot this is the case due to how the nodes appear:
How is the node configured in that case? As far as I recall, to use the Query Database node to fire a procedure you have to use a 'trick' such as we used back in the day with Oracle with the procedure is placed in the WHERE clause:
SELECT 1 FROM DUAL WHERE dbo.your_stored_procedure(ac!inputs) IS NULL
Otherwise my suggestion was going to be, with the Execute Stored Procedure service, to ensure your Run Validation input is set to False, as when this is true it will perform type validation each time the procedure is called, which will slow things down greatly.
pls find the screentshot that shows how i am executing proc
To note, Appian does have an OOTB service for executing a stored procedure and function - the Query DB node is not designed to do this (although you may be able to trick it to do so in some scenarios).
Are you not using this node due to data limitations? As you mention you need to return 4000 rows to save it to a different table - why not have the procedure do this without bringing it back into Appian in that case?
Hi Chris,
Procedure is not bringing fetched data back to appian process, it immediately insert that data into a table inside the ptocedure.
Shahid said:Procedure is not bringing fetched data back to appian process, it immediately insert that data into a table inside the ptocedure.
Which is confusing since you mention that the only reason you cannot use the execute stored procedure function or service is due to the size of data returned.?
Shahid said:Yes because in executeStoredProcedure function there is a limit of Max rows per result set 1000 rows, In my case there are around 4000 rows.
Lets start from the top, since you are executing a stored procedure, what happens when you use the Execute Stored Procedure Smart Service (with the "validate" parameter to "false"), which is designed for this. What errors do you receive?