Access to SQL Migration Errors

[JSeverson]

Hi,
I'm migrating data for a classic ASP site from an MS Access database to a MS SQL database. However, I am now encountering errors with some of the code. I was able to resolve some of the errors by removing the cursor types that the former Web developer was using. I'm still receiving the following error on various pages:

Microsoft OLE DB Provider for SQL Servererror '80040e14'
Incorrect syntax near the keyword 'union'.
/managers/report/original_vendor.asp, line 109


The SQL statement that is executed on line 109 is the following:

select a.accountno,a.schoolid,a.billingperiod,b.date,b.am ount,b.number,b.remark, 0 as adjust,0 as adjamount,c.name from expenses a , expensedtls b,schools c where a.expenseid = b.expenseid and a.schoolid=c.schoolid and a.vendorid=22 and b.date >= '8/1/2008' and b.date <= '8/15/2008' order by c.name,b.date desc,a.expenseid union select '',(select distinct c.schoolid from expenses a , expensedtls b,schools c where a.expenseid = b.expenseid and a.schoolid=c.schoolid and a.vendorid=22 and b.dtlid = adjustments.dtlid),0,date1,0,(select number from expensedtls where dtlid = adjustments.dtlid),remark,adjust,amount as adjamount,(select distinct c.name from expenses a , expensedtls b,schools c where a.expenseid = b.expenseid and a.schoolid=c.schoolid and a.vendorid=22 and b.dtlid = adjustments.dtlid) from adjustments where date1 >= '8/1/2008' and date1 <= '8/15/2008' and dtlid in (select distinct b.dtlid from expenses a , expensedtls b,schools c where a.expenseid = b.expenseid and a.schoolid=c.schoolid and a.vendorid=22) order by name,date

I'm not very familiar with unions so I'm not sure how to fix this error. Or is it an issue with a reserved keyword being used? Could somebody take a look?

Thanks,
Jeff

[pablowerk]

It looks like it's a sql error. It's possible that there is a type near the ~ a.expenseid union select '',( ~ section of your sql statement. If you have access to Enterprise manager, just copy your sql statement into a new querry window. The program will auto format your statement and make it easier to read and figure out where your error is.

hope that helps

[JSeverson]

Thanks but the query window wasn't much help. I was actually able to resolve the issue after experimenting a little bit. Access accepted the original statement which contained an Order By clause before the Union. But, MS SQL didn't like that clause. Once I removed it, everything seemed to start working correctly.

[apalmer123@msn.com]

You can only have one ORDER BY CLAUSE after all the UNION statements, not one in each sub SELECT clause. THe column you are sorting on has to be included in each select statement -- you can use AS to define the name of the selected column so they are all the same.

I also see a weird syntax error in your third line folowing the word union:

union select '',(select distinct

I think the quote and comma are also causing you problems.

HTH, Ann

[Christiaan]

SQL has some preserved keywords . (Both MS and the PHP variety)
If you use these keywords in your tables and/or fields, you do get errors when accessing the database.