SQL Execution timeout

0

Hi Michał,

I can't answer your question, but if the query is taking so long and there is no way to optimize it, you may want to consider creating a table in the database which will be periodically fed with data from your query, and then use it to populate a data table. However, I don't know your case and perhaps such an approach will not work for you, so it is just an idea :)

Hi Michał,

I also would recommend to change your approach. Even if you would increase the timeout you may run into it again at a later point in time.

Another alternative could be to execute a PS Script, which spawns a new session and executes your script. You could use the invoke-sqlcmd for this.
https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
Ones the command is executed the session closes automatically.
The workflow could trigger the execution and move into a system step. A timeout action could verify if the data is populated, or execute some other PS script to read some generated file, that the first PS has been executed successfully.

Best regards,
Daniel

This might be a good idea, as the report changes once a day
Thanks!

Michał