Post reply

SELECT TOP 1 cannot work in Partitioned View?

  • February 24, 2005 at 2:20 am

    #66508

    Assuming MotherCard is a distributed partitioned view, why is it that the following statements caused error?  However if TOP 1 is changed to TOP 1 PERCENT, it works?

    DECLARE @mothercard nvarchar(30)

    BEGIN TRANSACTION

    SELECT TOP 1 @mothercard = PIN FROM CallingCard.dbo.MotherCard (ROWLOCK, UPDLOCK, READPAST)

            WHERE (Status = 0) ORDER BY PIN ASC

    COMMIT

  • February 24, 2005 at 6:09 am

    #543885

    Nice lock-hints

    what's

    set @mothercared = (select top 1 pin from ...)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 24, 2005 at 7:44 am

    #543913

    2 questions:

    - why are you opening a transaction when you read?

    - what is the error message ?

     


    * Noel

  • February 28, 2005 at 1:03 am

    #544356

    Locking hints - (ROWLOCK, READPAST, UPDLOCK) - this is standard locking hints for "queue" processing. For example, how else would you do it if a process posts work items into a table for a few other processes to retrieve and process it???

    Why transaction? Because I need to lock the row I have selected to do an update (with an UPDATE statement which is not shown) I left out this update to only show the pertinent points. In fact the locking hints are also not necessary to create the error with SELECT TOP 1.

    What error? Immediately after COMMIT, the error says something like

    Error: Communication link failure (State:08S01, Native Code: 0)

    If instead of COMMIT, a ROLLBACK wouldn't have such problem.

    Any idea anyone? 🙂

  • February 28, 2005 at 7:42 pm

    #544525

    After experimenting further, Partitioned View implementation in SQL server seems outright buggy with very odd behavior.

    If I use varchar(30) instead of nvarchar(30) and if there isn't an ORDER BY clause behind the SELECT statement, then everything works! The varchar/nvarchar may be because I use varchar in the table whereas I have "accidentally" use nvarchar in the stored proc. This accident is perfectly legal under normal conditions. And also if an ORDER BY clause is used, the first time the query is executed, it works, the second time, the connection will break?!

    I just wonder why all this funny behavior cannot be found when I do a google search...

     

     

  • February 28, 2005 at 8:46 pm

    #544527

    BTW, anyone knows how to submit a bug report to Microsoft?

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

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