Large move from Access to SQL Server 200

Hi,

My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.

The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.

For example, the following code is taken from Access :

SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;

But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?

ThanksGROUP BY Description?

Another Classic Access example...

LOOK OUT! RUN AWAY TRAIN...ummmm developers...

Do you have a lot of nested Access queries as well?

Have you tried just linked the SQL Server tables?

Have you given any thought to a re-write?|||Actually we can't link to the Access db. Everything has to be stored in the SQL database.|||I meant the old access app, with no tables, linked to the sql server database...

are the table and column names still all the same?

If you link it, it should all run...

might be very slow though...

but at least you'll have all your reports...|||Without and more infomation, it looks like the Access upgrade thingy (whatever they call it now) has morphed soem of your text fields to TEXT or NTEXT. If you want to be able to join on these fields, they need to be CHAR, VARCHAR (or NCHAR, NVACHAR).

Your first step after running that thing is to go through the whole database, field by field, and see what has happened to your data in the translation.

Almost as easy to do it manually.

-bpd|||I ended up doing all of the queries as views and getting rid of the one text field, then on the final query, joined the text field, thus by-passing the Group By clause for that field. Thanks for all the help though.