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 spRetrieveDoorC ardDataForAllLa bs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficeP hone varchar(20), @PrimaryHomePho ne varchar(20)
declare @Secondaryempno bigint, @SecondaryConta ct varchar(50)
declare @SecondaryOffic ePhone varchar(20), @SecondaryHomeP hone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATI ON_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATI ONUSAGE ON LOCATION.LOCATI ON_ID =
LOCATION_LOCATI ONUSAGE.LOCATIO N_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATI ONUSAGE.LOCATIO N_USAGE_ID =
LOCATION_USAGE. LOCATION_USAGE_ ID
WHERE
(LOCATION_USAGE .GROUP_ID = 1) AND
(LOCATION_LOCAT IONUSAGE.LOCATI ON_USAGE_ID = 131)
order by LOCATION.LOCATI ON_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 viewLocationsFo rDoorCardReport
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_I D = 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,
@PrimaryOfficeP hone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePho ne = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DES CRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryConta ct = Contact_First + ' ' + Contact_Last,
@SecondaryOffic ePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomeP hone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(EMPNO = @Secondaryempno ) AND (PHONE_TYPE_DES CRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficeP hone as
PrimaryOfficePh one, @PrimaryHomePho ne as PrimaryHomePhon e,
@SecondaryConta ct as AlternateContac t, @SecondaryOffic ePhone as
SecondaryOffice Phone, @SecondaryHomeP hone as SecondaryHomePh one

--*************** *************** *************** *************** *************** ************
--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****@discuss ions.microsoft. com> wrote in message
news:A2******** *************** ***********@mic rosoft.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 spRetrieveDoorC ardDataForAllLa bs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department
varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficeP hone varchar(20), @PrimaryHomePho ne varchar(20)
declare @Secondaryempno bigint, @SecondaryConta ct varchar(50)
declare @SecondaryOffic ePhone varchar(20), @SecondaryHomeP hone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATI ON_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATI ONUSAGE ON LOCATION.LOCATI ON_ID =
LOCATION_LOCATI ONUSAGE.LOCATIO N_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATI ONUSAGE.LOCATIO N_USAGE_ID =
LOCATION_USAGE. LOCATION_USAGE_ ID
WHERE
(LOCATION_USAGE .GROUP_ID = 1) AND
(LOCATION_LOCAT IONUSAGE.LOCATI ON_USAGE_ID = 131)
order by LOCATION.LOCATI ON_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 viewLocationsFo rDoorCardReport
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_I D = 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,
@PrimaryOfficeP hone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePho ne = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DES CRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryConta ct = Contact_First + ' ' + Contact_Last,
@SecondaryOffic ePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomeP hone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(EMPNO = @Secondaryempno ) AND (PHONE_TYPE_DES CRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficeP hone as
PrimaryOfficePh one, @PrimaryHomePho ne as PrimaryHomePhon e,
@SecondaryConta ct as AlternateContac t, @SecondaryOffic ePhone as
SecondaryOffice Phone, @SecondaryHomeP hone as SecondaryHomePh one

--*************** *************** *************** *************** *************** ************
--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****@discuss ions.microsoft. com> wrote in message
news:A2******** *************** ***********@mic rosoft.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 spRetrieveDoorC ardDataForAllLa bs AS
begin
declare @LOCATION_ID int
declare @Building varchar(100), @Room varchar(20), @Department
varchar(100)
declare @Primaryempno bigint, @PrimaryContact varchar(50)
declare @PrimaryOfficeP hone varchar(20), @PrimaryHomePho ne varchar(20)
declare @Secondaryempno bigint, @SecondaryConta ct varchar(50)
declare @SecondaryOffic ePhone varchar(20), @SecondaryHomeP hone varchar(20)
declare @BuildingAlias varchar(20)

declare get_loc_id cursor for
SELECT TOP 10 LOCATION.LOCATI ON_ID
FROM
LOCATION INNER JOIN LOCATION_LOCATI ONUSAGE ON LOCATION.LOCATI ON_ID =
LOCATION_LOCATI ONUSAGE.LOCATIO N_ID
INNER JOIN LOCATION_USAGE ON LOCATION_LOCATI ONUSAGE.LOCATIO N_USAGE_ID =
LOCATION_USAGE. LOCATION_USAGE_ ID
WHERE
(LOCATION_USAGE .GROUP_ID = 1) AND
(LOCATION_LOCAT IONUSAGE.LOCATI ON_USAGE_ID = 131)
order by LOCATION.LOCATI ON_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 viewLocationsFo rDoorCardReport
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_I D = 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,
@PrimaryOfficeP hone = PHONE_NUMBER, @Primaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the primary emergency
contact
SELECT @PrimaryHomePho ne = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 8) AND
(EMPNO = @Primaryempno) AND (PHONE_TYPE_DES CRIPTION = 'home')

-- This section looks up the Secondary Emergency contact name and Office
phone Number
SELECT @SecondaryConta ct = Contact_First + ' ' + Contact_Last,
@SecondaryOffic ePhone = PHONE_NUMBER, @Secondaryempno = empno
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(PHONE_TYPE_DES CRIPTION = 'office')

-- This section looks up the "Home" phone number for the Secondary
emergency contact
SELECT @SecondaryHomeP hone = PHONE_NUMBER
FROM viewContacts
WHERE (LOCATION_ID = @LOCATION_ID) AND (CONTACT_TYPE_I D = 9) AND
(EMPNO = @Secondaryempno ) AND (PHONE_TYPE_DES CRIPTION = 'home')

select @Building as Building, @Room as Room, @Department as Dept,
@LOCATION_ID AS LOCATION_ID, @BuildingAlias as UNRAliasID,
@PrimaryContact as PrimaryContact, @PrimaryOfficeP hone as
PrimaryOfficePh one, @PrimaryHomePho ne as PrimaryHomePhon e,
@SecondaryConta ct as AlternateContac t, @SecondaryOffic ePhone as
SecondaryOffice Phone, @SecondaryHomeP hone as SecondaryHomePh one

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

end
GO