January 26, 2010 at 6:10 am
Hi,
I've been getting a strange problem with developing reports in Visual Studio where it is showing data that is incorrect. If I run a stored proc in VS I get an incorrect set of data, but if I run the same stored proc in Management Studio I get the correct set of data.
Now the really odd part is that the VS data is mostly correct i.e. row count matches Management Studio and the core of the data also matches (by this I mean that the both datasets contain the same personnel) but a whole column will contain incorrect data. The incorrect data is still of the same type as the correct data but it does not in any way relate to the record that has been returned.
Also the deployed report acts in the same way as Visual Studio displaying the incorrect data.
It turns out that running sp_recompile on the stored proc in question fixes the problem but I just don't understand how two products can get different data from the same stored proc.
Any help with this would be really appreciated as this is starting to make me lose confidence in SSRS.
Thanks,
Ben
January 26, 2010 at 6:35 am
Hi Ben
Does the stored proc have parameters?
Can you post the stored proc?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 26, 2010 at 6:54 am
Heres the code of one of the stored procs that has been subject to this problem.
ALTER PROCEDURE [reports].[spTimetablingEventData]
@EnteredDateFrom as char(10),
@EnteredDateTo as char(10),
@EventTypeCode as varchar(3),
@BookedForCode as varchar(12),
@BookedByMSTCode as varchar(12),
@ActivityCode as varchar(12),
@RoomCode as varchar(12),
@BuildingCode as varchar(12)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DateFrom DATETIME
DECLARE @DateTo DATETIME
BEGIN TRY
SET @DateFrom = CONVERT(DATETIME, @EnteredDateFrom, 103)
SET @DateTo = CONVERT(DATETIME, @EnteredDateTo, 103)
END TRY
BEGIN CATCH
RAISERROR (50001,11,0)
GOTO ProcEnd
END CATCH
--Raise error if start date > end date. Custom error message is returned on invalid date range.
IF @DateFrom > @DateTo
BEGIN
RAISERROR (50007,16,0)
GOTO ProcEnd
END
select
EI.EventTitle, EI.ModuleCode, MAV.ModuleName, MAV.Domain as ModuleArea, EI.EventInstanceDate, EI.EventStartTime, EI.EventEndTime,
ROM.Building,
case
when EIR.RoomCode is null then null
else dbo.FloorFromRoom(EIR.RoomCode)
end as Floor, EIR.RoomCode, EI.EventCode, EI.EventInstanceSequence, EI.EventType, EI.Activity, EI.BookingCode, EI.BookedForCode,
dbo.fProperCase(EI.BookedForForename) as BookedForForename, dbo.fProperCase(EI.BookedForSurname) as BookedForSurname, EI.BookedByMSTCode,
dbo.fProperCase(EI.BookedByMSTForename) as BookedByMSTForename, dbo.fProperCase(EI.BookedByMSTSurname) as BookedByMSTSurname
from
sits.vwEventInstance as EI LEFT JOIN sits.vwModuleAvailability as MAV
ON EI.ModuleCode =MAV.ModuleCode and
EI.PeriodSlotCode = MAV.PeriodSlotCode and
EI.MAVOccurrence = MAV.AvailabilityOccurrence and
EI.AcademicYearCode = MAV.AcademicYearCode
LEFT JOIN sits.vwEventInstanceRoom as EIR
ON EI.EventCode = EIR.EventCode and
EI.EventInstanceSequence = EIR.EventInstanceSequence
LEFT JOIN sits.vwRoom as ROM
ON ROM.RoomCode = EIR.RoomCode
where
EI.EventTypeCode <> 'U' and
EI.EventInstanceDate Between @DateFrom and @DateTo and
(EI.EventTypeCode = @EventTypeCode or @EventTypeCode ='All') and
(EI.BookedForCode = @BookedForCode or @BookedForCode = 'All') and
(EI.ActivityCode = @ActivityCode or @ActivityCode = 'All') and
(EIR.RoomCode = @RoomCode or @RoomCode = 'All') and
(ROM.BuildingCode = @BuildingCode or @BuildingCode = 'All') and
(EI.BookedByMSTCode = @BookedByMSTCode or @BookedByMSTCode = 'All')
order by
EI.EventInstanceDate,
EI.EventStartTime,
EI.EventEndTime,
ROM.BuildingCode,
EIR.RoomCode
ProcEnd:
END
January 26, 2010 at 7:08 am
Which columns from this query appear to differ between the two client environments?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 26, 2010 at 7:27 am
In this proc it was RoomCode which contained incorrect data in Visual Studio. Running the proc in Management Studio gave the correct data.
The same parameters were used in both VS and Management Studio.
Ben
January 26, 2010 at 7:30 am
is the entereddata an actual data time or just a date ??
This could be a reason for the difference but would need to see exactly what was being passed through
January 26, 2010 at 7:38 am
benreeve007 (1/26/2010)
In this proc it was RoomCode which contained incorrect data in Visual Studio. Running the proc in Management Studio gave the correct data.The same parameters were used in both VS and Management Studio.
Ben
Have a look at the function dbo.FloorFromRoom in different db's on the same server.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 26, 2010 at 7:39 am
So RoomCode was incorrect, but Floor was correct? Are you sure everything else is the same in each row and that the sort order is just different? Are you sure that you are connected to the same server/database in each case?
I'd run Profiler and run the proc from both places. Make sure you use the ExistingConnection, RPC:Completed, and SP:Completed Events so you can compare the connection properties and call between VS and SSMS. It seems odd, but one these settings could possibly be affecting sort order, etc..
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2010 at 8:19 am
dave.repton (1/26/2010)
is the entereddata an actual data time or just a date ??This could be a reason for the difference but would need to see exactly what was being passed through
Its just a date which is entered into a text box. The stored proc then converts the entered date into a datetime data type. In SSRS users have to enter dates as text because for some reason the calender interface is ridiculously slow on our network machines.
Dates would be entered in the format DD/MM/YYYY.
January 26, 2010 at 8:22 am
Have a look at the function dbo.FloorFromRoom in different db's on the same server.
I can't see anything which would have caused an issue. The function itself is quite simple.
ALTER FUNCTION [dbo].[FloorFromRoom]
(@RoomCode nvarchar(12))
RETURNS nvarchar(25)
AS
BEGIN
DECLARE @FloorCharacter as nchar(1)
DECLARE @TheResult as nvarchar(25)
Set @FloorCharacter = substring(@RoomCode,2,1)
If @FloorCharacter = '0' begin set @TheResult = 'Ground Floor' end
Else If @FloorCharacter = '1' begin set @TheResult = '1st Floor' end
Else If @FloorCharacter = '2' begin set @TheResult = '2nd Floor' end
Else If @FloorCharacter = '3' begin set @TheResult = '3rd Floor' end
Else If @FloorCharacter = '4' begin set @TheResult = '4th Floor' end
Else If @FloorCharacter = '5' begin set @TheResult = '5th Floor' end
Else If @FloorCharacter = '6' begin set @TheResult = '6th Floor' end
Else begin set @TheResult = 'Another Floor' end
RETURN @TheResult
END
January 26, 2010 at 8:33 am
Jack Corbett (1/26/2010)
So RoomCode was incorrect, but Floor was correct? Are you sure everything else is the same in each row and that the sort order is just different? Are you sure that you are connected to the same server/database in each case?I'd run Profiler and run the proc from both places. Make sure you use the ExistingConnection, RPC:Completed, and SP:Completed Events so you can compare the connection properties and call between VS and SSMS. It seems odd, but one these settings could possibly be affecting sort order, etc..
The connections were the same.
I don't think I can get an awful lot out of the profiler at the moment because this proc is working fine now but it didn't previously. It was fixed by running sp_recompile on the stored proc. No changes were made to the actual SQL or any used functions.
I'm just trying to understand why this problem keeps appearing in our reports. It doesn't make any sense why the same proc gives different results in different programs and that sp_recompile fixes it.
I do remember from when I ran the trace when this proc was an issue that when run from Management Studio, VS and Reports Manager that they all asked the server to run the same stored proc and that they were all passing the same parameters.
Ben
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply