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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SQL Server 2000 SP 4 issue with query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zodiac7
Starting Member

3 Posts

Posted - 02/01/2007 :  13:12:32  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/01/2007 :  13:18:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
Open a support case with Microsoft. If it is a bug in SP4, the case is free.

Tara Kizer
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/02/2007 :  08:39:13  Show Profile  Reply with Quote
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
Go to Top of Page

zodiac7
Starting Member

3 Posts

Posted - 02/05/2007 :  06:29:35  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/05/2007 :  06:35:05  Show Profile  Reply with Quote
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
Go to Top of Page

zodiac7
Starting Member

3 Posts

Posted - 02/05/2007 :  06:38:54  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/05/2007 :  06:41:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000