I created a new dataset for a new report that gets data from a stored
procedure. But when I run the dataset in the Data tab, it only returns the
column names with no data. The stored procedure runs fine in the Query
Analyzer with several returned records. Any help will be appreciated!On Mar 7, 4:14 pm, obnddc <obn...@.discussions.microsoft.com> wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!
Have you verified that the command type of the dataset is set to
stored procedure (instead of text)?
Enrique Martinez
Sr. SQL Server Developer|||Since it is returning all the columns it means it has accessed stored proc,
Just check the datasource using "test connection" if possible recreate the
datasource, more over do a "Refresh". Check for the server you connected
using Query Analyzer and the datasource are same.
Amarnath
"obnddc" wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!|||Thanks for the input, Emartinez and Amarnath!
I found out that the problem lay in the data itself. I have an input
parameter used in the where condition, like "WHERE tbl_name.customerID LIKE
@.custID"
The test data I used has several spaces after cutomerID charactors. It is
interesting to see the LIKE statement will ignore the spaces in Query
Analyzer but fail in SQL reporting services.
I tried to LTRIM and RTRIM customerID, or use @.custID+'%' but none worked.
Anyone here can help? Thanks.|||Finally I found out, it's not the spaces but the parameters.
I have begin date and end date as input parameters, and end date is
optional. When it is null, getdate(). In Query Analyzer, I left it empty, it
returned records. In SQL report, I have to enter an end date and the date I
entered happens to be the only date that has records. So no record met the
dates, no record reurned.
What I learned:
Before you conclude same process ran differently in different envirements,
make sure they ran under EXACTLY same conditions!
Thanks to all!
