Procedure in SQL 2000 and SQL 2005

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

General SQL Server Forums

New to SQL Server Programming

Procedure in SQL 2000 and SQL 2005

Author

Topic

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-11 : 15:10:33

hi gr8 people,

I had a one procedure in MS SQL 2000 which used to update the n number of records.
Recently we have upgraded App Server version and in DB we have upgraded to MS SQL 2005- SP1. Now same procedure is present in MS SQL 2005. And users are compaining that they are finding very less count of updated transaction.
For eg
Transaction count in MS SQL 2000 - 1400
Transaction count in MS SQL 2005 SP1 - 800 [Using Compatibility 80]

What will be the reason ? we have checked the procedure and both are same.

Please help me i wanna to come out this situation
Thanks in Advance

Dina Satam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 15:14:04

There is an error in line 4 of the query.
I guess.

It would be much easier of we could have a glimpse of the query you think is faulty.

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-11 : 15:20:03

Hey Peso,

I will do one thing I will get that query from office tommorrow and then suggest me if that query is to be tuned as per MS SQL 2005- SP1.
Also one more thing i want to notice as SP2 has fixes lot more issue which are SP1. like Update query, Bulk Insert and many more

Is that will be the reason ? and which will be resolve after applying SP2 patch

Dina Satam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 15:22:18

One more question.
Is there a specific reason you have compatibility level at 8 (sql server 2000)?

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-11 : 15:25:11

Peso,

We kept compatibility level 80 to run our all procedures which are present in SQL 2000.
As Our DBA team said keepin 80 in SQL 2005 will be perfomance issue.

Dina Satam

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 15:56:22

"As Our DBA team said keepin 80 in SQL 2005 will be perfomance issue."

I have heard that Compatibility-mode-80 on a SQL 2005 server can reduce performance dramatically.

But to set Compatibility-mode-90 you should run a complete set of regression tests of course

Kristen

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-12 : 14:30:10

Yes Kristen,
But i not finding the way to come out this problem. Lets see vendor is doing ?

Dina Satam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources