Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Unanswered: SQL Server 2000 SP 4 issue with query

    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

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I assume this is in a sproc? if so, it looks like it could be a parameter sniffing issue, see below for how to fix it:

    http://blogs.msdn.com/khen1234/archi...02/424228.aspx

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    btw, this:

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

    can be more clearly expressed as:

    coalesce(Contacts.Job_Title + ' ', '')

    it would make your query easier to read.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    wait a sec, I just noticed you are using a cursor and the while loop in your cursor will never terminate, because you never fetch next while inside the loop (it's commented out).

    so that's the reason it runs forever.

    I should have read your initial post more carefully! Did you comment that out just to trick me?

    also I can't resist saying that this is a very strange way to return data. You are using a cursor just to loop through a record set and build a string out of that record set. There are much more efficient ways to do that. see for example the blind one's article here:

    http://sqlblindman.googlepages.com/c...limitedstrings

    it's about creating a comma separated string from a record set, what you are doing is exactly the same, except you want it crlf separated instead of comma separated. do that and you don't need a cursor.

  5. #5
    Join Date
    Feb 2007
    Posts
    3

    progress...

    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 default (no type specified) cursors created as 'static forward-only', I couldn't find a way to do this using sp_configure so any suggestions would be appreciated.

  6. #6
    Join Date
    Feb 2007
    Posts
    3

    comment

    and jezemine, sorry about that rogue comment...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •