December 31, 2007 at 6:01 pm
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?
January 1, 2008 at 3:47 pm
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
January 2, 2008 at 9:03 am
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.
January 3, 2008 at 8:23 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply