January 21, 2010 at 11:21 am
Greeting SSC members. I'm not sure this post is in the right forum... but here goes.
Let me start out by saying I am not a database guru. I am a ColdFusion developer that has been forced to take on the role of DBA. While I am enjoying the learning experience, I have run into an issue that I simply cannot resolve. I have been frequenting these forums and have learned a great deal. I'm hoping that someone here will have some insight into the issue. I understand normally one should post create/populate table scripts and so on, but the issue I have happens with varying data sets and different SELECT statements; in fact, completely different databases. The only commonality I can see is the error happens when there is a CTE and/or a PIVOT clause.
Ok, so I've been referring to the "issue/error" and have yet to provide it... so here you go:
Error Executing Database Query. Incorrect syntax near '('.
The query that generates the error is:
;WITHvs_data AS (
SELECTREPLACE( vsf.field_abbr,' ','' ) AS field_abbr, vso.option_name, vsd.regid
FROMvisitor_survey_fields vsf
INNER JOINvisitor_survey_options vso
ONvso.field_id = vsf.field_id
LEFT JOINvisitor_survey_data vsd
ONvsd.field_id = vsf.field_id
ANDvsd.option_id = vso.option_id
)
SELECTcrmh.rep_id,
v.firstName,
v.lastname,
crmh.action_date,
v.firstname,v.lastname,v.pheve,v.phcell,v.phday,v.email,v.salesperson,b.company,a.agentname,a.cellphone,crmh.action_date,v.adsource,v.adsourcelvl2,crmh.notes , vsd.PurchaseReason,vsd.ResidenceType,vsd.Range
FROMvisitor v
INNER JOIN CRM_history crmh
ON crmh.rep_id = v.regid
ANDcrmh.rep_type = 'Visitor'
LEFT JOIN CRM_contact_types crmct
ON crmh.contact_type_id = crmct.contact_type_id
LEFT JOIN users u
ON crmh.userid = u.salespersonid
LEFT JOIN contract c
ON crmh.unitid = c.unitID
LEFT JOIN lookup lk
ON crmh.status = lk.valueno1
ANDlk.='characteristics'
ANDlk.key2='contactstatus'
LEFT JOIN broker b
ON v.bid = b.bid
LEFT JOIN agent a
ON v.aid = a.aid
LEFT JOIN visitor sc
ONsc.regid = v.regid
ANDsc.from_siteConnect = 1
LEFT JOINvs_data
PIVOT (
MAX(option_name)
FOR field_abbr IN ( PurchaseReason,ResidenceType,Range )
) AS vsd
ONvsd.regid = v.regid
WHERE 1 = 1
ANDcrmh.action_date >= {ts '2010-01-04 00:00:00'}
ANDcrmh.action_date <= {ts '2010-01-10 23:59:59'}
The query string that is returned with the error is:
;WITHvs_data AS (
SELECTREPLACE( vsf.field_abbr,' ',' ) AS field_abbr, vso.option_name, vsd.regid
FROMvisitor_survey_fields vsf
INNER JOINvisitor_survey_options vso
ONvso.field_id = vsf.field_id
LEFT JOINvisitor_survey_data vsd
ONvsd.field_id = vsf.field_id
ANDvsd.option_id = vso.option_id
)
SELECTcrmh.rep_id,
v.firstName,
v.lastname,
crmh.action_date,
v.firstname,v.lastname,v.pheve,v.phcell,v.phday,v.email,v.salesperson,b.company,a.agentname,a.cellphone,crmh.action_date,v.adsource,v.adsourcelvl2,crmh.notes , vsd.PurchaseReason,vsd.ResidenceType,vsd.Range
FROMvisitor v
INNER JOIN CRM_history crmh
ON crmh.rep_id = v.regid
ANDcrmh.rep_type = 'Visitor'
LEFT JOIN CRM_contact_types crmct
ON crmh.contact_type_id = crmct.contact_type_id
LEFT JOIN users u
ON crmh.userid = u.salespersonid
LEFT JOIN contract c
ON crmh.unitid = c.unitID
LEFT JOIN lookup lk
ON crmh.status = lk.valueno1
ANDlk.='characteristics'
ANDlk.key2='contactstatus'
LEFT JOIN broker b
ON v.bid = b.bid
LEFT JOIN agent a
ON v.aid = a.aid
LEFT JOIN visitor sc
ONsc.regid = v.regid
ANDsc.from_siteConnect = 1
LEFT JOINvs_data
PIVOT (
MAX(option_name)
FOR field_abbr IN ( PurchaseReason,ResidenceType,Range )
) AS vsd
ONvsd.regid = v.regid
WHERE 1 = 1
ANDcrmh.action_date >= {ts '2010-01-04 00:00:00'}
ANDcrmh.action_date <= {ts '2010-01-10 23:59:59'}
In this query, the second set of single quotes in the REPLACE function has somehow lost one of the quotes.
The error happens intermittently but is thrown about once a week. We have separate databases for each client though each of the schemas are the same. The error is not isolated to a particular database or data set. In fact, there is another query in the system with a similar make up (i.e. CTE and PIVOT clause) that occasionally generates the same error.
Below are our servers configuration:
Database
> Windows 2003 Server
> SQL Server 2005 SP3
Web
> Windows 2003 Server
> ColdFusion 8 Enterprise
> jTDS 1.4.2 JDBC driver
I am quite desperate at this point so any suggestions will be much appreciated. Thanks in advance!
January 21, 2010 at 3:17 pm
Ok, I'm guessing a little, but do you by chance have this query wrapped inside an EXEC or a sp_executesql? The reason I ask is that when you are in a string in SQL, the way you escape a single quote is by putting another next to it... which is exactly what you are getting. Take a look at this query:select 'two single quotes'' become one inside a string. If you want two single quotes within the string, you need '''' four.'
So what you are seeing would make perfect sense if the query was part of a string that was later executed.
Chad
Edit:
In fact, I'll bet if you look at the source for this query, all the other single quotes are doubled up and this one just needs to be double-doubled up. (I checked, double-doubled is in the dictionary, so don't ask :-D)
January 21, 2010 at 3:38 pm
Thanks for the reply Chad. Unfortunately, the query is executed from within application code. No EXEC or Stored Procedure. Also, if it were a case of not escaping a single quote, the error would occur every time.
January 21, 2010 at 4:13 pm
If you execute the query that was returned (without fixing the quote issue) do you get the same incorrect syntax error? What if you put the quote back in, do you get it then? Is any part of the query created dynamically inside the application code?
When I get the "incorrect syntax" error, the query returned is only from the point where the syntax was wrong and about the next 100 characters or so. Is there by chance an open paren right before the WITH in the source for the query?
Chad
January 22, 2010 at 8:01 am
If I take the query string that is returned with the error and run it in SSMS, the same error is generated. Simply adding the missing single quote fixes the issue.
There are some dynamic parts to the query. But the query string returned with the error has all the dynamic parts resolved to their static values. However, there is no dynamic code between the pair of single quotes.
January 22, 2010 at 9:26 am
I tried to write a query that was similar to yours, but would work on my system: ;WITHvs_data AS (
SELECTREPLACE( name,' ',') AS field_abbr
, id
, xtype
FROMsysobjects
)
SELECT* from vs_data
PIVOT (
MAX(id)
FOR xtype IN ( ,)
) AS vsd
But I get a different result. I get two errors, first "Unclosed quotation mark..." then the "Incorrect syntax near ') AS field_abbr...", but it is near an end paren not near a begin paran like your error, and the query returned after the error is only from the single quote on - I don't get the SELECT REPLACE part of the query returned as part of the error.
So I played with it a little and if I run the query in an EXEC as a string (i.e. EXEC 'sqlstring'), I do get the whole query returned in the error (at least the first 100 chars or so). If I take out the bad single quote and just leave that part blank, it returns the exact error you got (but with a close paren instead of an open paren), and doesn't return the query string as part of the error at all.
I keep thinking it has something to do with that single quote not being escaped properly - it seems to fit so perfectly, but I'm not sure how/why it's being done. Does it happen with any regularity, or can you force the error to happen from the client side? If so, maybe it would worthwhile to run Profiler to see exactly what is being passed into the server. It might be that ColdFusion is taking the query from your code and turning it into a prepared procedure or doing something else where the double single quote gets converted. For example, I know some .net code will wrap a query and pass it to the database in a sp_executesql call, so maybe ColdFusion is doing something similar. The last version of ColdFusion I used was 5, so I'm a little rusty there and not sure how more current versions handle queries.
Chad
January 22, 2010 at 7:45 pm
I tend to agree that it is some sort of escaping issue. However, the error is not consistent and I have yet to duplicate it myself.
I remember reading somewhere that ColdFusion does indeed use spexecutesql to pass the query string to the database.
January 23, 2010 at 11:41 am
I don't want to blame ColdFusion, especially since it kept me employed for a number of years, but it looks like there are some cases where it doesn't escape a double single quote quite as expected:
CFQuery not escaping single quotes
So... I was thinking, what could you do that would work regardless of whether the problem was in the code, or CF, or the driver, or SQL Server? What would you think of changing from
REPLACE( vsf.field_abbr,' ','')
to
REPLACE( vsf.field_abbr,' ',LTRIM(' '))
?
The end result is the same, but you don't end up puting a double single quote in the query. I'm thinking that should get past the problem whereever it is. If you don't want to put the space in, you could use REPLACE( vsf.field_abbr, CHAR(32),LTRIM(char(32))) and get rid of the single quotes altogether.
Yeah, it looks funny, but I think it will work.
Chad
January 25, 2010 at 10:37 am
Thanks for the suggestion. I will give it a try.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply