SProc generates different results same data

David_0223 · June 12th, 2008, 06:29 PM

I hope this won't be another head smacker, but What could cause a stored procedure to generate different result sets for the same data when the procedure is run from the same machine in the same software (SQL Server Managment Studio Express) just moments apart. There is one parameter which is a datetime. I'm using the same parameter over and over. Sometimes it generates the correct result and sometimes it doesn't. When I run the same stored procedure on my development machine it always works. But on the company test server it is intermittent.

The only thing I have found is that it seems like you get the correct result if you haven't run the procedure in a while. But that's pretty vague.

I think someone has ... SET AFU ON;

What you don't know can hurt you!

Jeff Moden · June 12th, 2008, 07:28 PM

Heh... the SET AFU ON may be in the form of unwanted parallelism. Development machines usually only have one or two processors which tend to limit parallelism to none... the test server may have more processors so parallelism can occur. Check the actual execution plan on both machines... see what the difference is...

--Jeff Moden

planoie · June 13th, 2008, 06:46 AM

Jeff,

Can you explain why parallelism would result in different results?

-Peter
compiledthoughts.com

Jeff Moden · June 13th, 2008, 01:22 PM

If there's anything that even comes close to a procedural task or something that's order dependent but ORDER BY hasn't been used, there's no guarantee what order parallelism would do something in. I'd have to see the code to know for sure...

--Jeff Moden

Peso · June 16th, 2008, 01:34 AM

Sometimes even parameter sniffing throws a different execution plan.

David_0223 · June 16th, 2008, 05:52 PM

This is a fairly complex stored procedure. Though I have seen some that were a lot more complex. Essentially the procedure declares a temporary table (@tmpDevices) then populates it with the devices available on the specified date through a couple of INSERT INTO statements.

The next piece uses a cursor to select any updates and apply them to the available devices. Finally a SELECT statement is called on the temporary table to return the records. Here's the cursor portion:

Code:

    DECLARE
        curTmp CURSOR FOR
        SELECT ri.ANALYZER_ID, ri.ITEM_TYPE, idt.ITEM_DATE_ID, idt.CHECKED 
        FROM (appToledoCEM.REPORT_ITEMS ri INNER JOIN appToledoCEM.STATUS_REPORT sr 
        ON ri.STATUS_REP_ID = sr.STATUS_REP_ID) INNER JOIN appToledoCEM.ITEM_DATES idt 
        ON ri.REPORT_ITEM_ID = idt.REPORT_ITEM_ID 
        WHERE sr.REPORT_DATE = @repdate
        ORDER BY ri.ANALYZER_ID, ri.ITEM_TYPE, idt.ITEM_DATE_ID, idt.CHECKED;

            OPEN curTmp;

            WHILE @@FETCH_STATUS = 0
            BEGIN
                FETCH NEXT FROM curTmp INTO @analid, @ittype, @itemid, @checked;

                UPDATE @tmpDevices SET CHECKED = @checked, ITEM_DATE_ID = @analid 
                WHERE ANALYZER_ID = @analid AND ITEM_TYPE = @ittype;
            END;

            CLOSE curTmp;

            SELECT * FROM @tmpDevices ORDER BY ITEM_TYPE, ORDER_NUM;

With the test data I'm using there is only one device that needs to have CHECK set to 1 but it will only do it the first time I run the procedure after not running it for a long time (hours). Even if I edit the stored procedure and re-run it, it doesn't see the Checked item.

I can run the same SELECT statement I used to declare the cursor immediately before I open the cursor. This causes me to get 2 result sets back, the first set shows the one device I need checked. But the second set doesn't.

I added "SELECT @analid, @ittype, @itemid, @checked;" right after the FETCH statement. This give me 2 result sets (where I expected 3), the first is the one record from the Cursor SELECT statement and the second is the final result set. But I didn't get a set for the SELECT following the FETCH.

All in all I'm pretty confused and looking for another way to do this.

Any ideas?

Thanks

What you don't know can hurt you!