19 >= 19 - query error

what the heck is this? 19 >= 19 The error occurred in E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20 18 :     A.*, B.name as PRMLNAME 19 :   FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email 20 :   where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> 21 : </CFQUERY> 22 : <cfoutput query="data"> It's worth noting that there are 18 columns in the table.  I dropped another column and ran the query again and I got "18 >= 18" (now there are only 17 columns) here's the full query:    SELECT      A.*, B.name as PRMLNAME    FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email    where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> Rick

What happens if you remove the cfqueryparam and run it again? what the heck is this? 19 >= 19 The error occurred in E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20 18 :     A.*, B.name as PRMLNAME 19 :   FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email 20 :   where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> 21 : </CFQUERY> 22 : <cfoutput query="data"> It's worth noting that there are 18 columns in the table.  I dropped another column and ran the query again and I got "18 >= 18" (now there are only 17 columns) here's the full query:    SELECT      A.*, B.name as PRMLNAME    FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email    where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> Rick

Rick, ----- Excess quoted text cut - see Original Post for more ----- The error may be because you've used an ambigious column name of "id". Try:   where A.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#" /> (I'm assuming the "id" column is in the results table.) -Dan

Dan G. Switzer, II wrote: > > The error may be because you've used an ambigious column name of "id". Try: > >   where A.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#" > /> That's interesting because the query worked exactly as it was until I started dropping other columns.  "Id" only exists in the dbo.results table so this usually works. I did solve the problem by specifying columns in my select list rather than using A.* ... still have the "ambiguous" id field in the where clause. Rick

This bug usually happens when you change something on the database server when you are using a prepared statement (cfqueryparam).  The workarounds I can recommend are 1. Disable "maintain client connections" in the cf admin - less desirable 2. Change the query ever so slightly.  I usually add an extra where clause : "AND 1=1" which has no performance penalty but makes the prepared statement recompile ----- Excess quoted text cut - see Original Post for more -----

Adam Howitt wrote: > This bug usually happens when you change something on the database > server when you are using a prepared statement (cfqueryparam).  The > workarounds I can recommend are > 1. Disable "maintain client connections" in the cf admin - less desirable > 2. Change the query ever so slightly.  I usually add an extra where > clause : "AND 1=1" which has no performance penalty but makes the > prepared statement recompile That sounds like the answer!  The problem didn't occur until I used query analyzer to drop some columns from the table. Rick

Something that I like about the 1=1...  It's pretty handy for getting rid of those "WHERE blah blah" "AND blah blah" things that sometimes happen when you have conditional WHERE stuff.  Just put a WHERE 1=1, and, the rest are ANDs. Just a little tidbit that makes it easier when you do have to do that stuff. If that even makes any sense. Sheesh. =P Must be a lazy day today. :den ----- Excess quoted text cut - see Original Post for more -----

Rick, This is the easiest problem in the world to fix.... Don't use SELECT *. Name the column in the select statement. Not only does this cut out your problem, it also: 1. Makes the select statement more clear to anyone else reading. 2. Increases speed of the query. 3. Decreases the amount (most likely) of info that SQL has to transfer to the CF memory space. Cheers, ben ...................... Ben Nadel Web Developer Nylon Technology 350 7th Ave. Suite 1005 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com Sanders: Lightspeed too slow? Helmet: Yes we'll have to go right to ludacris speed. what the heck is this? 19 >= 19 The error occurred in E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20 18 :     A.*, B.name as PRMLNAME 19 :   FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email 20 :   where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> 21 : </CFQUERY> 22 : <cfoutput query="data"> It's worth noting that there are 18 columns in the table.  I dropped another column and ran the query again and I got "18 >= 18" (now there are only 17 columns) here's the full query:    SELECT      A.*, B.name as PRMLNAME    FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email    where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> Rick

And to balance out the argument some, writing all the columns takes longer to write! Go on, fire away, I'm in my bunker and the flaps are down :OD Adrian Rick, This is the easiest problem in the world to fix.... Don't use SELECT *. Name the column in the select statement. Not only does this cut out your problem, it also: 1. Makes the select statement more clear to anyone else reading. 2. Increases speed of the query. 3. Decreases the amount (most likely) of info that SQL has to transfer to the CF memory space. Cheers, ben ....................... Ben Nadel www.nylontechnology.com Sanders: Lightspeed too slow? Helmet: Yes we'll have to go right to ludacris speed. what the heck is this? 19 >= 19 The error occurred in E:\Inetpub\wwwroot\tools\email_responses\respond.cfm: line 20 18 :     A.*, B.name as PRMLNAME 19 :   FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email 20 :   where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> 21 : </CFQUERY> 22 : <cfoutput query="data"> It's worth noting that there are 18 columns in the table.  I dropped another column and ran the query again and I got "18 >= 18" (now there are only 17 columns) here's the full query:    SELECT      A.*, B.name as PRMLNAME    FROM dbo.results A left join dbo.results2 b on a.fromAddress=b.email    where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> Rick

> And to balance out the argument some, writing all the columns takes longer > to write! > > Go on, fire away, I'm in my bunker and the flaps are down :OD > > Adrian Incoming! (Heh. Not often is it the other guys yelling that) Why wouldn't you write out columns? Isn't all your SQL generated? O.o Surely you're not actually hard-coding names... right?  You do have just one place where if you change a field name, it updates the forms, the SQL... basically everything where that name was referenced? Then you can force your DB person use your CF code to manipulate the DB. They love that. Really. :Deni ----- Excess quoted text cut - see Original Post for more -----

Nope, I use a passive code generator. Less risky :O) "Incoming! (Heh. Not often is it the other guys yelling that)" No comment on the friendly fire! :O. > > And to balance out the argument some, writing all the columns takes longer > to write! > > Go on, fire away, I'm in my bunker and the flaps are down :OD > > Adrian Incoming! (Heh. Not often is it the other guys yelling that) Why wouldn't you write out columns? Isn't all your SQL generated? O.o Surely you're not actually hard-coding names... right?  You do have just one place where if you change a field name, it updates the forms, the SQL... basically everything where that name was referenced? Then you can force your DB person use your CF code to manipulate the DB. They love that. Really. :Deni ----- Excess quoted text cut - see Original Post for more -----

> Nope, I use a passive code generator. Less risky :O) Passive? Generation is generation, man! Heh. What do you mean? "Incoming! (Heh. Not often is it the other guys yelling that)" > > No comment on the friendly fire! :O. Oooo! Good point.  Not as funny an idea tho, see... "look out bad guys, a grenade is rolling your way!" "You may want to duck, as I'm shooting at you!" A callback to a bygone age. Like vaudville.  Damn Sun Tzu... [ =  Sorry. Old war stuff on PBS, don't'cha know... ----- Excess quoted text cut - see Original Post for more -----

I'm not sure if the terms 'passive' and 'active' are correct, I think I heard it somewhere, but... ... passive will generate code that you take and use somewhere, active will mess with the actual code. If anyone knows different, pipe up! Adrian > > Nope, I use a passive code generator. Less risky :O) Passive? Generation is generation, man! Heh. What do you mean? "Incoming! (Heh. Not often is it the other guys yelling that)" > > No comment on the friendly fire! :O. Oooo! Good point.  Not as funny an idea tho, see... "look out bad guys, a grenade is rolling your way!" "You may want to duck, as I'm shooting at you!" A callback to a bygone age. Like vaudville.  Damn Sun Tzu... [ =  Sorry. Old war stuff on PBS, don't'cha know... ----- Excess quoted text cut - see Original Post for more -----

You may also eliminate this problem by changing the "max pooled statements" value in your DSN to 0. I'm not certain of that, but I know that the pooled statements are a form of SQL syntax caching and that for most applications they're not very effective in improving performance, so unlike disabling the "maintain client connections" feature there's not much to lose. I have some recollection of having the same sort of issues myself (change db structure outside cf, get error from unchanged and still valid select statement in cf) and being able to eliminate them that way. ----- Excess quoted text cut - see Original Post for more -----