May 2, 2014 at 9:31 am
In an SSRS 2008 report, I am not getting some records selected when I run the report in the ssrs 2008 report.
However when I run the sql in ssis manager, the records are selected. Thus I am trying to determine why some of the records are not selected whe running the ssrs report.
In the table called 'transactionfile', the fields that I am trying to determine where there is a problem is the following:
SchoolNumber (varchar(50),null), and
TransactionPaymentDate (varchar(50),null).
Examples of data are SchoolNumber = '121' and TransactionPaymentDate = '04162014'
In the SSRS report, the parameter value for @SchoolNumber is text and the parameter value for @EndDate and @StartDate is date/time.
The following is the query I am having a problem with:
SELECT s.SchoolNumber,
CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring (TransactionPaymentDate,1,2)+ '-' +
substring(TransactionPaymentDate,3,2)) as TransactionPaymentDate
from [eF].[dbo].[transactionfile] f
INNER JOIN
[eF].[dbo].schools] s
on rtrim(ltrim(s.SchoolNumber)) =rtrim(ltrim(f.SchoolNumber))
where rtrim(ltrim(s.SchoolNumber)) = rtrim(ltrim(@SchoolNumber))
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date)
>= @StartDate
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date) <= @EndDate
Thus could you suggerst what could be wrong when selecting some records by date?
May 2, 2014 at 10:24 am
It's really hard to tell what could be the problem with all the data type manipulation being done in the query.
My first thought is that it doesn't have anything to do with the query but that there is a Filter in one of the objects in the report (dataset, tablix or data region) that is limiting the results returned. So I'd look for those first.
I'd also, in development, run Profiler to verify that what I think is being passed to the SQL Server is really what is being passed to the SQL Server.
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
May 2, 2014 at 10:32 am
Are you using this query inside a stored procedure? If so there might be an issue of Parameter Sniffing.. Check in relation with that..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
May 2, 2014 at 10:38 am
a4apple (5/2/2014)
Are you using this query inside a stored procedure? If so there might be an issue of Parameter Sniffing.. Check in relation with that..
Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.
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
May 2, 2014 at 12:48 pm
Jack Corbett (5/2/2014)
Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.
Jack, I only said since the data is not retrieved correctly may be check that aspect too.. I didn't mean that's the only root cause.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
May 2, 2014 at 12:53 pm
a4apple (5/2/2014)
Jack Corbett (5/2/2014)
Parameter sniffing won't cause the results returned to be incorrect it can cause a sub-optimal plan to be used skewing performance. If parameter sniffing causes incorrect results that's a bug and a very big one that I think would have been caught and fixed a long time ago.Jack, I only said since the data is not retrieved correctly may be check that aspect too.. I didn't mean that's the only root cause.
But parameter sniffing won't cause data to be retrieved incorrectly, just possibly not as quickly. There is no reason to consider parameter sniffing for incorrect results. For inconsistent performance yes, but not incorrect results.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply