Highlighted

syntax error after server migration

New Here ,
Jul 08, 2015

Copy link to clipboard

Copied

Hi,

This is for a legacy site. I'm not the person who developed it and am new to Coldfusion. This is happening when doing a search query.

Cold is old but running on CF9. Previous db was MSSQL 5 but was converted to MSSQL 14 during this conversion.

Code

<cfquery name="registrylist" datasource="#DSN#">

  SELECT

  Registry.registryID

  ,Registry.categoryID

  ,A.textImage

  ,Registry.categoryOrder

  ,B.InvName

  ,B.retail_price

  ,C.brideLastName

  ,C.brideFirstName

  ,C.groomLastName

  ,C.groomFirstName

  ,Registry.ProductID

  ,Registry.quantity

  ,Registry.quantityGot

  ,Registry.inventoryType

  ,Registry.nonInvDesc

  ,Registry.nonInvPrice

  ,Registry.nonInvDesc

  ,event.eventDate

  ,EventType.eventName

  ,event.eventID

  ,C.RegistrantID

  ,B.Desc_Short

  ,B.img_large

  ,b.Personalize_Flag

  FROM Registry,event

  ,registryCategory A

  ,products B

  ,registrant C

  ,eventType

  WHERE Registry.categoryID*=A.regCategoryID

  and Registry.ProductID*=B.ProductID

  and event.registrantID*=C.RegistrantID

  and event.eventid = #eventid#

  and registry.eventid = event.eventid

  and Event.eventTypeCode = EventType.eventTypeCode

  ORDER BY A.sortOrder,Registry.categoryOrder,Registry.registryID

</cfquery>

Error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '*='.


Issue seems to be with the "*+" Removing just they "*"'s page will load but with no information from db. Removing jus the "=" or both "*+"'s returns similar error.


Please help.


Troy

Views

186

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

syntax error after server migration

New Here ,
Jul 08, 2015

Copy link to clipboard

Copied

Hi,

This is for a legacy site. I'm not the person who developed it and am new to Coldfusion. This is happening when doing a search query.

Cold is old but running on CF9. Previous db was MSSQL 5 but was converted to MSSQL 14 during this conversion.

Code

<cfquery name="registrylist" datasource="#DSN#">

  SELECT

  Registry.registryID

  ,Registry.categoryID

  ,A.textImage

  ,Registry.categoryOrder

  ,B.InvName

  ,B.retail_price

  ,C.brideLastName

  ,C.brideFirstName

  ,C.groomLastName

  ,C.groomFirstName

  ,Registry.ProductID

  ,Registry.quantity

  ,Registry.quantityGot

  ,Registry.inventoryType

  ,Registry.nonInvDesc

  ,Registry.nonInvPrice

  ,Registry.nonInvDesc

  ,event.eventDate

  ,EventType.eventName

  ,event.eventID

  ,C.RegistrantID

  ,B.Desc_Short

  ,B.img_large

  ,b.Personalize_Flag

  FROM Registry,event

  ,registryCategory A

  ,products B

  ,registrant C

  ,eventType

  WHERE Registry.categoryID*=A.regCategoryID

  and Registry.ProductID*=B.ProductID

  and event.registrantID*=C.RegistrantID

  and event.eventid = #eventid#

  and registry.eventid = event.eventid

  and Event.eventTypeCode = EventType.eventTypeCode

  ORDER BY A.sortOrder,Registry.categoryOrder,Registry.registryID

</cfquery>

Error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '*='.


Issue seems to be with the "*+" Removing just they "*"'s page will load but with no information from db. Removing jus the "=" or both "*+"'s returns similar error.


Please help.


Troy

Views

187

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 08, 2015 0
haxtbh LATEST
Advocate ,
Jul 08, 2015

Copy link to clipboard

Copied

wow! That is some old SQL!

*= is a LEFT OUTER JOIN

This operator was depreciated about 10 years ago though, with the old version of SQL you were using you wouldn't have noticed an issue but now using a newer version of SQL you will see these problems.

You will need to re write the SQL queries using the correct LEFT JOIN syntax.

As a side note, if you are using CF9, you may find problems with SQL 2014 as support for it has only become available for CF11

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 08, 2015 0