?
Solved

cfquery sql erratic error

Posted on 2011-10-19
18
Medium Priority
?
292 Views
Last Modified: 2012-05-12
I have a complex dynamic sql query string that gives me an occasional error.  I create a query that gives me a variable number of rows (4 or 5) and I use an acronym column from that query to dynamically build the main query.  I use a cfloop to create select columns and joins and use the acronym to prefix the alias name.  The same cfloop is used 4 times during the build. Occasionally, the loop uses the same (first) row of the query over and over instead of looping through the rows.  I have attached the source code and a copy of the generated query string.  Occasionally I get an error one of the dynamically built select columns can't be bound.  I can't reproduce the error. In my humble opinion there is a timing problem between Coldfusion and MS SQL.

The second code snippet is actually the second type of error message I get.
The cfinvoke within this function returns a query with "hr", "mkt", "tax", and "ag" as acronyms used in the cfloops.


<cffunction name="getPackages" output="false" returntype="query">
		<cfargument name="indivNum" required="No" type="string" default="" />
		<cfargument name="companyNum" required="No" type="string" default="" />
		<cfset var queryResults="" />
<cfinvoke component="/control/sitewide/cfc.services" method="getContactTypes" servicelineid="13" returnvariable="Qcontacts2" />
		<cfquery datasource="#request.dsn#" name="queryResults">
			SELECT 0 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 1 
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum#) or
					</cfloop>
					0 = 1)
			</cfif>
			union all
			SELECT 1 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 0			
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
 					and (
					<cfloop query="Qcontacts2">
						(#Qcontacts2.contact_type_acronym#.contact_id > 0 and #Qcontacts2.contact_type_acronym#.package = 0) or
					</cfloop>
					0 = 1) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# ) or
					</cfloop>
					0 = 1)
			</cfif>
			ORDER BY id desc, activecheck, start_date DESC 
		</cfquery>
		<cfreturn queryResults />
	</cffunction>

Open in new window

Error Executing Database Query. 
[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "uiha.lastname" could not be bound.   The error occurred in D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\cfc\basic.cfc: line 824
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\dspIndiv.cfm: line 475
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\dspIndiv.cfm: line 1
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\index.cfm: line 26
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\cfc\basic.cfc: line 824
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\dspIndiv.cfm: line 475
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\dspIndiv.cfm: line 1
Called from D:\inetpub\wwwroot\TaxTalent.net\Production\control\individuals\index.cfm: line 26
822 :                           AND (         
823 :                           <cfloop query="Qcontacts2">
824 :                                 (#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.package = 0) or
825 :                                 </cfloop>
826 :                                 0 = 1)
________________________________________
VENDORERRORCODE	  4104
SQLSTATE	  HY000
SQL	   SELECT 0 as activecheck,cs.id,cs.status,cs.position_title,cs.start_date,cs.end_date,cs.reg_billrate, cws.as400_initials as cons_working_srch_name , rrj.as400_initials as rec_referring_jo_lead_name , cs.position_title ,c.name as companyname ,uiha.lastname + ', ' + uiha.firstname as hacontactname, ha.contact_id as hacontact_id, ha.active as haactive ,uihr.lastname + ', ' + uihr.firstname as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,uiit.lastname + ', ' + uiit.firstname as itcontactname, it.contact_id as itcontact_id, it.active as itactive ,uirp.lastname + ', ' + uirp.firstname as rpcontactname, rp.contact_id as rpcontact_id, rp.active as rpactive FROM contract_staffing as cs LEFT OUTER JOIN admin_users as cws on cws.adminid = cs.cons_working_srch LEFT OUTER JOIN admin_users as rrj on rrj.adminid = cs.rec_referring_jo_lead LEFT JOIN companies as c on c.id = cs.company_id left join client_services_contacts as ha on ha.contacttypeid = 4 and ha.service_id = cs.ID and ha.active = 1 left join client_services_contact_Types as hatypes on hatypes.id = ha.contacttypeid left JOIN Users_Info AS uiha ON uiha.ID = ha.contact_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = cs.ID and hr.active = 1 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS uihr ON uihr.ID = hr.contact_id left join client_services_contacts as it on it.contacttypeid = 6 and it.service_id = cs.ID and it.active = 1 left join client_services_contact_Types as ittypes on ittypes.id = it.contacttypeid left JOIN Users_Info AS uiit ON uiit.ID = it.contact_id left join client_services_contacts as rp on rp.contacttypeid = 9 and rp.service_id = cs.ID and rp.active = 1 left join client_services_contact_Types as rptypes on rptypes.id = rp.contacttypeid left JOIN Users_Info AS uirp ON uirp.ID = rp.contact_id WHERE 0=0 AND ( (ha.contact_id = 36116 and ha.package = 0) or (hr.contact_id = 36116 and hr.package = 0) or (it.contact_id = 36116 and it.package = 0) or (rp.contact_id = 36116 and rp.package = 0) or 0 = 1) union all SELECT 1 as activecheck,cs.id,cs.status,cs.position_title,cs.start_date,cs.end_date,cs.reg_billrate, cws.as400_initials as cons_working_srch_name , rrj.as400_initials as rec_referring_jo_lead_name , cs.position_title ,c.name as companyname ,uiha.lastname + ', ' + uiha.firstname + '(PREV)' as hacontactname, ha.contact_id as hacontact_id, ha.active as haactive ,uihr.lastname + ', ' + uihr.firstname + '(PREV)' as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,uiit.lastname + ', ' + uiit.firstname + '(PREV)' as itcontactname, it.contact_id as itcontact_id, it.active as itactive ,uirp.lastname + ', ' + uirp.firstname + '(PREV)' as rpcontactname, rp.contact_id as hrcontact_id, hr.active as hractive FROM contract_staffing as cs LEFT OUTER JOIN admin_users as cws on cws.adminid = cs.cons_working_srch LEFT OUTER JOIN admin_users as rrj on rrj.adminid = cs.rec_referring_jo_lead LEFT JOIN companies as c on c.id = cs.company_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = cs.ID and hr.active = 0 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS uihr ON uihr.ID = hr.contact_id left join client_services_contacts as oth on oth.contacttypeid = 8 and oth.service_id = cs.ID and oth.active = 0 left join client_services_contact_Types as othtypes on othtypes.id = oth.contacttypeid left JOIN Users_Info AS uioth ON uioth.ID = oth.contact_id left join client_services_contacts as tax on tax.contacttypeid = 10 and tax.service_id = cs.ID and tax.active = 0 left join client_services_contact_Types as taxtypes on taxtypes.id = tax.contacttypeid left JOIN Users_Info AS uitax ON uitax.ID = tax.contact_id WHERE 0=0 AND ( (hr.contact_id = 36116 and hr.package = 0) or (oth.contact_id = 36116 and oth.package = 0) or (tax.contact_id = 36116 and tax.package = 0) or 0 = 1) ORDER BY cs.ID , activecheck

Open in new window

errormsg.PNG
0
Comment
Question by:lantervj
  • 9
  • 9
18 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 36994981
  > I can't reproduce the error.

Sounds suspiciously like the result of race conditions. One cause is forgetting to VAR scope function local variables.  When that happens, the variables are placed into the shared VARIABLES scope, causing weird things can happen if 2 threads hit the code at the same time.  Because they're both using the same variable.  That's why you must VAR scope every single function local variable. Including the results of other cfinvoke calls:

     <cfinvoke component="/control/sitewide/cfc.services" ... returnvariable="Qcontacts2" />

Go through the function above (and any functions it's using) and be sure to var scope everything.  I'm guessing that will fix your problem.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36995074
> Qcontacts2.contact_type_acronym

Note, I'm assuming the underlying data used to generate the sql in the first place isn't changing.  If however, it's constantly changing, then that type of error is to be expected.  ie It's just one of the hazards of dynamic data.
0
 

Author Comment

by:lantervj
ID: 36997393
QContacts2 is a constant query reused to generate dynamic sql strings. Everything I see seems to be properly scoped.  The one constant with the particular error that occurs erratically is it's the second occurance of the cfloop;

<cfloop query="Qcontacts2">
                        left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 1
                        left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid
                        left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id
                  </cfloop>

It generates the same dynamic sql strings for each iteration of the loop.  For example;

left join client_services_contacts as #Qcontacts2.contact_type_acronym#

should resolve to;

left join client_services_contacts as hr
left join client_services_contacts as mkt
left join client_services_contacts as tax
left join client_services_contacts as ag

but the resulting sql string from the error message shows;


left join client_services_contacts as hr
left join client_services_contacts as hr
left join client_services_contacts as hr
left join client_services_contacts as hr


0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:lantervj
ID: 36997397
The other three occurances of the cfloop of QContacts2 show the proper iterations and creation of sql strings.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36997501
  > Everything I see seems to be properly scoped.

"Qcontacts2" isn't.  Any variable you create within the function has to be var scoped.  Otherwise it ends up in the shared variables scope. In this case, you could have multiple threads all trying to initialize or loop through "Qcontacts2" at the same time.  As it's the same object, which thread would win? How would CF even know which row the loop was on?  Like I said, lack of var scoping can cause all sorts of wacky things to happen. The only way to prevent it is to ensure you var scope everything.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36997539
Gah.. hit return too soon.

I was going to add that since var scoping is quick and easy, you should try it first. See if it resolves the issue. If not, then try more extensive debugging.
0
 

Author Comment

by:lantervj
ID: 36999414
I guess I meant to say that I've added;

<cfset var Qcontacts2="" />

and nothing else needs to be var scoped.  All I can do now is wait and see since I can't reproduce the error.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37000132
Ah, ok.

Sounds like a plan. Btw, if you ever need to check whether variables are var scoped, structCount() the variables scope at the very beginning and end of the function. If the count stays the same, you know everything was properly var scoped.
0
 

Author Comment

by:lantervj
ID: 37000976
Justgot another error;
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The correlation name 'billtypes' is specified multiple times in a FROM clause.

This time the same cfloop should have had iterations of "bill", "hr",
"tax", and"ag".  It actually resolved to "bill", "hr", "tax", and "bill" causing a duplicate of the "bill" portion.

SELECT 0 as activecheck,jb.ID, jb.status, jb.jobtitle, jb.comptitle, jb.jobdate, jb.closedate, jb.feetype, c.name as companyname ,uibill.lastname + ', ' + uibill.firstname as billcontactname, bill.contact_id as billcontact_id, bill.active as billactive ,uihr.lastname + ', ' + uihr.firstname as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,uitax.lastname + ', ' + uitax.firstname as taxcontactname, tax.contact_id as taxcontact_id, tax.active as taxactive ,uiag.lastname + ', ' + uiag.firstname as agcontactname, ag.contact_id as agcontact_id, ag.active as agactive ,uicmp.lastname + ', ' + uicmp.firstname as cmpcontactname, cmp.contact_id as cmpcontact_id, cmp.active as cmpactive FROM Board_Details as jb LEFT JOIN companies as c on c.id = jb.companyid left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = jb.ID and hr.active = 1 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS uihr ON uihr.ID = hr.contact_id left join client_services_contacts as tax on tax.contacttypeid = 10 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 1 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS uibill ON uibill.ID = bill.contact_id WHERE 0=0 and ad_type = 'Intern' AND ( (bill.contact_id = 94299 and bill.package = 0) or (hr.contact_id = 94299 and hr.package = 0) or (tax.contact_id = 94299 and tax.package = 0) or (ag.contact_id = 94299 and ag.package = 0) or (cmp.contact_id = 94299 and cmp.package = 0) or 0 = 1) union all SELECT 1 as activecheck,jb.ID, jb.status, jb.jobtitle, jb.comptitle, jb.jobdate, jb.closedate, jb.feetype,c2.name as companyname ,ui2bill.lastname + ', ' + ui2bill.firstname + '(PREV)' as billcontactname, bill.contact_id as billcontact_id, bill.active as billactive ,ui2hr.lastname + ', ' + ui2hr.firstname + '(PREV)' as hrcontactname, hr.contact_id as hrcontact_id, hr.active as hractive ,ui2tax.lastname + ', ' + ui2tax.firstname + '(PREV)' as taxcontactname, tax.contact_id as taxcontact_id, tax.active as taxactive ,ui2ag.lastname + ', ' + ui2ag.firstname + '(PREV)' as agcontactname, ag.contact_id as agcontact_id, ag.active as agactive ,ui2cmp.lastname + ', ' + ui2cmp.firstname + '(PREV)' as cmpcontactname, cmp.contact_id as cmpcontact_id, cmp.active as cmpactive FROM Board_Details as jb LEFT JOIN companies as c2 on c2.id = jb.companyid left join client_services_contacts as bill on bill.contacttypeid = 2 and bill.service_id = jb.ID and bill.active = 0 left join client_services_contact_Types as billtypes on billtypes.id = bill.contacttypeid left JOIN Users_Info AS ui2bill ON ui2bill.ID = bill.contact_id left join client_services_contacts as hr on hr.contacttypeid = 5 and hr.service_id = jb.ID and hr.active = 0 left join client_services_contact_Types as hrtypes on hrtypes.id = hr.contacttypeid left JOIN Users_Info AS ui2hr ON ui2hr.ID = hr.contact_id left join client_services_contacts as tax on tax.contacttypeid = 10 and tax.service_id = jb.ID and tax.active = 0 left join client_services_contact_Types as taxtypes on taxtypes.id = tax.contacttypeid left JOIN Users_Info AS ui2tax ON ui2tax.ID = tax.contact_id left join client_services_contacts as ag on ag.contacttypeid = 19 and ag.service_id = jb.ID and ag.active = 0 left join client_services_contact_Types as agtypes on agtypes.id = ag.contacttypeid left JOIN Users_Info AS ui2ag ON ui2ag.ID = ag.contact_id left join client_services_contacts as cmp on cmp.contacttypeid = 20 and cmp.service_id = jb.ID and cmp.active = 0 left join client_services_contact_Types as cmptypes on cmptypes.id = cmp.contacttypeid left JOIN Users_Info AS ui2cmp ON ui2cmp.ID = cmp.contact_id WHERE 0=0 and ad_type = 'Intern' AND ( (bill.contact_id = 94299 and bill.package = 0) or (hr.contact_id = 94299 and hr.package = 0) or (tax.contact_id = 94299 and tax.package = 0) or (ag.contact_id = 94299 and ag.package = 0) or (cmp.contact_id = 94299 and cmp.package = 0) or 0 = 1) ORDER BY jb.jobdate DESC, jb.id, activecheck

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 37001122

1. Can you post the current code for "getPackages" and "getContentTypes"?

2. Put a cftry/cfcatch around the query that's failing.  Inside the cfcatch, email yourself a full dump of #Qcontacts2#, along with the error message.  So next time the error occurs, you can check the query results against the actual query generated for further clues.

3. Are either of these functions/components being stored in a shared scope?

4. As a last resort you try could supplying the row number explicitly, though you shouldn't have to.
ie <cfloop query="Qcontacts2">
         #Qcontacts2.columnName[currentRow]#
   </cfloop>
0
 

Author Comment

by:lantervj
ID: 37001345
The following functions are all invoked in sequence and each one is very similar to the other.  I have attached getPackages and getContractTypes.  The error can happen in any one of the functions. The function getContactTypes will return the apporpriate acronyms for the particular function that calls it by changing the argument servicelineid..

getContractStaffing
GetExecutiveSearches
getJobAds
getOrgAssessment
getPackages
getPurchases
getSalaryBenchmarking
<cffunction name="getPackages" output="false" returntype="query">
		<cfargument name="indivNum" required="No" type="string" default="" />
		<cfargument name="companyNum" required="No" type="string" default="" />
		<cfset var queryResults="" />
		<cfset var Qcontacts2="" />
		<cfinvoke component="/control/sitewide/cfc.services" method="getContactTypes" servicelineid="13" returnvariable="Qcontacts2" />
		<cfquery datasource="#request.dsn#" name="queryResults">
			SELECT 0 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 1 
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum#) or
					</cfloop>
					0 = 1)
			</cfif>
			union all
			SELECT 1 as activecheck,p.id,p.status,p.start_date,p.end_date,p.total_fee,ai.name as marketer, c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id, #Qcontacts2.contact_type_acronym#.active as #Qcontacts2.contact_type_acronym#active
			</cfloop>
			FROM packages as p
			LEFT JOIN admin_users as ai on ai.adminid = p.marketer_id
			LEFT JOIN companies as c on c.id = p.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = p.ID and #Qcontacts2.contact_type_acronym#.active = 0			
				left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0
			<cfif val(arguments.CompanyNum) NEQ 0>
				AND (p.company_ID = 
				<cfqueryparam value="#arguments.companynum#" /> or p.agency_ID = 
				<cfqueryparam value="#arguments.companynum#" />) 
 					and (
					<cfloop query="Qcontacts2">
						(#Qcontacts2.contact_type_acronym#.contact_id > 0 and #Qcontacts2.contact_type_acronym#.package = 0) or
					</cfloop>
					0 = 1) 
			</cfif>	
			<cfif val(arguments.indivNum) NEQ 0>
				 AND (		 
				 <cfloop query="Qcontacts2">
					(#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# ) or
					</cfloop>
					0 = 1)
			</cfif>
			ORDER BY id desc, activecheck, start_date DESC 
		</cfquery>
		<cfreturn queryResults />
	</cffunction>

Open in new window

<cffunction name="getContactTypes" returntype="query" access="remote">
		<cfargument name="servicelineID" required="yes" />
		<cfif val(arguments.servicelineID) eq 0>
			<cfquery datasource="#request.dsn#" name="contacttypes">
				select * from client_services_contact_types group by id, description, contact_type_acronym, servicelineids order by id
			</cfquery>
		<cfelse>
			<cfquery datasource="#request.dsn#" name="contacttypes">
				select * from client_services_contact_types where (CHARINDEX(',#arguments.servicelineid#,', ','+servicelineids+',') > 0) group by id, description, contact_type_acronym, servicelineids order by id 
			</cfquery>
		</cfif>
		<cfreturn contacttypes />
	</cffunction>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 37001393
>  <cfquery datasource="#request.dsn#" name="contacttypes">

Looks like there's still queries/variables that aren't VAR scoped.  You need to go through all of the functions you listed and make sure every single query, cfinvoke result, etc... is var scoped.  Use structCount(variables) at the beginning and end of the function to make sure you've gotten them all.
0
 

Author Comment

by:lantervj
ID: 37001658
The structcount before and after each cfquery in each function is a constant 100. That is, as of now.
I am emailing myself the count before and after.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37001966
The structCount() is just a one time thing. Once you've fixed the code, it won't change.
0
 

Author Comment

by:lantervj
ID: 37002080
I understand. But, those functions I listed above are called from two different pages.  The only time I get an error is when the indivnum is passed in. I am getting emails whether theyare called with indivnum or companynum. At first I was getting a count of 99 then 100.  Now they are all 100.  So, I guess that problem has been corrected.  All I can do now is wait and see if I get something from cftry/cfcatch.
0
 

Author Comment

by:lantervj
ID: 37007861
So far, so good.  Looks like it's working.  Thanks.
0
 

Author Closing Comment

by:lantervj
ID: 37007869
Tough one for me.  Easy for EE.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37008062
> So far, so good.  Looks like it's working.  

Great! Glad to hear it :)

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question