I have a very basic app that plugs data into a stored procedure which in turn returns a recordset. I've been experiencing what I thought were 'timeouts'. However, I'm now no longer convinced that this is what is really happening. The reason why is that the DBA and I watched sql server spotlight to see when the sproc was finished processsing. As soon as the procedure finished processing and returned a recordset, the CF page returned a 'timeout' error. I'm finding this to be consistent whenever the procedure takes longer than a minute. To prove this, I created a stored procedure with nothing more than this:
WAITFOR DELAY '00:00:45';
SELECT TOP 1000 * FROM AnyTableName
If I run it for 59 seconds I get a result back in CF. If I change it to on minute:
WAITFOR DELAY '00:01';
I get a cfstoredproc timeout error. I've tried running this in different instances of CF on the same server, different databases/datasources. Now, what is strange, is that I have other procedures that run longer than a minute and return a result. I've even tried this locally on my desktop with CF10 and get the same result. At this point, I'm out of places to look so I'm reaching out for other things to try. I've also increased the timeout in the datasource connections and that didn't help. I even tried CF10 with the timeout attribute but no luck there either. What is consistent is that the timeout error is displayed when the query completes.