Incorrect Data returned by USP to SSRS

Question

Incorrect Data returned by USP to SSRS

Ronald R Mayfield

SSC-Addicted

Points: 446
More actions
December 31, 2007 at 6:01 pm

#182264

I have a report that has been running for about a year. Today, the data returned by the User Stored Procedure was the oldest data in the tables instead of the newest. I checked the stored procedure, ran it in SSMS and the data returned was correct. I ran the procedure in SSRS and the data returned was not correct.
The procedure returns the "Top 30" values from a 3 view join ordered by date desc. When running this in the management studio I get all items from December 2007. When running this from the reporting server, I get what would be the "Top 30" values from a 3 view join ordered by date asc.
Has anyone seen this type of behavior before?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

Without more details, I'll have to give you a generalized response. Never suspect a bug. Always remember "Garbage In - Garbage Out".

Your procedure is okay when YOU run it in QA. Does SSRS run it the same way that you do. The fact that this is the first day of a new year is a very strong hint. If there is a parameter involved, then what value is actually being passed.

Tom Garth

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Ronald R Mayfield SSC-Addicted Points: 446 More actions · Answer 2

The procedure has one parameter, which is a 3 digit brand name. It looks something like this:

Select Top 30

a.FERMID

,a.FermID

,a.Value AS TA

,b.Value AS FA

,c.Value as XTF

,a.FillDate

FROM snb01.dbo.vwAe a

INNER JOIN snb01.dbo.vwxt c ON a.FERMID=c.FERMID

INNER JOIN snb01.dbo.vwFA b ON a.FERMID=b.FERMID

WHERE a.Brand=@Brand

AND b.Value is not null

The view vwAe is ordered by FillDate desc.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

Add an ORDER BY to your SQL statement.

In general, you should never rely on embedded sort orders (i.e. view sorts, primary key of a table, etc.) to return the results in the correct manner from a database. It's possible that the UDF has an old query plan, that is returning results in a different order than when you execute the SQL statement directly. As well, I've seen different sort orders occur for the same statement depending on how it was submitted to the database.

Basically, not including an ORDER BY tells the engine you don't care about the sort order.