Access SQL vs SQL 2005

[kelbantaemi]

We recently migrated from an Access database to SQL 2005. When comparing the syntax I see queries are now different.

Access Example <

SELECT `Customer`.`Customer`, `Invoice_Header`.`Open_Invoice_Amt`, `Invoice_Header`.`Due_Date`, `Invoice_Header`.`Document_Date`, `Customer`.`Name`, `Customer`.`Sales_Code`, `Address`.`Status`

FROM (`Customer` `Customer` LEFT OUTER JOIN `Invoice_Header` `Invoice_Header` ON `Customer`.`Customer`=`Invoice_Header`.`Customer`) LEFT OUTER JOIN `Address` `Address` ON `Invoice_Header`.`Customer`=`Address`.`Customer`

WHERE `Address`.`Status`='Active' AND NOT (`Customer`.`Sales_Code`='Edmar' OR `Customer`.`Sales_Code`='EMI')

ORDER BY `Customer`.`Sales_Code`, `Customer`.`Customer`

SQL 2005 Example <

SELECT "Customer"."Customer", "Invoice_Header"."Open_Invoice_Amt", "Invoice_Header"."Due_Date", "Invoice_Header"."Document_Date", "Customer"."Name", "Customer"."Sales_Code", "Address"."Status"

FROM ("PRODUCTION"."dbo"."Customer" "Customer" LEFT OUTER JOIN "PRODUCTION"."dbo"."Invoice_Header" "Invoice_Header" ON "Customer"."Customer"="Invoice_Header"."Customer") LEFT OUTER JOIN "PRODUCTION"."dbo"."Address" "Address" ON "Invoice_Header"."Customer"="Address"."Customer"

WHERE "Address"."Status"='Active' AND NOT ("Customer"."Sales_Code"='Edmar' OR "Customer"."Sales_Code"='EMI')

ORDER BY "Customer"."Sales_Code", "Customer"."Customer"

All my solutions in FM scripts are import records requests. I see a problem when using calculated Dynamic WHERE requests. When I recode a FM query Example < "SELECT `Material_Location`.`Material`, `Material_Location`.`Location_ID`, `Material_Location`.`On_Hand_Qty`

FROM `Material_Location`

WHERE `Material_Location`.`Material` =" & g_PartVariable >

FM throws up an error telling me it can not find field…in this appended recoded example as < "SELECT “Material_Location”.”Material”,> can not find Material_Location.

I have to say from the get-go I struggled with syntax issues from the beginning even when my queries were to Access tables. In fact My work around has been to design my table requirements in a crystal 9 report and copy and paste the SQL query from crystal to FM and make minor adjustments.

I believe my problem lies with the SQL 2005 use of quotes. I am afraid this is going to be a daunting task to resurrect my FM solutions. Any help will be appreciated

[kelbantaemi]

I think I figured out one way to do this, however not sure if there is a better way. All my existing Access queries which now need to be updated to read SQL 2005 were written to a calculated concatenated field containing my ""&" from within the import record script.

So now I added a global text field were I hand recode my SQL 2005 query substituting all the (') with (")...which is going to be a pain, then update the import calculated field with the concatenated reference to g_SQL & variable_field. Oh, the variable field is also a calculated field to insert any required syntax to handle text or number formatting.

So is there a better way or is this it?