SQL 7.0 to SQL 2000 Post Migration Prob. in Access queries

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

Other Forums

MS Access

SQL 7.0 to SQL 2000 Post Migration Prob. in Access queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-21 : 08:32:26

writes "Hi,
Recently we migrated objects in SQL Server 7.0 to SQL Server 2000. We use SQL tables in 'MS Access 2000' queries by linking those tables to the Access MDB file. After the migration, a query accessing this SQL table is running for a long time and it never completed, so i had to cancel the query half way always.

This is weird and I have no idea why this is happening after migrating to SQL Server 2000. The same query was taking few minutes to complete, when it was using an object in SQL Server 7.0.

FYI... The query uses date range as parameter. For a month's date range the same query completes in 10-20 mins whereas the problem is with Quarter range of data. For QTR range of data there is no progress in the query.

The query is,

SELECT tblAllPOData.PONumber, IIf([tblAllPOData].[DCID]="",Null,[tblAllPOData].[DCID]) AS DCID, IIf([tblAllPOData].[DeliveryID]="",Null,[tblAllPOData].[DeliveryID]) AS DeliveryID, tblAllPOData.DeptID, tblAllPOData.SeasonCode, tblAllPOData.VendorID, tblAllPOData.AgentID, tblAllPOData.CountryOfOrigin, tblAllPOData.ShippingMode, tblAllPOData.DNSBDate, tblAllPOData.ShipCancelDate, tblAllPOData.ContractualShipCancelDate, tblAllPOData.InDCDate AS PlannedInDcDate, tblAllPOData.OrderType, tblAllPOData.EstimatedLandCost AS EstimatedLandCost, tblAllPOData.FirstCostUSD AS FirstCostUSD, IIf([POCreateDate]<4/29/2001 And [USDRetailPrice] Is Not Null Or [POCreateDate] Is Null And [USDRetailPrice] Is Not Null,[USDRetailPrice],[ActualTicketPrice]) AS Price, [Price]*[tblAllPOData].[OrderQuantity] AS ExtRetailPrice, tblAllPOData.OrderQuantity AS OrderQuantity, tblAllPOData.CorpID, tblAllPOData.CompanyID, "Focus_Current" AS Period, #2/2/2003# AS DateFrom, #5/3/2003# AS DateTo INTO tbl001_InitialDataPopulation
FROM (tblAllPOData LEFT JOIN tblPODueDates ON tblAllPOData.PONumber = tblPODueDates.PONumber) LEFT JOIN tmpMerchRetailPrice ON (tblAllPOData.CurrencyType = tmpMerchRetailPrice.RPCurrency) AND (tblAllPOData.StyleCode = tmpMerchRetailPrice.StyleCode) AND (tblAllPOData.CompanyID = tmpMerchRetailPrice.CoID) AND (tblAllPOData.CorpID = tmpMerchRetailPrice.CorpID)
WHERE (((tblAllPOData.AgentID)<>"000301222") AND ((tblAllPOData.InDCDate) Between [StartDate] And [EndDate]) AND ((tblAllPOData.OrderQuantity)>0) AND ((tblAllPOData.CorpID)<>"81"));

Can you help me to figure out the cause for this problem?

Thanks,
Jafer"

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-21 : 10:49:52

I had similar problem months ago, and couldn't resolve. The only different is that we were not from SQL7 but from Sybase.
I ended up with replacing these select queries with stored procedures and got rid of link-tables/DSN all together.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources