Stephen, I'll share some thoughts. This is hard to explain in email because things are often not at all what they may seem on the surface. I help people solve these kind of problems all the time (via my consulting, over the web, and I'd be happy to offer that to you, satisfaction guaranteed or you don't pay for the time, which may take less than an hour). But in case you are averse to that, or want to try what you can for free, and in that I may help you (or others reading this), here you go.
First, yes, the DSN timeout could affect the CFSTOREDPROC, but which timeout do you mean? There are ones that apply to the connection pool, not to any CF processing itself. Second, are you saying there is no TIMEOUT on the CFSTOREDPROC in question? Third, and perhaps most significant, I would argue that the timeout may be due NOT to that tag, but to processing that preceded it. Before I elaborate on that, let me clarify that whatever the timeout time is, it's definitely being set in CF (not in IIS or the database or anything else), since the error is definitely CF's error saying that the request has exceeded the timeout. But as for where it may be, it's reasonable for you to assume that the timeout is happening on the CFSTOREDPROC that the error points to. But it may not be. I've spoken and written about this before. I'll summarize here but if you want more details, see: CF911: Lies, Damned Lies, and CF Request Timeouts...What You May Not Realize http://www.carehart.org/blog/client/index.cfm/2010/10/15/Lies_damned_lies_an d_CF_timeouts In brief, it could be that something prior to that CFSTOREDPROC is what really held up the request. CF will often let some long-running tag go well beyond any timeout (set on the tag, or in the CF Admin, or in CFSETTING, etc.) because CF can't interrupt the tag while it's running. This is most often with tags that talk to something outside of CF, like CFQUERY, CFSTOREDPROC, CFHTTO, etc. See the blog entry for more. The thing is, CF will indeed check the time for the request, but on a subsequent tag after that one that held things up (and it's not all tags where it checks the time. It does on ones like CFQUERY, CFSTOREDPROC, CFOUTPUT, CFLOOP, and so on, but not on CFSET, CFIF, and so on.) So you would want to look at what precedes that CFSTOREDPROC in the flow of control. It could be another CFQUERY or CFSTOREDPROC, but it could also be a CFHTTP, or a CFINVOKE, or perhaps even a CFLOCK, which got held up. And note that the timeout (whatever it is, which is causing this check on this CFSTOREDPROC to say "we've taken to long") could really be any value at all, less than the 120 seconds you are clocking. For instance, it may be that there's a CFSETTING REQUESTTIMEOUT somewhere in the request (in that file, in an included file, or in the application.cfc/cfm) which sets the timeout to say, 60 seconds. Again, some tags simply will not stop when that time is hit, but they will finish (in your case, in less than 120 seconds), and then processing would fall through and hit this tag that DOES check the time (your CFSTOREDPROC), where it errors and says the request has taken too long. I realize that this can be mind-numbing to consider-and even frustrating when we are all led to believe that the error is what it says. That's why I blogged what I did (and have presented on it, too, including in the cfobjective lightning round of talks last year). It's really an important problem that many don't understand and are misled by. Finally, note that you don't even need to guess at why the request is taking long. If you have CF Enterprise (and its Server Monitor) or CF Std or Enterprise and FusionReactor or SeeFusion, you can use those tools to get a "stack trace" while the request is running (or even by email if you setup an alert), which will tell you exactly what line of code is running in a long-running request at a point in time. For more on that, see my blog entry and talks at: CF911: Easier thread dumps and stack traces in CF: how and why http://www.carehart.org/blog/client/index.cfm/2009/6/24/easier_thread_dumps If any of that has helped, great. If you may think it's on the right track but would you be interested some help, it shouldn't take more than 15 minutes for us to find and solve this, over the web. If you may be interested, check out www.carehart.org/consulting/. I am in the US, at GMT-4 but I'm always happy to work later or early to help folks in timezones that are far from me. /charlie From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of Stephen M Sent: Thursday, March 29, 2012 12:56 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CF9 & SQL Server on Amazon Web Servers On Friday, January 27, 2012 10:21:44 PM UTC+11, Stephen M wrote: I've replicated this problem on my local workstation using a network db server, so its no longer about Amazon. I can get the timeout error on the stored proc as below. But the problem is I am getting this timeout at the 120 sec mark when the CF Request timeout, the datasource query tiomeout and the IIS connection timeouts are all set to 300 secs or more. A stupid question - does the query timeout in the datascource setting apply to stored procs as well? I assume that it would. I also notice that on my dev server I have some extra settings under Request Tuning that I don't have on the win 2008 servers which are only CF9 standard. The extra settings are for Request Queue Timeout settings. I have increased that to the same as the regular request timeout because it says "If a request has waited in the queue for this long, timeout the request. This value should be at least as long as the Request Timeout setting (currently 300 seconds)." The next setting is for a Request Queue Timeout page "Specify a relative path from the web root to an HTML page to send to clients when a template request times out before running, for example /CFIDE/timeout.html. The page you specify cannot contain CFML. If you do not specify a page, clients receive a 500 Request Timeout error when their request does not run. " This immediately attracts my attention because it is the first time I have seen the mention of the 500 Request Timeout error which is what I get if I do not set Enable HTTP status codes. Changing the Request Queue Timeout hasn't made any difference and I don't have that setting on the server anyway but I'm curious as to what effect it actually has. The real timeout (as timed by me with my watch) is 120 secs, so I was wondering if the request waiting after the stored proc in the queue is the one triggering the timeout error. As in, the stored proc finally comes back after 2 minutes, then CF looks at the next request in the queue and says that CFLOOP has been there too long so its timed out. But in that case the Request Queue timeout setting should fix it but it hasn't. I'll try a reboot and run it again. ================= 15:33:20.020 - Application Exception - in C:\WorkingDirectory\RTE\wwwroot\trunk\RTEreports\com\roymorgan\RTEreports\se rvices\ReportDAO.cfc : line 83 The request has exceeded the allowable time limit Tag: CFSTOREDPROC -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To view this discussion on the web visit https://groups.google.com/d/msg/cfaussie/-/cJmIKCjsQcsJ. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
