SQL Server 2000 SP 4 issue with query

Question

Post reply

SQL Server 2000 SP 4 issue with query

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

your compiled plan is "wrong". Try use with recomplie or parameter sniffing to bypass the issue.

Cheers,

* Noel

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

You need to rewrite the query to be efficient. You can do that whole thing without a cursor:

Declare

@MatterID bigint

Declare

@EntityID int

Declare

@ReturnDetails int

Declare

@RETVAL VarChar(8000)

Set

@MatterID = 5875

Set

@EntityID = 1

Set

@ReturnDetails = 0

Select

@RETVAL = IsNull(@RETVAL + ',', '') + IsNull(C.Job_Title, '') + IsNull(C.First_Name, '') + IsNull(C.Middle_Name, '') + IsNull(C.Last_Name, '')

INNER

JOIN Contacts As C On L.Contact_ID = C.ContactID

where

L.Matter_ID = @MatterID

AND

L.Entity_ID = @EntityID

Order

By C.ContactID

Select

@RETVAL

I replaced your case statements with IsNull() and aliased your tables to make the query easier to read.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

Heh... well done... I'm thinking that if SP4 is so tough on cursors, then I like it a lot

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Zodiac Seven Newbie Points: 6 More actions · Answer 4

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.

I wish rewriting all the sps was an option but they're part of an old legacy system and I don't want to mess around with it too much.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Afraid not...

You can search syscomments to find all cursors and then update 'em all :

SELECT OBJECT_NAME(id) AS ObjName FROM dbo.SysComments WHERE Text like '%CURSOR%' ORDER BY ObjName