help with migration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

help with migration

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2004-10-19 : 18:05:24

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 19:02:40

What's the error?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2004-10-19 : 19:05:16

I'm not exactly sure. He said his passthru lookup is failing. I think we figured it out. in sql 7.0, a false is -1 and true is 1 (I think) and in sql 2000 a false is zero and true is 1. I just didn't know if there was some list of differences that I could reference .. I couldn't find one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources