May 23, 2009 at 6:16 am
hi all,
i am developing reports using ssrs
i am using BI project for develop this reports ,
i write query in query string of dataset as follow
exec [HSI].[usp_RaguTest_Reports] @year
but once i click on the OK button it give me followiong error
Invalid object name '##Report'. (Microsoft SQL Server, Error: 208)
please help me out to solve this problem
thanks
Vikram
May 23, 2009 at 7:58 am
can you post the DDL for [HSI].[usp_RaguTest_Reports] ?
"##report" is a global temp table that seems to be missing.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 23, 2009 at 10:30 am
hi here below is query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_Test_Reports]
@intYear int
as
Declare @intIncidentCategoryID int;
Declare @chvIncidentCategory varchar(50);
Declare @intIncidentModeID int;
Declare @chvIncidentMode varchar(50);
Declare @chvCTIncCat nvarchar(4000)
Declare @chvCTIncMode nvarchar(4000)
Declare @chvCreateTable nvarchar(4000)
Declare @chvTableName nvarchar(10);
Declare @chvDropTableQuery nvarchar(4000)
DECLARE @chnParmDefinition nvarchar(500);
DECLARE @intVariable int;
Declare @chvType varchar(50);
Declare @chvInsertQueryPart1 varchar(4000);
Declare @chvInsertQueryPart2 varchar(4000);
Declare @chvInsertQueryPart11 varchar(4000);
Declare @chvInsertQueryPart22 varchar(4000);
Declare @chvOutQuery1 varchar(5000);
Declare @chvOutQuery2 varchar(5000);
set @chvInsertQueryPart1= 'Insert into ##Report ([YEAR]' ;
set @chvInsertQueryPart2 = 'values (' + CONVERT(VARCHAR(4), @intYear)
Set @chvTableName= '##Report';
set @chvCTIncCat='Create table ' + @chvTableName + '([ReportTableID] [int] IDENTITY(1,1) NOT NULL,[YEAR] INT,[Month] int'
DECLARE cur_IncCat CURSOR FOR
Select IncidentCategoryID,IncidentCategory from tblIncidentCategory
where IsDeleted=0 and IsApproved=1
-- Open CURSOR cur_IncCat
OPEN cur_IncCat
FETCH NEXT FROM cur_IncCat
INTO
@intIncidentCategoryID, @chvIncidentCategory
WHILE @@FETCH_STATUS = 0
BEGIN
set @chvCTIncCat=@chvCTIncCat + ',[IC_' +convert(varchar(4),@intYear) + @chvIncidentCategory + '] varchar(50)'
set @chvCTIncCat=@chvCTIncCat + ',[IC_'+convert(varchar(4),(@intYear-1)) + @chvIncidentCategory + '] varchar(50)'
FETCH NEXT FROM cur_IncCat
INTO
@intIncidentCategoryID, @chvIncidentCategory
END
CLOSE cur_IncCat
DEALLOCATE cur_IncCat
DECLARE cur_IncMode CURSOR FOR
Select IncidentModeID,IncidentMode
from tblIncidentMode
where IsDeleted=0 and IsApproved=1
-- Open CURSOR cur_IncCat
OPEN cur_IncMode
FETCH NEXT FROM cur_IncMode
INTO
@intIncidentModeID, @chvIncidentMode
WHILE @@FETCH_STATUS = 0
BEGIN
set @chvCTIncCat=@chvCTIncCat + ',[IM_' +convert(varchar(4),@intYear)+ @chvIncidentMode + '] varchar(50)'
set @chvCTIncCat=@chvCTIncCat + ',[IM_'+convert(varchar(4),(@intYear-1))+ @chvIncidentMode + '] varchar(50)'
FETCH NEXT FROM cur_IncMode
INTO
@intIncidentModeID, @chvIncidentMode
END
--Close CURSOR cur_IncCat
CLOSE cur_IncMode
DEALLOCATE cur_IncMode
set @chvCTIncCat=@chvCTIncCat+')';
Print @chvCTIncCat
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 1 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 1,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 2 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 2,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 3 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 3,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 4 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 4,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 5 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 5,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 6 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 6,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 7 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 7,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 8 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 8,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 9 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 9,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 10 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 10,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 11 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 11,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
----------------------------For Month 12 ----------------------------------------------------------
execute [usp_GetInsertQueryForReports] 12,@intYear, @chvOutQuery1 output, @chvOutQuery2 output
set @chvInsertQueryPart11 =@chvInsertQueryPart1+ @chvOutQuery1 -- + ') '
set @chvInsertQueryPart22 = @chvInsertQueryPart2 + @chvOutQuery2 -- + ')'
SET @chvCTIncCat =@chvInsertQueryPart11+@chvInsertQueryPart22;
EXEC sp_executesql @chvCTIncCat;
SELECT * FROM ##Report
set @chvDropTableQuery = 'drop table ' + @chvTableName;
EXEC sp_executesql @chvDropTableQuery;
May 23, 2009 at 11:13 am
I would just run the S.P in ur management studio or Query analyzer window with taking off create S.P from ur query and assigning the @year value to check if it runs fine. If there is some error, it will point out the specific line in ur query where the error is happening.
May 24, 2009 at 3:28 am
Mayank Khatri (5/23/2009)
I would just run the S.P in ur management studio or Query analyzer window with taking off create S.P from ur query and assigning the @year value to check if it runs fine. If there is some error, it will point out the specific line in ur query where the error is happening.
That's indeed the debug path to follow !
IMO your "EXEC sp_executesql @chvCTIncCat ;" must have failed.
Could be a to long string issue, so your statement would have been cut off, causing a syntax error.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply