Data will not show on report preview

I have a proc that combines the results from 2 queries. I can see all of the
data in QA and the Data tab of SRS there are no issues with that part. When
I preview the report only the second part shows up, nothing from the first
query. I have started over a few times, still the same problem. Any ideas'
ThanksMake sure your SP returns only one resultset. RS does not handle multiple
resultsets. I.e. you should only have one select statement. If you have
multiple it will only use the first one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> I have a proc that combines the results from 2 queries. I can see all of
the
> data in QA and the Data tab of SRS there are no issues with that part.
When
> I preview the report only the second part shows up, nothing from the first
> query. I have started over a few times, still the same problem. Any
ideas'
> Thanks|||There is only one select statement, it joins 2 temp tables. The record set
looks fine in SRS Data tab and in QA, just not in the actual report.
"Bruce L-C [MVP]" wrote:
> Make sure your SP returns only one resultset. RS does not handle multiple
> resultsets. I.e. you should only have one select statement. If you have
> multiple it will only use the first one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> > I have a proc that combines the results from 2 queries. I can see all of
> the
> > data in QA and the Data tab of SRS there are no issues with that part.
> When
> > I preview the report only the second part shows up, nothing from the first
> > query. I have started over a few times, still the same problem. Any
> ideas'
> > Thanks
>
>|||Try it using the generic query designer in the data tab. The code used there
is exactly what is used during report preview time.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||This should work. One thing to make sure of with your SP is that you do not
explicitly drop your temp tables. Just let them fall out of scope. Also,
have your select be the last statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||I started all over again, this time I created the parameter dataset first
then added the call to the stored proc. This worked, no clue as to why.. as
to the temp tables I have always droped them after the last select with no
issues.
thanks all ;)
"Bruce L-C [MVP]" wrote:
> This should work. One thing to make sure of with your SP is that you do not
> explicitly drop your temp tables. Just let them fall out of scope. Also,
> have your select be the last statement.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> > There is only one select statement, it joins 2 temp tables. The record set
> > looks fine in SRS Data tab and in QA, just not in the actual report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make sure your SP returns only one resultset. RS does not handle multiple
> >> resultsets. I.e. you should only have one select statement. If you have
> >> multiple it will only use the first one.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> >> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> >> > I have a proc that combines the results from 2 queries. I can see all
> >> > of
> >> the
> >> > data in QA and the Data tab of SRS there are no issues with that part.
> >> When
> >> > I preview the report only the second part shows up, nothing from the
> >> > first
> >> > query. I have started over a few times, still the same problem. Any
> >> ideas'
> >> > Thanks
> >>
> >>
> >>
>
>

Data Type and Length in SQL Server

Hello folks!

I have written a stored proc that selects data from this table:
A1 AA
A2 BB
B1 AAA
B2 BBB

and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB

In short, I wanted to concatenate data in the second column.

My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.

Any ideas how I can get around this problem?

Thanks!

ParulVChances are, it is not limiting the string to 256 characters. This is probably due to the default 256 character result set width in Query Analyzer. Go through Query Analyzer's options and bump it up to a higher value.