SQL Server - Get Collection

[J_DEV_22]

Hi, I am sending a query to get collection using the SQL Server VBO, but due to the long execution time on the SQL Server, my action in BP is timing out. "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Does anyone know how to increase the timeout period? I know the query works, it just takes awhile to load.

Thanks.

 

[VJR]

Hi J_DEV_22,

Try the options given in the links after saving or taking the necessary backups-
https://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio
https://manage.accuwebhosting.com/k...Increase-the-MS-SQL-Remote-Query-Timeout.html

On the other hand try to see if the overall query execution time can be possibly reduced by tuning the query to allow it to execute within the normal acceptable time.

 

[J_DEV_22]

Hi VJR,

Thanks for the reply. I have tried both methods and am still seeing problems. Is there a way to alter the VBO BPA Object -Data - SQL Server code to increase its wait time?

 

[VJR]

Hi J_DEV_22,

Based on this link it looks like a time out can be set to the command object. The command object is used in the code stage of the Execute action of Data- SQL Server VBO so you may want to give that a try. Once again since this is trial and error do take the necessary backups.

 

[J_DEV_22]

Thanks VJ,

I applied the following code block in the Get Data Table action on the 'Get Collection' tab and it has seemed to work thus far...

Using cmd As New SqlCommand()
cmd.Connection = moConnection
cmd.Transaction = moTransaction
cmd.CommandText = SQL
cmd.CommandTimeout = 180

 

[VJR]

Thanks VJ,

I applied the following code block in the Get Data Table action on the 'Get Collection' tab and it has seemed to work thus far...

Using cmd As New SqlCommand()
cmd.Connection = moConnection
cmd.Transaction = moTransaction
cmd.CommandText = SQL
cmd.CommandTimeout = 180

That is great to know that you've got it working. In case a timeout of 180 works sometimes and doesn't at certain times then as mentioned on the link you can also use a value of 0 which indicates to wait forever but keeping in mind that using so may get delicate at times.

 

[cs007]

That is great to know that you've got it working. In case a timeout of 180 works sometimes and doesn't at certain times then as mentioned on the link you can also use a value of 0 which indicates to wait forever but keeping in mind that using so may get delicate at times.

Thank you so much ! This solutions worked fine, can you please let me know if i want to make the same change to "get csv file" option where do i need to change!