I was in a hurry when I typed the previous response and found a glitch
in the logic. In the inner cfcatch blocks, I am throwing the errors. So,
whenever there is a problem, the "yourOwnError" exception will be
thrown. That means you will never see " Send "MSSQLSERVER Not Avail and
Agent Not Avail" in cfmail."
To resolve that, at the top of my head, just remove the <cfthrow> tags
in the cfcatch blocks. Handle it outside the cftry and cfcatch. I don't
think you would even need to nest the cftry anymore. So, the revised
version will be like this:
<!--- Set the variable for your second issue here. --->
<cfset notRunQuery = 0 >
<cfset problemQueryNumber = 0>
<cftry>
<cfstoredproc name="checksrv" datasource="master"
returnCode="YES" result="storedProc1Return">
SELECT @@SERVICENAME;
</cfstoredproc>
<cfcatch type = "database">
<cfset notRunQuery = notRunQuery + 1 >
<cfset problemQueryNumber = 1>
</cfcatch>
</cftry>
<cftry>
<cfstoredproc name="checkage" datasource="master"
returnCode="YES" result="storedProc2Return">
SELECT count (program_name)
FROM master.dbo.sysprocesses
WITH (NOLOCK)
WHERE program_name LIKE '%Agent%'
</cfstoredproc>
<cfcatch type = "database">
<cfset notRunQuery = notRunQuery + 1 >
<cfset problemQueryNumber = 2>
</cfcatch>
</cftry>
<cfif notRunQuery is 2>
<!--- Send "MSSQLSERVER Not Avail and Agent Not Avail" in
cfmail. --->
<cfelseif notRunQuery is 1>
<cfif problemQueryNumber = 1>
<!--- Send "MSSQLSERVER Not Avail" in cfmail.
--->
<cfelseif problemQueryNumber = 2>
<!--- Send "Agent Not Avail" in cfmail. --->
</cfif>
</cfif>
<!---
Check the errors(return values) with from two stored
procedures.
Do CFMail here by checking two return structs
("storedProc1Return and storedProc2Return")
--->
-----Original Message-----
From: PINE Phyo Z
Sent: Wednesday, February 08, 2006 8:53 AM
To: 'cf-talk@houseoffusion.com'
Subject: RE: cftry and cfcatch
I'm guessing here but I think your two queries are using stored
procedures. So, use cfstoredproc to check the return values. Then, your
first issue will be resolved. Then use nested cftry and cfcatch for each
stored proc to resolve the second issue.
So probably something like this:
<cftry>
<!--- Set the variable for your second issue here. --->
<cfset notRunQuery = 0 >
<cfset problemQueryNumber = 0>
<cftry>
<cfstoredproc name="checksrv" datasource="master"
returnCode="YES" result="storedProc1Return">
SELECT @@SERVICENAME;
</cfstoredproc>
<cfcatch type = "database">
<cfset notRunQuery = notRunQuery + 1 >
<cfset problemQueryNumber = 1>
<cfthrow type = "yourOwnError">
</cfcatch>
</cftry>
<cftry>
<cfstoredproc name="checkage" datasource="master"
returnCode="YES" result="storedProc2Return">
SELECT count (program_name)
FROM master.dbo.sysprocesses
WITH (NOLOCK)
WHERE program_name LIKE '%Agent%'
</cfstoredproc>
<cfcatch type = "database">
<cfset notRunQuery = notRunQuery + 1 >
<cfset problemQueryNumber = 2>
<!--- Throw your own error for the outermost cfcatch to catch
--->
<cfthrow type = "yourOwnError">
</cfcatch>
</cftry>
<cfcatch type ="yourOwnError">
<cfif notRunQuery is 2>
<!--- Send "MSSQLSERVER Not Avail and Agent Not Avail" in
cfmail. --->
<cfelseif notRunQuery is 1>
<cfif problemQueryNumber = 1>
<!--- Send "MSSQLSERVER Not Avail" in cfmail.
--->
<cfelseif problemQueryNumber = 2>
<!--- Send "Agent Not Avail" in cfmail. --->
</cfif>
</cfif>
</cfcatch>
</cftry>
<!---
Check the errors(return values) with from two stored
procedures.
Do CFMail here by checking two return structs
("storedProc1Return and storedProc2Return")
--->
HTH,
Pine
-----Original Message-----
From: John Lucania [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 07, 2006 6:00 PM
To: CF-Talk
Subject: cftry and cfcatch
I have two queries:
<cfquery name="checksrv" datasource="master">
SELECT @@SERVICENAME;
</cfquery>
<cfquery name="checkage" datasource="master">
SELECT count (program_name)
FROM master.dbo.sysprocesses
WITH (NOLOCK)
WHERE program_name LIKE '%Agent%'
</cfquery>
I want to be notified through cfmail if
1)
query checksrv doesn't return "MSSQLSERVER"
then, "MSSQLSERVER not returned" in cfmail
or
query checkage doesn't return 2
then, "Agent not returned 2" in cfmail
or
both of checksrv and checkage don't return
then, "MSSQLSERVER not returned and Agent not returned 2" in
cfmail or
2)
query checksrv cannot be run
then, "MSSQLSERVER Not Avail" in cfmail
or
query checkage cannot be run
then, "Agent Not Avail" in cfmail
or
both of checksrv and checkage cannot be run
then, "MSSQLSERVER Not Avail and Agent Not Avail" in cfmail.
Any ideas?
tia,
jl
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231658
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
