March 6, 2009 at 4:22 am
Hi,
I am having stored procedure
CREATE procedure USP_ONLREPORTS
(@ServiceCode varchar(2))
As
declare @sql varchar(8000)
set @sql ='if exists (select * from dbo.sysobjects
where id = object_id(N''[dbo].[USP_ONLREPORTS_View]'')
and OBJECTPROPERTY(id, N''IsView'') = 1)
drop view USP_ONLREPORTS_View'
exec(@sql)
set @sql=''
set @sql='create view USP_ONLREPORTS_View as
select * from Table1
exec(@sql)
if @ServiceCode='0'
begin
select col1,col3 from USP_ONLREPORTS_View
end
else
begin
select col4,col5 from USP_ONLREPORTS_View where col1=@ServiceCode
end
GO
which is working fine in query analyser. But when i am trying to set the stored procedure USP_ONLREPORTS as the dataset in SSRS 2005, I am getting the error "Invalid Object Name USP_ONLREPORTS_View.(Microsoft Sql server Error 208)"
Is there any specific way to set the SP which contains view as dataset?
Thanks in Advance
Soundari
March 6, 2009 at 5:53 am
Interesting - if you're dropping and recreating the view in this sproc, then everything else which references the view must do the same - otherwise, the view definition is unknown when it's referenced. If this is the case, then wouldn't it be more efficient to query the tables directly in the sproc?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2009 at 7:29 am
Let add to what Chris is telling you SSRS is saying don't pass anything that temporary to me, because SSRS rejects most code using temp tables so if you create a view and drop it that is not code to pass to SSRS.
Kind regards,
Gift Peddie
March 6, 2009 at 10:40 pm
Hi
Thank You very much for your valuable reply. Now Removed the dropping and creating the view part from SP and its working fine.
Soundari
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply