SQL Server 2000 SP 4 issue with query

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

Transact-SQL (2000)

SQL Server 2000 SP 4 issue with query

Author

Topic

zodiac7
Starting Member

3 Posts

Posted - 2007-02-01 : 13:12:32

This query executes (instantly) in SQL Server 2000 without SP4. With SP 4, it hangs - goes over 5 mins and does nothing. If we make the following change to the query: (replace the variables with actual values)
...
where Links.Matter_ID = 5872

AND Links.Entity_ID = 1

Order By Contacts.ContactID
...
It executes almost instantly in SP4 as well.
Looks like a SP4 issue, any ideas on how we could fix this?
t-sql follows:

Declare @MatterID bigint

Declare @EntityID int

Declare @ReturnDetails int

Declare @RETVAL VarChar(8000)

Declare @EntityDetails VarChar(1000)

Set @MatterID = 5875

Set @EntityID = 1

Set @ReturnDetails = 0

DECLARE Entity_Cursor CURSOR FOR

Select CASE WHEN Contacts.Job_Title IS Null Then '' Else Contacts.Job_Title + ' ' End + CASE WHEN Contacts.First_Name IS Null Then '' Else Contacts.First_Name + ' ' End + CASE WHEN Contacts.Middle_Name IS Null Then '' Else Contacts.Middle_Name + ' ' End

+ CASE WHEN Contacts.Last_Name IS Null Then '' Else Contacts.Last_Name End EntityDetails

From Links

INNER JOIN Contacts

On Links.Contact_ID = Contacts.ContactID

where Links.Matter_ID = @MatterID

AND Links.Entity_ID = @EntityID

Order By Contacts.ContactID

OPEN Entity_Cursor

FETCH NEXT FROM Entity_Cursor INTO @EntityDetails

Select @RETVAL = ''

WHILE @@FETCH_STATUS = 0

BEGIN

Print 'test'

/* If @ReturnDetails = 1

Select @RETVAL = @RETVAL + @EntityDetails + '
'

Else

Select @RETVAL = @RETVAL + @EntityDetails + ', '

FETCH NEXT FROM Entity_Cursor INTO @EntityDetails */

END

CLOSE Entity_Cursor

DEALLOCATE Entity_Cursor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-01 : 13:18:09

Open a support case with Microsoft. If it is a bug in SP4, the case is free.

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-02-02 : 08:39:13

There was an issue I recall about mismatched datatypes in JOINs etc. causing significantly slower query plans, and something about parameter sniffing too.

I *think* that is discussed in this thread (but its 3 pages long and I haven't re-read it to check carefully)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Concerns+about+SQL+2000+SP4

Kristen

zodiac7
Starting Member

3 Posts

Posted - 2007-02-05 : 06:29:35

ok, the original sql works fine if I change the cursor type to static forward-only

I just changed the line
DECLARE Entity_Cursor CURSOR
To
DECLARE Entity_Cursor static forward-only CURSOR

but now I have a bunch of sps which could all fail because they are like this, Is there a way to tell sql server to have all cursors created as 'static forward-only' by default, I couldn't find a way to do this using sp_configure so any suggestions would be appreciated.

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 06:35:05

Don't know about sp_configure style solutions, but you could probably use the SYSCOMMENTS table to find all the DECLARE CURSOR statements in your Sprocs.

(But if the source for your Sprocs is stored outside the database that would be better. Actually thinking about it Scripting the database would probably be better than using SYSCOMMENTS because if an SProc uses multiple pages in SYSCOMMENTS a DECLARE CURSOR statement could be split over 2 rows, and you'd miss wit with, say, a LIKE query; but SYSCOMMENTS would give you a quick heads-up on how many there are to fix!)

Kristen

zodiac7
Starting Member

3 Posts

Posted - 2007-02-05 : 06:38:54

when I say I want all cursors created as 'static forward-only' by default - I mean only the ones which have no 'type' specified so this way only the problemsome sps will be affected.

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 06:41:18

If it isn't too many I would suggest you find & fix them.

You'll be the best person to decide what "too many" is!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources