all groups > sql server reporting services > october 2006 >
You're in the

sql server reporting services

group:

Temp Table Alernative in SRS 2005


Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
10/31/2006 4:01:26 PM
sql server reporting services:
uh can't you just use derived tables instead of temp tables?

or table-valued user-defined functions?

I dont think that #temp tables should be used anywhere for anyting
under any circumstance


[quoted text, click to view]
Temp Table Alernative in SRS 2005 Pete Zerger
10/31/2006 11:25:01 PM


All,

WHile I am able to query the dataset I want, and able do query it. However,
I did so using #temp tables to achieve my desired result, and encounter errors
when attempting to add this dataset in Report Designer in VS2005 (Microsoft
Report Designer: Could not generate a list of fields for the query. Check
the query syntax, or click Refresh Fields on the query toolbar).

**MY QUESTION: Is using temp tables the only way to do this, or should my
query syntax be changed to avoid using temp tables?**

I have the a dataset (from MOM 2005 SystemCenterReporting db) that looks
like this in structure all coming from a single table (view actually in this
case):

ServerName, PerfObjectName, PerfCounterName, PerfInstanceName, SampledValue
SERVER1, LogicalDisk, % Free Space, E:, 20.55
SERVER1, LogicalDisk, Free Megabytes, E:, 255


I would like to return the following in a single line in a SQL RS report
(using the sample dataset from above) like so:

TABLE HEADER: ServerName, % Disk Free Space, Free Space(MB), Drive Name,
Drive Capacity (the last is a calculated field)
TABLE ROW: SERVER1, C:, 20.55, 255, 1240.87


I have posted the query I am using to the following location for viewing
(which may be helpful if you have a MOM 2005 data warehouse at your disposal).
http://www.momresources.org/scripts/query.txt

I have googled the topic of temp tables in SRS and not found any definitive
information as to how I should proceed. Guidance or pointers to authoritative
literature would be welcome.

Regards,

Pete Zerger, MCSE(Messaging), MVP - MOM
URL:http://www.momresources.org
BLOG: http://www.it-jedi.net

Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
11/1/2006 7:25:41 AM
Bruce

I just believe that thigns like derived tables; and views on top of
views are better.

for starters; with .NET it's IMPOSSIBLE to leave a connection open...
right?

so it's basically IMPOSSIBLE to _EVER_ see whats' in a temp table when
you're using them right?

If you used derived tables you would never have that problem

I believe that persistent tables or derived tables are always a better
architecture than temp tables.




[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/1/2006 8:12:31 AM
I use temp tables extensively from stored procedures. If you are using temp
tables from within the generic query designer then that is not a good idea.

Temp tables are absolutely no problem. Do the following:
1. Leave off set nocount on
2. Do not explicitly drop the temp table in your stored procedure.
3. Have the last statement in your SP be your select statement.

If the dataset tab doesn't get the fields then click on the refresh fields
button.

I use temp tables all the time in both Sybase and SQL Server. Lots and lots
and lots of reports. Following above suggestions and you should have no
problems,.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/1/2006 12:04:54 PM
Your whole premise seems to revolve around seeing what's in a temp table
when you are using them. Not sure what that means and it is certainly
incorrect. If that was the case then the use of temp tables in stored
procedures wouldn't work with RS which they certainly do.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
11/2/2006 4:09:40 PM
you CAN see them if you use them within the context of a single batch

but it's impossible to see them-- in the .NET world outside of that.

execute a command that creates a temp table; go to get the results from
the #temp table using a dataReader and it's GONE ALREADY

I don't agree with using different styles of SQL Statements in
different areas.
If a SQL Statement isn't portable enough to use in:

Access
Reporting Services
..NET

Then it's not worth using at all.

RIGHT?

I just don't understand the point; I can do everything you can with
temp tables-- using derived tables... right?

-Aaron



[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
11/2/2006 4:11:55 PM
I just believe that the use of temp tables vs the use of table
variables is directly related to things like:

SMP count
Load on the server

Temp Tables are written to disk. Derived tables and table variables
aren't written to disk.

Using temp tables on a server that already has busy IO is just asking
for problems.
You'd probably have better performance on a temp table on an 8-way
server than you would with table variables.. because temp tables can
use multiple threads; and table variables can't use multiple threads

derived tables don't have a problem using multiple threads though I
believe





[quoted text, click to view]
RE: Temp Table Alernative in SRS 2005 Eric Hu
11/7/2006 4:58:02 PM
I have a similar situation where I am using stored proc that returned temp
table and it executed fine in report designer but it did not populate the
field list under Dataset.

We are in the same boat and I am guess that we will need to use CTE instead
of temp table. I am hoping that we don't have to do that

[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/7/2006 10:41:15 PM
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select

If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). Although what he says makes sense, I have never had
a single problem with temp tables but anyway, here is his explanation: "The
issue with RS is that the rowset of the SP is defined by calling the SP with
SET FMTONLY ON because Temp tables don't get created if you select from the
temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."

Let me know if any of the above works for you.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 Eric Hu
11/8/2006 3:38:02 PM
Hi Bruce,

I have tried what you suggested and it still does not populate the field
list. I have also try to select from temp table in the text command area and
it also does not return any resultset but direct table does. Do you have any
other ideas ? Thanks for your help !

-Eric

[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/8/2006 8:42:52 PM
Wait a minute. Are you trying to query the temp table from RS? Temp tables
should only be in the stored procedure and the last statement should be a
select.

Also, are you going against SQL Server? Using ODBC or OLEDB or native SQL
Server driver?


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 Eric Hu
11/9/2006 11:19:02 AM
I created a report server project using VS 2005 and created a new rdl. Under
the dataset, I created a new dataset using ODBC and add the stored proc
contained the select temp table being the last statement. If the stored proc
contain a direct select from a table the Dataset will populate the field
list. But with select temp table the filed list is empty, which means that I
cannot drop any fields from the temp table to the Report Layout.

Am I doing something wrong ?

Thanks

[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/9/2006 1:29:23 PM
What is the back end database?

I do this against Sybase and SQL Server all the time. Heck, I don't know
when I do a stored procedure without a temp table. Usually I create stored
procedures due to complexity and that usually means a temp table someplace.


Bruce Loehle-Conger

[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
11/9/2006 3:47:44 PM
yeah 'dont use temp tables'


[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Eric Hu
11/10/2006 12:52:01 PM
The back end database is SQL 2000. Is that the issue maybe ?

[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/10/2006 3:04:23 PM
No. Not at all (before upgrading to SQL 2005 I was doing this against SQL
2000).

If you can post your code (or the vital parts with the temp table).


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Temp Table Alernative in SRS 2005 Eric Hu
11/13/2006 2:43:02 PM
Hi Bruce,

Here is a sample of my stored proc structure.

CREATE PROCEDURE procReportHist


@Users as varchar(10)='all',
@SAUser as int=0

AS

--set nocount on
Set FMTONLY Off



--create result table


if object_id('tempdb..#grad_results') is not null
drop table #grad_results


SELECT 0 as t1, 0 as t2, null as inv1, null as inv2, null as inv3, null as
inv4, null as grad_retain_t, 0 as tpact1 into #grad_results



update #grad_results
set tpact1 = (SELECT count(distinct Student.iStudentID)
FROM Student
WHERE (Student.dGradDate BETWEEN CONVERT(DATETIME, @YearStart1,
102) AND
CONVERT(DATETIME, @lYearEnd1, 102)) AND
(Student.iMajorID = 44))

select * from #grad_results

GO
[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 dbahooker NO[at]SPAM hotmail.com
11/13/2006 3:32:42 PM
Bruce;

you admit right there the shortcomings of these so called 'temp tables'





[quoted text, click to view]
Re: Temp Table Alernative in SRS 2005 Bruce L-C [MVP]
11/13/2006 4:56:32 PM
Two things. I always have a return statement (not sure if that would make a
difference). The other thing is your
if object_id('tempdb..#grad_results') is not null drop table #grad_results

I never do anything as far as management the temp tables (and I have stored
procedures with multiple temp tables, some created explicitely some created
using select into). I alway let SQL Server manage the temp table lifetime.
Try removing the if object_id code. Also at the return before the go.


--
Bruce Loehle-Conger