Post reply

Changes migrating from 7.0 to 2000

  • October 19, 2004 at 4:07 pm

    #163047

    We are migrating a DB from 7.0 to 2000 and the developer has several complicated queries which work from his access frontend on the 7.0 DB but not on the 2000. Is there anywhere that explains exactly what changes are made to a 7.0 Database when it is restored to a 2000 instance???

    Here is a copy of his sql statement that is bombing...

    Set db = CurrentDb()

    Set qdf = db.CreateQueryDef(SelEmpNum & "SiteSort")

    With qdf

    .Connect = "ODBC;DSN=SiteMaster;Description=SiteMaster" _

    & ";UID=passthru;PWD=passthru;DATABASE=sitemaster" _

    & ";Network=DBMSSOCN;Address=sqlvirtsrv,1433"

    .ReturnsRecords = True

    .SQL = "SELECT dbo.SiteMASTER.SiteID, ISNULL(dbo.SiteMASTER.NextiraOneID,'') + ' # ' + ISNULL(dbo.SiteMASTER.SiteName,'') AS SiteName " _

    & ", MAX(CASE dbo.SiteMASTERSystemTypes.Type WHEN 'PBX' THEN ISNULL(dbo.SiteMASTERProjects.SystemType,'') + ' ' + ISNULL(dbo.SiteMASTERProjects.ContractType,'') ELSE NULL END) AS PBX " _

    & ", MAX(CASE dbo.SiteMASTERSystemTypes.Type WHEN 'KEYsys' THEN ISNULL(dbo.SiteMASTERProjects.SystemType,'') + ' ' + ISNULL(dbo.SiteMASTERProjects.ContractType,'') ELSE NULL END) AS KEYsys " _

    & ", MAX(CASE dbo.SiteMASTERSystemTypes.Type WHEN 'BCM' THEN ISNULL(dbo.SiteMASTERProjects.SystemType,'') + ' ' + ISNULL(dbo.SiteMASTERProjects.ContractType,'') ELSE NULL END) AS BCM " _

    & ", MAX(CASE dbo.SiteMASTERSystemTypes.Type WHEN 'VM' THEN ISNULL(dbo.SiteMASTERProjects.SystemType,'') + ' ' + ISNULL(dbo.SiteMASTERProjects.ContractType,'') ELSE NULL END) AS VM " _

    & ", MAX(CASE dbo.SiteMASTERSystemTypes.Type WHEN 'Centrex' THEN ISNULL(dbo.SiteMASTERProjects.SystemType,'') + ' ' + ISNULL(dbo.SiteMASTERProjects.ContractType,'') ELSE NULL END) AS Centrex " _

    & "FROM (dbo.SiteMASTER LEFT JOIN dbo.SiteMASTERProjects ON dbo.SiteMASTER.SiteID = dbo.SiteMASTERProjects.SiteID) " _

    & "LEFT JOIN dbo.SiteMASTERSystemTypes ON dbo.SiteMASTERProjects.SystemType = dbo.SiteMASTERSystemTypes.SystemType " _

    & "WHERE ((dbo.SiteMaster.CustomerID = " & SelectedClient & ") And (dbo.SiteMaster.Active = -1)) " _

    & "GROUP BY dbo.SiteMASTER.SiteID, ISNULL(dbo.SiteMASTER.NextiraOneID,'') + ' # ' + ISNULL(dbo.SiteMASTER.SiteName,'') " _

    & "ORDER BY ISNULL(dbo.SiteMASTER.NextiraOneID,'') + ' # ' + ISNULL(dbo.SiteMASTER.SiteName,'')"

    End With

    SelectedSortDesc = "Sort by Number"

    SelectedSiteSort = "MenuMcr.SortNumber"

    SaveSortSettings

    DoCmd.Hourglass False

    End Function

  • October 20, 2004 at 3:48 am

    #527287

    .Connect = "ODBC;DSN=SiteMaster;Description=SiteMaster" _

    & ";UID=passthru;PWD=passthru;DATABASE=sitemaster" _

    & ";Network=DBMSSOCN;Address=sqlvirtsrv,1433"

    .ReturnsRecords = True

    Here you'ce specified a DSN (in teh registry I guess) which will be overridden by the "Address = param"

    Also, what is the error? Syntax? Connection?

    The migration? Same box? Different box? SQL2000 replaces SQL7 on same box or is it an instance with SQL7 being the default instance?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply