SQL Timeouts

Sep 6, 2011 at 5:43 PM

Hi!

First off, great work on these SP actions. They've saved my bacon on more than one occasion! The Execute SQL in particular is incredibly handy, and I feel like I've only just scratched the surface of what I can use this one for.

I've been having a strange issue with one off my workflows that uses Execute SQL where I get "Error on executing: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." upon running the WF.  The list that I'm running the workflow on is huge (around 70000 entries), so the fact that it's timing out is not that wierd. What's odd is that it seems to fail 4 or 5 times, then works fine for any WFs I run after it's 'warmed up.'

Here's my SQL for reference:

SELECT Cast([sql_variant3] as nvarchar(4000))+ '_' as 'data()'
  FROM [WSS_Content].[dbo].[AllUserData]
    Where tp_ListId = '3B8D961E-BCD5-4021-BCE1-A8458DDBFA01' AND tp_RowOrdinal = 0 AND tp_IsCurrent = 1 AND nvarchar1 Like 'StudentName'AND datetime1 Between'Start' AND 'End' ANDnvarchar7 Like '%Teaching%' AND tp_DeleteTransactionId =0x
 GROUP BY [sql_variant6], [sql_variant3]
FOR xml path('_')

Connection String: Server=LocalServerName;Database=WSS_Content;User ID=*******;Password=*******

I've tried adding the ConnectionTimeout and CommandTimeout parameters to my connection string, but it doesn't seem to work in this context and I get the error "Keyword not supported: 'commandtimeout'"

Coordinator
Sep 9, 2011 at 7:10 AM

Hi,

it seems you that you can't set the command timeout in the connection string (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx). To solve it you could change it in in the source code or try to optimize the response time from your sql server.

Bye,

Christian

 

Sep 9, 2011 at 6:48 PM

Yeah, changing source code is a bit outside my area of expertise :) I downloaded your source to look at it, and frankly it scared me a little. Perhaps if you end up doing another version of this one you could make command timeout a parameter to this action? just a thought. I'll see if I can figure out another way around in the mean time.

Thanks!

Steve

Sep 14, 2011 at 1:48 PM

I found a workaround to this little problem. I thought I'd share in case anyone else has a similar issue. If you run a query on the same list from SQL Management Studio, it caches the data and the workflow action completes within the allowed time.