Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    GROUP 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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Actually we can't link to the Access db. Everything has to be stored in the SQL database.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  6. #6
    Join Date
    Jan 2004
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •