MDAC 2.8

Hi,
Recently I run Windows Update on my Windows 2000 Server with MS SQL Server
on the same machine. It update the MDAC to 2.8 version.
It broke my store procedure which work fine with MDAC 2.6 sp2.
With MDAC 2.6 it only take 1 minutes to run; however, with MDAC 2.8 it take
all day and lock up the database. Not only that, the CPU utilization is 100%
the whole day.
Please help ! Thank you in advance.
Here is the sql statement:
Create Procedure dbo.spSbcAudit_AddressMismatch
As
Select
A.ListedPhone As SbcPhone,
A.ListingInstructions As SbcListingInstructions,
A.OmitCode As SbcOmitCode,
A.LastName As SbcLastName,
A.FirstName As SbcFirstName,
A.TitleOfAddress As SbcTitleOfAddress,
A.TitleOfLineage As SbcTitleOfLineage,
A.Number As SbcNumber,
A.Directional As SbcDirectional,
A.StreetName As SbcStreetName,
A.Locality As SbcCity,
A.ListType As SbcListType,
A.RecordType As SbcRecordType,
A.InputFileName As SbcInputFileName,
Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day(A.DateFromClec)))+'/'+Lt
rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
-- Icoms fields
B.AccountNumber As IcomsAccountNumber,
B.InstallDate As IcomsInstallDate,
B.TelephoneNumber As IcomsPhone,
B.TelephoneStatus As IcomsListingStatus,
B.RecordType As IcomsRecordType,
B.ListType As IcomsListType,
B.ListingIdCode As IcomsListingIdCode,
B.ListingInstructions As IcomsListingInstructions,
B.LastName As IcomsLastName,
B.FirstName As IcomsFirstName,
B.TitleOfLineage As IcomsTitleOfLineage,
B.SpecialFilingName As IcomsSpecialFilingName,
B.Title As IcomsTitle,
B.Apartment As IcomsApartmentNumber,
B.Directional As IcomsDirectional,
B.AddressNumber As IcomsNumber,
B.StreetName As IcomsStreetName,
B.City As IcomsCity,
Substring(B.ZipCode,1,5) As IcomsZipCode,
B.ClassOfService As IcomsClassOfService
Into dbo.tblSbcAuditResult_AddressMismatch
From dbo.tblPde7661 A
Inner Join dbo.tblIcomsData B On A.ListedPhone = B.TelephoneNumber
Where
A.ListType = B.ListType And
(Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim(B.StreetName))))
And
B.TelephoneStatus In (
Select vchListingStatus
From dbo.tblAuditListingStatus
Where bitIsInclude = 1) And
B.RateCenterId <> 9 And
-- caption only, Icoms does not support this
A.ListedPhone Not In (Select ListedPhone From dbo.tblPde7661 Where
Reference='Customer Service') And
-- Install date must be before the SBC download date
B.InstallDate <= (select intInstallDate from tblAuditInstallDate)
Order By
B.ClassOfService,
B.ListType,
A.ListingInstructions,
A.OmitCode,
B.ListingInstructionswell, latest mdac 2.8 includes security fixes and perheps
some not needed enhancements. You can go to Microsoft site
and search for mdac 2.6 service pack 2 and download it
from there. Or you can use following link to download it:
http://www.microsoft.com/downloads/details.aspx?
familyid=8e5f816c-4918-4250-b8bd-
3794582c9089&languageid=f49e8428-7071-4979-8a67-
3cffcb0c2524&displaylang=en
hth.
>--Original Message--
>Hi,
>Recently I run Windows Update on my Windows 2000 Server
with MS SQL Server
>on the same machine. It update the MDAC to 2.8 version.
>It broke my store procedure which work fine with MDAC 2.6
sp2.
>With MDAC 2.6 it only take 1 minutes to run; however,
with MDAC 2.8 it take
>all day and lock up the database. Not only that, the CPU
utilization is 100%
>the whole day.
>Please help ! Thank you in advance.
>Here is the sql statement:
>Create Procedure dbo.spSbcAudit_AddressMismatch
>As
>Select
> A.ListedPhone As SbcPhone,
> A.ListingInstructions As SbcListingInstructions,
> A.OmitCode As SbcOmitCode,
> A.LastName As SbcLastName,
> A.FirstName As SbcFirstName,
> A.TitleOfAddress As SbcTitleOfAddress,
> A.TitleOfLineage As SbcTitleOfLineage,
> A.Number As SbcNumber,
> A.Directional As SbcDirectional,
> A.StreetName As SbcStreetName,
> A.Locality As SbcCity,
> A.ListType As SbcListType,
> A.RecordType As SbcRecordType,
> A.InputFileName As SbcInputFileName,
>Ltrim(Str(Month(A.DateFromClec)))+'/'+Ltrim(Str(Day
(A.DateFromClec)))+'/'+Lt
>rim(Str(Year(A.DateFromClec))) As SbcDateFromClec,
> -- Icoms fields
> B.AccountNumber As IcomsAccountNumber,
> B.InstallDate As IcomsInstallDate,
> B.TelephoneNumber As IcomsPhone,
> B.TelephoneStatus As IcomsListingStatus,
> B.RecordType As IcomsRecordType,
> B.ListType As IcomsListType,
> B.ListingIdCode As IcomsListingIdCode,
> B.ListingInstructions As IcomsListingInstructions,
> B.LastName As IcomsLastName,
> B.FirstName As IcomsFirstName,
> B.TitleOfLineage As IcomsTitleOfLineage,
> B.SpecialFilingName As IcomsSpecialFilingName,
> B.Title As IcomsTitle,
> B.Apartment As IcomsApartmentNumber,
> B.Directional As IcomsDirectional,
> B.AddressNumber As IcomsNumber,
> B.StreetName As IcomsStreetName,
> B.City As IcomsCity,
> Substring(B.ZipCode,1,5) As IcomsZipCode,
> B.ClassOfService As IcomsClassOfService
>Into dbo.tblSbcAuditResult_AddressMismatch
>From dbo.tblPde7661 A
>Inner Join dbo.tblIcomsData B On A.ListedPhone =B.TelephoneNumber
>Where
> A.ListType = B.ListType And
> (Upper(Rtrim(Ltrim(A.StreetName))) <> Upper(Rtrim(Ltrim
(B.StreetName))))
>And
> B.TelephoneStatus In (
> Select vchListingStatus
> From dbo.tblAuditListingStatus
> Where bitIsInclude = 1) And
> B.RateCenterId <> 9 And
> -- caption only, Icoms does not support this
> A.ListedPhone Not In (Select ListedPhone From
dbo.tblPde7661 Where
>Reference='Customer Service') And
> -- Install date must be before the SBC download date
> B.InstallDate <= (select intInstallDate from
tblAuditInstallDate)
>Order By
> B.ClassOfService,
> B.ListType,
> A.ListingInstructions,
> A.OmitCode,
> B.ListingInstructions
>
>.
>