RE: [ cf-dev ] CF MS Access conflict?

Sam Westlake Wed, 07 Apr 2004 05:14:25 -0700

I am trying to execute the following query:

SELECT t_SectionXFA.subxfaID, t_SubXFA.subxfaKey, t_SubXFA.subxfaValue, t_SectionXFA.xfaID, t_XFA.xfaKey

FROM t_XFA INNER JOIN (t_SubXFA INNER JOIN t_SectionXFA ON t_SubXFA.subxfaID = t_SectionXFA.subxfaID) ON t_XFA.xfaID = t_SectionXFA.xfaID

where t_XFA.xfaKey = "planning"

</cfquery>

but I get an Error Executing Database Query exception thrown.

If I omit the [where t_XFA.xfaKey = "planning"] code then CFMX is happy. If I leave the line in then it throws the exception.

In t he Access query tool, the SQL generates a record set without complaints with or without the where statement included.

Is this a CF bug and what can I do about it? Is this an appropriate time to use Query of Queries?

Sam Westlake

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 07 April 2004 12:02
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] CF ODBC to unresponsive SQLServer, and fine-tuning ODBC data-sour ces

could you try adding another cfcatch, with type set to "any", and see if that catches it?

Duncan Cumming
IT Manager

http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600

Creative solutions in a technical world

----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------

"Osullivan Karl (RKB) Senior Analyst/Programmer" <[EMAIL PROTECTED]>

07/04/2004 11:58
Please respond to dev

To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc:
Subject: RE: [ cf-dev ] CF ODBC to unresponsive SQLServer, and fine-tuning ODBC data-sour ces

Sorry. The Endoscopy server has to be re-booted as it has become unresponsive (can't even be ping-ed) Once the Endoscopy server is responsive, then CF works again.

CF is on a different server but has to link to the Endoscopy server to get results.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 07 April 2004 11:56
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] CF ODBC to unresponsive SQLServer, and fine-tuning ODBC data-sour ces

you said that rebooting the server causes CF server to stop running - are the CF services set to Automatic?

Duncan Cumming
IT Manager

http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600

Creative solutions in a technical world

----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------

"Osullivan Karl (RKB) Senior Analyst/Programmer" <[EMAIL PROTECTED]>

07/04/2004 11:50
Please respond to dev

To: CF - Development Group <[EMAIL PROTECTED]>
cc:
Subject: [ cf-dev ] CF ODBC to unresponsive SQLServer, and fine-tuning ODBC data-sour ces

I'm having problems with my CF server in that it keeps hanging - not processing any CF pages at all. However, the Task Manager reports an Idle CPU of 98%.

I have narrowed the problem down to another server that I retrieve patient endoscopy results from, stored in SQLServer7. Sometimes this server becomes completely unresponsive and has to be re-booted, and somehow this in turn causes my CF server to stop processing .cfm pages. I know this is an on-going problem, so I put some error trapping in my program, such that:

<CFSET db_endoscopy_error = false>
<CFTRY>

<CFQUERY NAME="Get_Pt_Endo" DATASOURCE="#Request.DSN#">
SELECT ReportNo,
VersionNo
FROM Reports
WHERE Patient = '#URL.id#'
</CFQUERY>

<CFIF db_endoscopy_error>
Sorry, database is down
<CFELSE>
Show the results
</CFIF>

This seems to work if SQL Server is STOPPED on the server, but does NOT work if the whole Endoscopy server is unavailable. Is there something else I should be checking for? I thought, based on the CF ODBC settings below, it would try, fail after 5 seconds, and then report an error.

The settings I have in CF5 for Endoscopy are:

Limit connections: not ticked

Maintain database connections: ticked

Connection timeout: 20 minutes

I'm also a bit unsure as to whether these settings are optimum. Basically, the server only get's about 100 queries a day, and each only ever returns 1 or 2 records. So any advice on ODBC setup in CF would be appreciated.

Thanks

Karl O'Sullivan

RE: [ cf-dev ] CF MS Access conflict?

Reply via email to