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