Blank Field List When Using Stored Procedure

I am trying to use SQL Server 2000 stored procedure that does not take
any parameters in the Report Designer. When I run dataset in Data tab
of report designer and try to REFRESH the fields, this doesn't help
to get a list of fields. I get no error but no data. So the field list
is still empty. I tried
manually entering the fields via the Fields tab on the Data Set view
or by right clicking in the Fields window and this didn't help. When I
try to run the
report I get "An Error has occured during processing. Invalid attempt
to read when no data is present."
This is my stored procedure:
----
CREATE PROC dbo.sp_branch_counts
AS
IF OBJECT_ID('dbo.temp') IS NOT NULL
drop table [temp]
CREATE TABLE [temp] (
[Branch] [nvarchar] (255) NULL,
[InProgress] int NULL,
[Taken] int NULL,
[Dropped] int NULL
)
GO
declare @.loc nvarchar(50)
DECLARE loc_cursor CURSOR LOCAL
FOR
select branch_id from Branch
FOR READ ONLY
OPEN loc_cursor
FETCH NEXT FROM loc_cursor INTO @.loc
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
select distinct @.loc, * from
(select count(t.tran_status_id) as InProgress
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=1
and u.branch=@.loc) a,
(select count(t.tran_status_id) as Taken
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=2
and u.branch=@.loc) b,
(select count(t.tran_status_id) as Dropped
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=3
and u.branch=@.loc) c
FETCH NEXT FROM loc_cursor INTO @.loc
END
CLOSE loc_cursor
DEALLOCATE loc_cursor
select * from [temp] order by Branch
GO
Please help!!!Have you tried using a table variable in your stored procedure.
"Natasha" wrote:
> I am trying to use SQL Server 2000 stored procedure that does not take
> any parameters in the Report Designer. When I run dataset in Data tab
> of report designer and try to REFRESH the fields, this doesn't help
> to get a list of fields. I get no error but no data. So the field list
> is still empty. I tried
> manually entering the fields via the Fields tab on the Data Set view
> or by right clicking in the Fields window and this didn't help. When I
> try to run the
> report I get "An Error has occured during processing. Invalid attempt
> to read when no data is present."
> This is my stored procedure:
> ----
> CREATE PROC dbo.sp_branch_counts
> AS
> IF OBJECT_ID('dbo.temp') IS NOT NULL
> drop table [temp]
> CREATE TABLE [temp] (
> [Branch] [nvarchar] (255) NULL,
> [InProgress] int NULL,
> [Taken] int NULL,
> [Dropped] int NULL
> )
> GO
> declare @.loc nvarchar(50)
> DECLARE loc_cursor CURSOR LOCAL
> FOR
> select branch_id from Branch
> FOR READ ONLY
> OPEN loc_cursor
> FETCH NEXT FROM loc_cursor INTO @.loc
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> select distinct @.loc, * from
> (select count(t.tran_status_id) as InProgress
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=1
> and u.branch=@.loc) a,
> (select count(t.tran_status_id) as Taken
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=2
> and u.branch=@.loc) b,
> (select count(t.tran_status_id) as Dropped
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=3
> and u.branch=@.loc) c
> FETCH NEXT FROM loc_cursor INTO @.loc
> END
> CLOSE loc_cursor
> DEALLOCATE loc_cursor
> select * from [temp] order by Branch
> GO
> Please help!!!
>|||I tried it and it worked. Thank you so much :)
Natasha
"B. Mark McKinney" <B. Mark McKinney@.discussions.microsoft.com> wrote in message news:<B724506F-89BA-41B9-B786-9E02FFFA17D3@.microsoft.com>...
> Have you tried using a table variable in your stored procedure.
> "Natasha" wrote:
> > I am trying to use SQL Server 2000 stored procedure that does not take
> > any parameters in the Report Designer. When I run dataset in Data tab
> > of report designer and try to REFRESH the fields, this doesn't help
> > to get a list of fields. I get no error but no data. So the field list
> > is still empty. I tried
> > manually entering the fields via the Fields tab on the Data Set view
> > or by right clicking in the Fields window and this didn't help. When I
> > try to run the
> > report I get "An Error has occured during processing. Invalid attempt
> > to read when no data is present."
> >
> > This is my stored procedure:
> > ----
> > CREATE PROC dbo.sp_branch_counts
> > AS
> >
> > IF OBJECT_ID('dbo.temp') IS NOT NULL
> > drop table [temp]
> > CREATE TABLE [temp] (
> > [Branch] [nvarchar] (255) NULL,
> > [InProgress] int NULL,
> > [Taken] int NULL,
> > [Dropped] int NULL
> > )
> > GO
> > declare @.loc nvarchar(50)
> >
> > DECLARE loc_cursor CURSOR LOCAL
> > FOR
> > select branch_id from Branch
> > FOR READ ONLY
> > OPEN loc_cursor
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> >
> > select distinct @.loc, * from
> > (select count(t.tran_status_id) as InProgress
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=1
> > and u.branch=@.loc) a,
> > (select count(t.tran_status_id) as Taken
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=2
> > and u.branch=@.loc) b,
> > (select count(t.tran_status_id) as Dropped
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=3
> > and u.branch=@.loc) c
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > END
> > CLOSE loc_cursor
> > DEALLOCATE loc_cursor
> >
> > select * from [temp] order by Branch
> > GO
> >
> > Please help!!!
> >