April 18, 2012 at 12:15 pm
I have to create a report by using a stored procedure. stored procedure contains temporary table .At the time of creating a report. I am getting an error message : Invaild object name . Doesn't the report support the temporary tables. If so , is there any alternative way to create the report. I tried by using table variables .. my senior dba told me not use table variables ..
April 18, 2012 at 12:24 pm
Pretty sparse on details. There is no reason that a stored proc called from a report can't use temp tables. Does the proc work when run outside the report?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 12:26 pm
I have several reports that rely on procs that use temp tables or table variables. We would need more info on this question to be able to answer it fully.
SSRS does support temp tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 12:29 pm
Not enough information to really help.
Read the article at http://qa.sqlservercentral.com/articles/Best+Practices/61537/ and follow the instructions on what information to post and how to post it. The more information you provide, the better answers you will get.
Will also need to see the code for your stored procedure.
April 18, 2012 at 12:37 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter proc [dbo].[CdoeOrders_Test]
@userid nvarchar(20) ,
@StartDate dateTime ,
@EndDate dateTime
As
select b.User_ID ,d.First_Name+' '+ d.Last_Name as FullName,a.Cust_Name ,a.Cust_Num,SUM( b.PO_AMT) as TotalOrderAmount ,COUNT(*) as TotalOrders
into #Orderstastics
from ORDER_CANADA.dbo.ORDHEAD b
inner join BIDMAN_COMMON.dbo.LeCustomer a
on a.LE=b.LE and b.CUST_ID = a.Cust_Num
inner join ORDER_CANADA.dbo.NEG_JOB c
on b.NEG_NUMBER= c.NEG_NUMBER
inner join BIDMAN_COMMON.dbo.USER_PROFILE d
on c.OWNER_ID = d.SALESMAN
where b.NEG_NUMBER not like '1000%' and SourceSystem ='CDOE' and (c.OWNER_ID = @userid or @userid is NUll )
and CONVERT(CHAR(10),b.Created,120) between CONVERT( CHAR(10),@Startdate,120) and Convert ( CHAR(10),@EndDate,120)
group by a.Cust_Name,a.CUST_Num,b.USER_ID,d.FIRST_NAME,d.LAST_NAME
insert into #Orderstastics
select b.User_ID ,d.First_Name+' '+ d.Last_Name as FullName , a.Cust_Name ,a.Cust_Num, SUM( b.PO_AMT)as TotalOrderAmount , Count(*) as TotalOrders
from ORDER_GLOBAL.dbo.ORDHEAD b
inner join BIDMAN_COMMON.dbo.LeCustomer a
on a.LE=b.LE and b.CUST_ID=a.Cust_Num
inner join ORDER_GLOBAL.dbo.NEG_JOB c
on b.NEG_NUMBER= c.NEG_NUMBER
inner join BIDMAN_COMMON.dbo.USER_PROFILE d
on b.LE = d.LE
where b.NEG_NUMBER not like '1000%' and SourceSystem ='CDOE' and (c.OWNER_ID = @userid or @userid is NUll )
and CONVERT(CHAR(10),b.Created,120) between CONVERT( CHAR(10),@Startdate,120) and Convert ( CHAR(10),@EndDate,120)
group by a.Cust_Name,a.CUST_Num,b.USER_ID,d.FIRST_NAME,d.LAST_NAME
insert into #Orderstastics
select b.User_ID ,d.First_Name+' '+ d.Last_Name as FullName ,a.Cust_Name ,a.Cust_Num,SUM( b.PO_AMT) as TotalOrderAmount ,Count(*) as TotalOrders
from ORDER_MAN.dbo.ORDHEAD b
inner join BIDMAN_COMMON.dbo.LeCustomer a
on a.LE=b.LE and b.cust_id = a.Cust_Num
inner join ORDER_MAN.dbo.NEG_JOB c
on b.NEG_NUMBER= c.NEG_NUMBER
inner join BIDMAN_COMMON.dbo.USER_PROFILE d
on b.LE = d.LE
where b.NEG_NUMBER not like '1000%' and SourceSystem ='CDOE' and (USER_ID = @userid or @userid is NUll )
and CONVERT(CHAR(10),b.Created,120) between CONVERT( CHAR(10),@Startdate,120) and Convert ( CHAR(10),@EndDate,120)
group by a.Cust_Name,a.CUST_Num,b.USER_ID,d.FIRST_NAME,d.LAST_NAME
insert into #Orderstastics
select b.User_ID , d.First_Name+','+ d.Last_Name as FullName,a.Cust_Name ,a.Cust_Num, SUM( b.PO_AMT) as TotalOrderAmount , Count (*) as TotalOrders
from ORDER_OEM.dbo.ORDHEAD b
inner join BIDMAN_COMMON.dbo.LeCustomer a
on a.LE=b.LE and b.CUST_ID=a.Cust_Num
inner join ORDER_OEM.dbo.NEG_JOB c
on b.NEG_NUMBER= c.NEG_NUMBER
inner join BIDMAN_COMMON.dbo.USER_PROFILE d
on b.LE = d.LE
where b.NEG_NUMBER not like '1000%' and SourceSystem ='CDOE' and (USER_ID = @userid or @userid is NUll )
and CONVERT(CHAR(10),b.Created,120) between CONVERT( CHAR(10),@Startdate,120) and Convert ( CHAR(10),@EndDate,120)
group by a.Cust_Name,a.CUST_Num,b.USER_ID,d.FIRST_NAME,d.LAST_NAME
Select * from #Orderstastics
order by USER_ID
This is my stored procedure .. I can execute the stored procedure in management studio. But , when I am creating a report . I am getting an error message invalid object name ( #Orderstatics) . I tried by using global temporary table.
April 18, 2012 at 12:39 pm
Is that the complete and unabridge error message or is there more to it?
April 18, 2012 at 12:42 pm
How are you calling the stored procedure from SSRS?
One of the things I do differently than you (and since they work on my end), is to list out the columns rather than do a Select *. I also create the table and then perform insert into for all insertions.
If that doesn't work, the next possibility is to call the proc from a second proc. Then use the calling proc in the report.
Last thing, I noticed that your error message has a misspelled temp table different from the temp table used in the proc. Maybe it is just a typo - but thought I would bring that up.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 12:45 pm
error Message :Invalid object name '#Orderstatics'. (.Net SqlClient Data Provider). I am getting
April 18, 2012 at 12:49 pm
Sounds like you are not calling the stored proc then because the temp table in the proc is
#Orderstastics
Either that, or you have an error within the proc that you corrected when posting the query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 12:54 pm
It is a typo error invalid Object Name #Orderstastics
April 18, 2012 at 12:59 pm
So the question remains, how are you calling the stored procedure in your dataset?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 1:08 pm
ADD-->> Report --> NewItem -->> Report -->> Dataset -->>commandtype--'Storedprocedure'-->>Querystring--'Procedurename'
April 18, 2012 at 1:12 pm
kodalisridevi (4/18/2012)
error Message :Invalid object name '#Orderstatics'. (.Net SqlClient Data Provider). I am getting
This is the complete error message? No line number, nothing on any additional line? Anything in any of the error logs?
April 18, 2012 at 1:16 pm
Nope , I have Copied the error message
April 18, 2012 at 1:24 pm
kodalisridevi (4/18/2012)
ADD-->> Report --> NewItem -->> Report -->> Dataset -->>commandtype--'Storedprocedure'-->>Querystring--'Procedurename'
And when you test the dataset here, it errors?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply