Reporting Services

Hi,
I am new for the Reporting Services. I need to generate a reports
based on a stored procedure. When we call the stored procedure it says
Invalid object name '#LType'. (Microsoft SQL SERVER, Error:208)
If we see the stored procedure, we find that there is a temporary table
'#LType' where we are storing the data and applying some condition for
fatching the records from the table. At the bottom of the stored
procedure we are also dropping the temporary table.The stored procedure
looks like:
alter procedure Test4 .
@.UID int,@.varSourceType varchar(10)=null
......
......
as
set quoted_identifier off
set nocount on
declare @.BranchIDsql varchar(1000),@.varleveltype varchar
.....
....
Begin
create table <b> #LType (LevelType varchar(4)) </b>
insert into #LType select ltrim(rtrim(UL.varleveltype)) from
tbluserlevel UL inner join tblUserProfile UP on
UL.intUserProfileID=UP.intUserProfileID where UP.intUserLoginID=@.UID
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[BID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BID]
create table BID (intUlBranchID int)
if ((select count(Leveltype) from #LType where LevelType='CORP')>0)
Begin
set @.BranchIDsql="insert into BID select tblULBranch.intUlBranchID
from tblULBranch where intULBranchGroupID in
(select intULBranchGroupID from tblULBranchGroup where intULCountyID
in
(select intULCountyID from tblULCounty where intULRegionID in
(select intULRegionID from tblULRegion where intULCompanyID in
(select intULCompanyID from tblULCompany where intULCompanyGroupID
in
(select intULCompanyGroupID from tblULCompanyGroup where
intULCorporateID in
(select intlevelvalue from tblUserLevel where intUserProfileID in
(select intUserProfileID from tblUserProfile where intUserLoginID in
("+convert(varchar,@.UID)+"))))))))"
exec(@.BranchIDsql)
End
............
............
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[#LType]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[#LType]
Thanks in Advance
Munna Kumar Singh
Bangalore
IndiaHi
Your procedure is not returning a result set!
Why are you dropping the BID table, this could cause issues on a multiuser
system?
Maybe something like the following (untested!) may be what you want?
ALTER PROCEDURE Test4 @.UID INT,@.varSourceType VARCHAR(10)=NULL
......
......
AS
SET NOCOUNT ON
DECLARE @.BranchIDsql VARCHAR(1000),@.varleveltype VARCHAR(4)
.....
....
BEGIN
IF EXISTS ( SELECT * FROM
dbo.tbluserlevel UL
JOIN dbo.tblUserProfile UP ON UL.intUserProfileID=UP.intUserProfileID
WHERE UP.intUserLoginID=@.UID
AND LTRIM(RTRIM(UL.varleveltype)) = 'CORP'
)
BEGIN
SELECT DISTINCT B.intUlBranchID
FROM tblULBranch B
JOIN tblULBranchGroup G ON B.intULBranchGroupID = G.intULBranchGroupID
JOIN tblULCounty C ON G.intULCountyID = C.intULCountyID
JOIN tblULRegion R ON R.intULRegionID = C.intULRegionID
JOIN tblULCompany Y ON Y.intULCompanyID = R.intULCompanyID
JOIN tblULCompanyGroup Z ON Y.intULCompanyGroupID = Z.intULCompanyGroupID
JOIN tblUserLevel U ON U.intlevelvalue = Z.intULCorporateID
WHERE U.intUserLoginID = @.UID
END
ELSE
SELECT NULL AS intUlBranchID
END
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data to get a more precise reply. Also posting expected results from
example data is very useful.
John
<dreammunna@.gmail.com> wrote in message
news:1135665571.048020.288240@.g43g2000cwa.googlegroups.com...
> Hi,
> I am new for the Reporting Services. I need to generate a reports
> based on a stored procedure. When we call the stored procedure it says
> Invalid object name '#LType'. (Microsoft SQL SERVER, Error:208)
> If we see the stored procedure, we find that there is a temporary table
> '#LType' where we are storing the data and applying some condition for
> fatching the records from the table. At the bottom of the stored
> procedure we are also dropping the temporary table.The stored procedure
> looks like:
>
> alter procedure Test4 .
> @.UID int,@.varSourceType varchar(10)=null
> ......
> ......
> as
> set quoted_identifier off
> set nocount on
> declare @.BranchIDsql varchar(1000),@.varleveltype varchar
> .....
> ....
> Begin
> create table <b> #LType (LevelType varchar(4)) </b>
> insert into #LType select ltrim(rtrim(UL.varleveltype)) from
> tbluserlevel UL inner join tblUserProfile UP on
> UL.intUserProfileID=UP.intUserProfileID where UP.intUserLoginID=@.UID
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[BID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[BID]
> create table BID (intUlBranchID int)
> if ((select count(Leveltype) from #LType where LevelType='CORP')>0)
> Begin
> set @.BranchIDsql="insert into BID select tblULBranch.intUlBranchID
> from tblULBranch where intULBranchGroupID in
> (select intULBranchGroupID from tblULBranchGroup where intULCountyID
> in
> (select intULCountyID from tblULCounty where intULRegionID in
> (select intULRegionID from tblULRegion where intULCompanyID in
> (select intULCompanyID from tblULCompany where intULCompanyGroupID
> in
> (select intULCompanyGroupID from tblULCompanyGroup where
> intULCorporateID in
> (select intlevelvalue from tblUserLevel where intUserProfileID in
> (select intUserProfileID from tblUserProfile where intUserLoginID in
> ("+convert(varchar,@.UID)+"))))))))"
> exec(@.BranchIDsql)
> End
> ............
> ............
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[#LType]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[#LType]
>
> Thanks in Advance
> Munna Kumar Singh
> Bangalore
> India
>