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()'
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'"