Skip to main content
S
shahidsalamk0001
Inspiring
October 19, 2022
Question

Insufficient time to complete activity - Query Database smart service & Stored Procedure

  • October 19, 2022
  • 9 replies
  • 0 views

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 - 

The amount of time allocated for the completion of an unattended activity was insufficient to allow the activity to complete.

attached screenshot for reference

Thanks 

9 replies

stefanhelzle0001
stefanhelzle0001
Brainy
October 19, 2022

Is there a reason to not use the executeStoredProcedure function?

S
shahidsalamk0001Author
Inspiring
October 19, 2022

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.

stefanhelzle0001
stefanhelzle0001
Brainy
October 20, 2022

Wow, what do you do with 4000 rows in an Appian process !?!

The error above is coming from a timeout. Seems like your SP takes too long.

What is your use case?

csteward
csteward
October 20, 2022

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.

S
shahidsalamk0001Author
Inspiring
October 20, 2022

pls find the screentshot that shows how i am executing proc

csteward
csteward
October 20, 2022

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?

Terms & ConditionsAccessibility statement