Temp tables for reports -- sql server reporting services

Scott Allen
8/7/2004 2:22:09 PM

Hi James:

With SQL Server prefix the table name with a single '#' character.
This gives you a 'local temporary table'. Local temp tables are only
visible to the current connection. You could have 5 users on 5
connections executing the same query which puts temporary results into
a table named #FooBar, and each one will see a distinct table - they
won't interfere.

HTH,

--
Scott
http://www.OdeToCode.com

On Sat, 7 Aug 2004 10:48:23 -0500, "James Foye" <jimfoye2@hotmail.com>
[quoted text, click to view]

Jéjé
8/7/2004 3:52:13 PM

if your data are in Access and you don't access this information from any
other way (but you can use a linked server in SQL Server)
I suppose your user click a button to generate the report. (not access the
report online directly)

Then what you do is (when the user click "generate"):
1. create you temporary table
2. call RS to generate the report and save it as PDF for example (report
based on the temporary table)
3. destroy your temporary table
4. open the PD file, or the snapshot version on the web

also you can save the report as a snapshot in RS.

over way, the statement in RS is not limited to "Select" commands; so you
can create complex queries directly in the query definition in your report
(like creating a temporary table in Access with a create table command or a
select into, then executing an SQL statement on the temporary table); but
I've not tested this.

"James Foye" <jimfoye2@hotmail.com> a écrit dans le message de
news:eifI7YJfEHA.2396@TK2MSFTNGP11.phx.gbl...
[quoted text, click to view]

James Foye
8/8/2004 11:27:02 AM

Thanks. Can Reporting Services see a table in tempdb? If so, how would I
link fields in the source to fields in the report in the designer? In
Access, I would actually create the table in the mdb, and the Access report
designer can of course see it. I'm not sure how this would work in Reporting
Services.

The other thing I wasn't sure about with respect to temporary tables is that
in Access, each user is using the temp table on his local HD, so I'm not
concerned about disk space issues. On the server, if I have a lot of users
doing a report that uses a temp table, then tempdb is filling up, and of
course this potentially impacts other users who are simply doing sorts or
something.

Jim

[quoted text, click to view]

Ravi Mumulla (Microsoft)
8/8/2004 12:43:58 PM

If you use temp tables, you'd have to populate the fields list manually. Use
table variables if you can. If you use table variables in your stored
procedures, the fields list is automatically populated for you. Here's a
simple example of how to use a table variable in your stored procedures:

++++++++++++++++++++++++++
create proc temptablevar AS
begin
declare @testtable table (title varchar(256), type varchar(256), price
decimal)
insert into @testtable select title, type, price from titles
select * from @testtable
end

++++++++++++++++++++++++++

On your second question, there are two types of temp tables in SQL Server:
Global and Local. If you create a **local** temp table (i.e., if the name of
the temp table is prefixed with one pound sign "#"), it is always local in
scope, i.e., other users don't see it. On the other hand, if you use a
**global** temp table (i.e., if the name of the temp table is prefixed with
two pound signs), it is global in scope (visible across all sessions.)

Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp?frame=true
and http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B305977 for
information on table variables. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp?frame=true
for details on local and global temp tables.

--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.
[quoted text, click to view]

James Foye
8/8/2004 4:21:57 PM

[quoted text, click to view]

Ah, thanks! This could be what I'm looking for. Since I haven't started
actually playing around with Reporting Services, I didn't realize I could
use a stored procedure as my data source. Perfect.

[quoted text, click to view]

Yes, I understand the scope. I just meant that if there is heavy use of
tempdb by people running reports, it will impact other SQL Server users. In
Access, since the .mdb is local for each user, there would be much less
impact.

Thanks again.
Jim

Scott Allen
8/8/2004 9:54:02 PM

Hi Jim:

<snip>
[quoted text, click to view]

The good news is SQL Server had a good lead over Access in terms of
scalability. It's impossible to predict without knowing the hardware
environment and how you define "heavy use" of tempdb, so the safest
plan is to do some measurements before you get too far along. Even if
you see an impact, you'll have more choices for optimizing the
workload than you did with Access.

--
Scott

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Temp tables for reports