SMS Web Reports - Use of temporary tables 1

[98VFR]

Hi all!

I'm trying to figure out how to use temporary tables from within SMS 2003 Reporting. I can do it directly in SQL, such as through Query Analyzer, but when I use the same SQL statements in my web report, it doesn't work. Here's the basic structure of my statement:

=================================
SELECT <some fields>
INTO #TEMP1
FROM <table name>
WHERE <criteria>
ORDER BY <fields>

SELECT <some fields>
INTO #TEMP2
FROM <table name>
ORDER BY <fields>

SELECT #TEMP2.Field1,
#TEMP1.[Field1],
#TEMP1.[Field2],
#TEMP1.[Field3]
FROM #TEMP1 INNER JOIN #TEMP2 ON #TEMP1.Field1 = #TEMP2.Field1
WHERE <criteria>
ORDER BY #TEMP2.Field1
=================================

The SMS web report returns 0 records for each of the first two SELECT statements, and doesn't display anything at all for the third, which are the combined results that I'm actually looking for.

Again, my statement works perfectly in QA, so it's something with the way SMS handles temp tables. I know that SMS allows for multiple select statements and displays the results separately on the same page, but I can't figure out how to use the temp tables. Does anyone have any suggestions or thoughts?

Thanks in advance.

 

[tbrennans]

I believe the problem is that the web reports and console in general all use SQL views, so thats why you cant do this through that source, unless you created a new view I suppose. Never went there before

 

[johnnyb99]

I used the following statements in a report display the AD OU in which a system resides.

The AD OU path is parsed into several entries with the last one containing the full path. I selected the last entry by using "Top 1", sort in descending order, and building a temp table with the reversed result, which then allowed me to select from the temp table and parse out the last OU name.

Set nocount on

Select Reverse((Select Top 1 sysou.System_OU_Name0 From v_RA_System_SystemOUName sysou Where sysou.ResourceID=sys.ResourceID Order By sysou.System_OU_Name0 Desc)) As OU, sys.Netbios_Name0 as Computer, Case When sys.Client0 = 1 Then 'Yes' Else 'No' End As SMSClient
Into #myWorkTable
From v_R_System sys

Select Reverse(Left(OU,CharIndex('/',OU)-1)) As 'Organizational Unit', Computer, SMSClient As 'SMS Client' From #myWorkTable
Order By 'Organizational Unit', Computer

Drop Table #myWorkTable

 

[98VFR]

Okay, I've got it working.

The "SET NOCOUNT ON" takes care of the display problem, where it was showing two empty recordsets where I had created the temp tables.

Once I had done that I got an error for my third table (not sure why it didn't show before), and it had to do with a join to a view -- permissions of some sort. But, I was able to find the same data in System_DISC so took care of it from there, and now my report is working perfectly.

Thanks for the help, johnnyb99.

 

[globalstrata]

I have found that sometimes the web reports are not as good as I want them to be so I am currently working on getting a lot of my reports in SQL 2000 Reporting Services:

http://www.microsoft.com/sql/reporting/default.mspx.

This is a free add-on for SQL License Owners that allows the creation of web reports. I figure since MOM already uses these types of reports and so will the next version of SMS, then might as well getting acustom. ;o)


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources