reporting services - query from stored procedure returns one recor

[andres]

hi all,
thanks for everyone for your support!

i have a report being built in studio 2005. It calls a stored procedure. The
SP runs a query and gets a data set which then loops through to get other
data. At the end the stored procedure cursor returns several data sets.

my problem is that the report services appears to be reading only the data
returned from the first loop of the cursor inside the stored procedure. It
does not return the other result. Can anyone explain how i can retrieve the
other data sets the store procured cursor should be sending out? I have
tested the SP and makde sure it DOES return more than one result.

here is the sp in case you need to see it.:

CREATE PROCEDURE spRetrieveDoorCardDataForAllLabs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficePhone varchar(20), @PrimaryHomePhone varchar(20)
declare @Secondaryempno bigint, @SecondaryContact varchar(50)
declare @SecondaryOfficePhone varchar(20), @SecondaryHomePhone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATION_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATIONUSAGE ON LOCATION.LOCATION_ID =
LOCATION_LOCATIONUSAGE.LOCATION_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID =
LOCATION_USAGE.LOCATION_USAGE_ID
WHERE
(LOCATION_USAGE.GROUP_ID = 1) AND
(LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID = 131)
order by LOCATION.LOCATION_ID

open get_loc_id
fetch next from get_loc_id into @LOCATION_ID

while @@fetch_status = 0
begin
--BEGIN CURSOR LOOP
--************************************************** *************************************
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME,
@BuildingAlias = UNR_ALIAS_ID
FROM viewLocationsForDoorCardReport
WHERE (LOCATION_ID = @LOCATION_ID)

-- This section lookups up the Building Name, Room and Department
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 4 OR
CONTACT_TYPE_ID = 7 OR
CONTACT_TYPE_ID = 2)

-- This section looks up the Primary Emergency contact name and Office
phone Number
SELECT @PrimaryContact = Contact_First + ' ' + Contact_Last,
@PrimaryOfficePhone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryContact = Contact_First + ' ' + Contact_Last,
@SecondaryOfficePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(EMPNO = @Secondaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficePhone as
PrimaryOfficePhone, @PrimaryHomePhone as PrimaryHomePhone,
@SecondaryContact as AlternateContact, @SecondaryOfficePhone as
SecondaryOfficePhone, @SecondaryHomePhone as SecondaryHomePhone

--************************************************** *************************************
--END CURSOR LOOP
fetch next from get_loc_id into @LOCATION_ID
end
close get_loc_id
deallocate get_loc_id

end
GO

[Cowboy \(Gregory A. Beamer\)]

Select all of the records in the cursor loop (argh!*) into a temp location,
like a table variable or, if you must, a pound temp table (temporary temp
table, not global, which is pound pound): #tempTable. Then select all the
records with a single select after the cursor.

reporting services is probably getting all of the data, but it sees each of
the results as a separate table and only handles table one. This is, BTW,
extremely normal.

* I would choose something other than a cursor to do this, like building the
denormalized data in temp tables or table variables

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
"andres" <an****@discussions.microsoft.com> wrote in message
news:A2**********************************@microsof t.com...

hi all,
thanks for everyone for your support!

i have a report being built in studio 2005. It calls a stored procedure.
The
SP runs a query and gets a data set which then loops through to get other
data. At the end the stored procedure cursor returns several data sets.

my problem is that the report services appears to be reading only the data
returned from the first loop of the cursor inside the stored procedure. It
does not return the other result. Can anyone explain how i can retrieve
the
other data sets the store procured cursor should be sending out? I have
tested the SP and makde sure it DOES return more than one result.

here is the sp in case you need to see it.:

CREATE PROCEDURE spRetrieveDoorCardDataForAllLabs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department
varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficePhone varchar(20), @PrimaryHomePhone varchar(20)
declare @Secondaryempno bigint, @SecondaryContact varchar(50)
declare @SecondaryOfficePhone varchar(20), @SecondaryHomePhone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATION_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATIONUSAGE ON LOCATION.LOCATION_ID =
LOCATION_LOCATIONUSAGE.LOCATION_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID =
LOCATION_USAGE.LOCATION_USAGE_ID
WHERE
(LOCATION_USAGE.GROUP_ID = 1) AND
(LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID = 131)
order by LOCATION.LOCATION_ID

open get_loc_id
fetch next from get_loc_id into @LOCATION_ID

while @@fetch_status = 0
begin
--BEGIN CURSOR LOOP
--************************************************** *************************************
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME,
@BuildingAlias = UNR_ALIAS_ID
FROM viewLocationsForDoorCardReport
WHERE (LOCATION_ID = @LOCATION_ID)

-- This section lookups up the Building Name, Room and Department
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 4 OR
CONTACT_TYPE_ID = 7 OR
CONTACT_TYPE_ID = 2)

-- This section looks up the Primary Emergency contact name and Office
phone Number
SELECT @PrimaryContact = Contact_First + ' ' + Contact_Last,
@PrimaryOfficePhone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryContact = Contact_First + ' ' + Contact_Last,
@SecondaryOfficePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(EMPNO = @Secondaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficePhone as
PrimaryOfficePhone, @PrimaryHomePhone as PrimaryHomePhone,
@SecondaryContact as AlternateContact, @SecondaryOfficePhone as
SecondaryOfficePhone, @SecondaryHomePhone as SecondaryHomePhone

--************************************************** *************************************
--END CURSOR LOOP
fetch next from get_loc_id into @LOCATION_ID
end
close get_loc_id
deallocate get_loc_id

end
GO

[andres]

Thank you Cowboy! Why didn't I think of that!!! I'll give it a try!

"Cowboy (Gregory A. Beamer)" wrote:

Select all of the records in the cursor loop (argh!*) into a temp location,
like a table variable or, if you must, a pound temp table (temporary temp
table, not global, which is pound pound): #tempTable. Then select all the
records with a single select after the cursor.

reporting services is probably getting all of the data, but it sees each of
the results as a separate table and only handles table one. This is, BTW,
extremely normal.

* I would choose something other than a cursor to do this, like building the
denormalized data in temp tables or table variables

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
"andres" <an****@discussions.microsoft.com> wrote in message
news:A2**********************************@microsof t.com...

hi all,
thanks for everyone for your support!

i have a report being built in studio 2005. It calls a stored procedure.
The
SP runs a query and gets a data set which then loops through to get other
data. At the end the stored procedure cursor returns several data sets.

my problem is that the report services appears to be reading only the data
returned from the first loop of the cursor inside the stored procedure. It
does not return the other result. Can anyone explain how i can retrieve
the
other data sets the store procured cursor should be sending out? I have
tested the SP and makde sure it DOES return more than one result.

here is the sp in case you need to see it.:

CREATE PROCEDURE spRetrieveDoorCardDataForAllLabs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department
varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficePhone varchar(20), @PrimaryHomePhone varchar(20)
declare @Secondaryempno bigint, @SecondaryContact varchar(50)
declare @SecondaryOfficePhone varchar(20), @SecondaryHomePhone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATION_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATIONUSAGE ON LOCATION.LOCATION_ID =
LOCATION_LOCATIONUSAGE.LOCATION_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID =
LOCATION_USAGE.LOCATION_USAGE_ID
WHERE
(LOCATION_USAGE.GROUP_ID = 1) AND
(LOCATION_LOCATIONUSAGE.LOCATION_USAGE_ID = 131)
order by LOCATION.LOCATION_ID

open get_loc_id
fetch next from get_loc_id into @LOCATION_ID

while @@fetch_status = 0
begin
--BEGIN CURSOR LOOP
--************************************************** *************************************
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME,
@BuildingAlias = UNR_ALIAS_ID
FROM viewLocationsForDoorCardReport
WHERE (LOCATION_ID = @LOCATION_ID)

-- This section lookups up the Building Name, Room and Department
SELECT @Building = PARENT, @Room = LOCATION_NAME, @Department = DNAME
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 4 OR
CONTACT_TYPE_ID = 7 OR
CONTACT_TYPE_ID = 2)

-- This section looks up the Primary Emergency contact name and Office
phone Number
SELECT @PrimaryContact = Contact_First + ' ' + Contact_Last,
@PrimaryOfficePhone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryContact = Contact_First + ' ' + Contact_Last,
@SecondaryOfficePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(PHONE_TYPE_DESCRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomePhone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_ID = 9) AND
(EMPNO = @Secondaryempno) AND (PHONE_TYPE_DESCRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficePhone as
PrimaryOfficePhone, @PrimaryHomePhone as PrimaryHomePhone,
@SecondaryContact as AlternateContact, @SecondaryOfficePhone as
SecondaryOfficePhone, @SecondaryHomePhone as SecondaryHomePhone

--************************************************** *************************************
--END CURSOR LOOP
fetch next from get_loc_id into @LOCATION_ID
end
close get_loc_id
deallocate get_loc_id

end
GO