Report Services doesn't like temp table??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Report Services doesn't like temp table???

Author

Topic

lliu
Starting Member

7 Posts

Posted - 2005-02-15 : 12:26:16

I tried to create report with Report Services but if my stored procedure use temp table (ex: #Temp), I'll get the error "There is an error in the query. Invalid object name '#Temp'). But if the stored procedure doesn't include temp table, everything will be fine, Report Services will go through every steps to create report.

I can execute the stored procedure on Query Analyzer and get the query result, everything looks fine, but if I click the option ¡°Display estimated execution plan¡± on Query menu of Query Analyzer, I got same message ¡°Invalid object name '#temp'¡±.

I tried many times on our existing stored procedures(they are all working fine on our backend and client backend) on different Server, it¡¯s happened to any stored procedures which include temp table. Is it could be same problem which I got from Report Services? Does anybody can tell me what¡¯s going on?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-15 : 12:39:43

I have a report inside Reporting Services that calls a stored procedure which uses a temp table. I don't have any problems with it. You can't use the display estimated execution plan with temp tables, you'd have to use the actual execution plan to view it.

Tara

lliu
Starting Member

7 Posts

Posted - 2005-02-15 : 13:52:48

Yes, after I executed the stored procedure I can use execution plan to view it. But why I cannot use "Display estimated execution plan", it works to all other stored procedures that do not use temp tables.
Anyway, it's not most what I want to know.

I just want to know why I cannot create report with the stored procedure including temp table because I have no problem to create report with other stored procedures without temp tables.

Thank you response.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-15 : 13:55:14

You can though. I'm using stored procedures with temp tables. In fact, I have one that has 3 temporary tables in it. Maybe you should post your stored procedure so that we can figure out what is going on.

Tara

lliu
Starting Member

7 Posts

Posted - 2005-02-15 : 14:05:49

quote:
Originally posted by tduggan

You can though. I'm using stored procedures with temp tables. In fact, I have one that has 3 temporary tables in it. Maybe you should post your stored procedure so that we can figure out what is going on.

Tara

It's happened to all of my stored procedure which have temp tables inside.
This is only a most simple one for testing.

CREATE PROCEDURE RPTLong_test
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

create table #temp
(
mGroup varchar(20),
Provid varchar(20),
FName varchar(20),
LName varchar(20)
)

insert #temp
select distinct g.txtGroup, p.txtproviderid,p.txtfirst,p.txtlast
from tbProviderGroup g
inner join tbProvider p on g.txtGroupid = p.txtGroupid
left join tbprovideraddress a on p.intProviderid = a.intProviderid

select * from #temp
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-15 : 15:07:58

I'm sure there is more to your stored proc as what you have doesn't make sense to use a temporary table.

[EDIT] Ignore the above as I realized after I posted it that you are just testing with it.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-15 : 15:24:15

Try this in a report:

CREATE PROC SomeProc
AS
SET NOCOUNT ON

CREATE TABLE #Temp (Column1 int NOT NULL)

INSERT INTO #Temp VALUES(1)
INSERT INTO #Temp VALUES(2)

SELECT Column1 FROM #Temp

DROP TABLE #Temp

RETURN

GO

Tara

lliu
Starting Member

7 Posts

Posted - 2005-02-15 : 15:59:17

Although I knew what I would get but I tried again, I got same message.

It could be some other reason.

Thanks for your response again.

phish_brady
Starting Member

1 Post

Posted - 2005-02-15 : 21:01:23

I've used temp tables and has seen the error. I build the solution and it eliminates the issue.

lliu
Starting Member

7 Posts

Posted - 2005-02-16 : 14:12:50

quote:
Originally posted by phish_brady

I've used temp tables and has seen the error. I build the solution and it eliminates the issue.

I tried to rebuilt the solution but it didn't work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-16 : 15:24:36

The only thing that I can think of to cause this is that the dataset isn't setup correctly. In the command type field, did you select stored procedure and just put the name of it in the next field? When you click the run button in the dataset tab, does it run the stored procedure successfully?

Tara

lliu
Starting Member

7 Posts

Posted - 2005-02-18 : 11:55:03

quote:
Originally posted by tduggan

The only thing that I can think of to cause this is that the dataset isn't setup correctly. In the command type field, did you select stored procedure and just put the name of it in the next field? When you click the run button in the dataset tab, does it run the stored procedure successfully?

Tara

Thank you Tara.

I have walked out of the trouble. There is no difference to use Stored Procedure(Command Type) and type stored procedure name in next field or choose Text(Command Type) and put Exec stored_procedure_name there. I tried both way and got error with different message, but it’s not big deal, just following the message and press refresh button on data tab, all of fields would show up and I got the ball rolling.

Liu.

Fletch
Starting Member

29 Posts

Posted - 2005-02-21 : 11:42:25

I seem to be having a very similar problem.
I am using three temporary tables in a stored procedure and am attempting to use the sp as a dataset for a matrix. When I add the dataset I'm told...
Could not generate a list of fields for the query.
..Invalid object name '#temptable01'

Adding the fields and parameters manually leaves me no better off.

any advice much appreciated.

lliu
Starting Member

7 Posts

Posted - 2005-02-25 : 10:34:55

quote:
Originally posted by Fletch

I seem to be having a very similar problem.
I am using three temporary tables in a stored procedure and am attempting to use the sp as a dataset for a matrix. When I add the dataset I'm told...
Could not generate a list of fields for the query.
..Invalid object name '#temptable01'

Adding the fields and parameters manually leaves me no better off.

any advice much appreciated.

I have no idea about the reason of the problem.
My suggestion is: not to use report wizard, just choose report option when you add new item then fill all of required information in Dataset dialogue box. If you get error message when you click OK, click Refresh button ( in the tool bar beside on Dataset dropdown) then check out if you get all of fields.
I got it working by this way but not sure it works on your side.

troutbum
Starting Member

1 Post

Posted - 2005-02-25 : 10:40:10

1. Right Click Reports Folder
2. Add New Item
3. Report
4. Under the Dataset Drop down add new dataset
5. Command Type : Stored Procedure
6. Query String : your stored procedure name
7. Select Fields Tab (Enter all fields returning from query)
If using parameters
8. Select Parameters Tab (Enter your parameters)
9. Go to Layout view of Report
10. Right Click select Report Parameters
11. Create paramters
12. Select Data Tab
13. Select Parameters > under value field select parameters
14 Add Table with fields
15 Select Preview

Hope this helps

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-05-16 : 04:16:25

Hi, nice work there thanks.

I have a question thou that I'd appreciate help with.

I have an sp that returns 4 columsn like this
ID InfoA InfoB InfoC
1 xxxxxxxxxxxxxxxxxxxxxx
1 xxxxxxxxxxxxxxxxxxxxxx
2 xxxxxxxxxxxxxxxxxxxxxx
3 xxxxxxxxxxxxxxxxxxxxxx
3 xxxxxxxxxxxxxxxxxxxxxxx

My problem is I get 4 boxes where I can select the values in that the sp returns but I want a list where all the values are listed like above.

What am I doing wrong?

Thanks for help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 12:42:33

quote:
Originally posted by IceDread

Hi, nice work there thanks.

I have a question thou that I'd appreciate help with.

I have an sp that returns 4 columsn like this
ID InfoA InfoB InfoC
1 xxxxxxxxxxxxxxxxxxxxxx
1 xxxxxxxxxxxxxxxxxxxxxx
2 xxxxxxxxxxxxxxxxxxxxxx
3 xxxxxxxxxxxxxxxxxxxxxx
3 xxxxxxxxxxxxxxxxxxxxxxx

My problem is I get 4 boxes where I can select the values in that the sp returns but I want a list where all the values are listed like above.

What am I doing wrong?

Thanks for help!

Do you mean you want all 4 of them in one column?

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-05-19 : 02:31:50

Hey

No, I want to have a table with 4 columns with the result from the sp. What I have is four dropdownlists where I can select the result in, which is not what I want to have.

Thanks for help.

IceDread
Yak Posting Veteran

66 Posts

Posted - 2008-05-19 : 03:00:25

Now I know what I did wrong.

I should not have created parameters. And to get it to work, just removing them didnt do the trick, hade to remove the entire dataset and then it worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 03:34:56

quote:
Originally posted by IceDread

Hey

No, I want to have a table with 4 columns with the result from the sp. What I have is four dropdownlists where I can select the result in, which is not what I want to have.

Thanks for help.

You just need to add a new dataset to yourreport and give the command type as stored procedure. Give your stored procedure name and click run (exclamation mark) and populate your dataset. Then go to layout tab,place a table and drag and drop fields from dataset tab on left. run report in preview tab and you can see the data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources