Commands Used - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Commands Used

Author

Topic

hh1234
Starting Member

30 Posts

Posted - 2011-08-24 : 12:31:47

I am trying to figure out a SQL program that is used within SCCM 2007.

SELECT DISTINCT sys.Resource_Domain_OR_Workgr0 AS 'Computer Domain', sys.User_Name0 as 'UserName', sys.Netbios_Name0 as 'Workstation', COMP.Manufacturer0 as 'Manufacturer', COMP.Model0 as 'Model', BIOS.SerialNumber0 as 'Workstation Serial#'FROM v_R_System sys JOIN v_GS_PC_BIOS BIOS on Sys.ResourceID = BIOS.ResourceID JOIN v_GS_COMPUTER_SYSTEM COMP on Sys.ResourceID = COMP.ResourceID WHERE sys.Netbios_Name0 NOT LIKE '1%'
AND sys.Resource_Domain_OR_Workgr0 LIKE 'ACME' AND Operating_System_Name_and0 LIKE '%Workstation%' ORDER BY sys.Netbios_Name0

This does not work and I'm not sure why. In SCCM you can see the SQL reporting build tool. You see the VIEWS and COLUMNS. What I don't understand is where you find things like COMP.Manufacturer0 and BIOS.SerialNumber0. Then are not views or columns. Where do you find that info? And does anyone know why this code fails?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:35:29

COMP,BIOS etc are table alises ie short names for tables/views v_GS_COMPUTER_SYSTEM and v_GS_PC_BIOS respectively.
to know why it fails can you post error message it gives?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-08-24 : 13:00:18

Hi. When I run this from within the SCCM Admin console (under reports) I get this error.

The website cannot display the page
HTTP 500
Most likely causes:
The website is under maintenance.
The website has a programming error.

What you can try:
Refresh the page.

Go back to the previous page.

More information

This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:27:08

oh this is an application error. any idea what was error thrown from query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-08-24 : 13:38:19

Hi. I don't follow your question. If you are asking did the SQL report give an error when I saved it the answer is NO. Only when I run it does it fail.

But I have copy/pasted other SQL statements from SMS and they run fine in SCCM. But this one fails.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:41:00

i was asking whether you managed to run this query in ssms and what error it gave?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-08-24 : 13:47:49

Yes, I run this within SCCM 2007 R3. I created a custom report and then ran it. When I run it I get the error I posted earlier. Any ideas how to get rid of the error and have it display my data?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:49:47

nope first try running the query in sql management studio and see what error it returns if at all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-08-24 : 16:18:50

Okay, I ran the SQL statement inside of SQL. I do get an error. It says:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'v_R_System'.

I have a tool that lists all of the views in SCCM and v_R_System is listed. It says the following about this view.

Lists all discovered system resources by resource ID, resource type, whether the resource is a client, what type of client, client version, NetBIOS name, user

so I'm not sure why it fails on this line of code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 01:37:26

that means that view is either not in same db or it might be in some other schema which you dont have access

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hh1234
Starting Member

30 Posts

Posted - 2011-08-25 : 11:25:21

Thank you everyone for your help. We found the issue. On our SCCM central site we had to change a buffer setting in a file. C:\inetpub\wwwroot\SMSReporting_ABC\REPORT.ASP In this file we had to change the Response.Buffer to False as show below. This allowed the report to run and this report returns almost 9,000 records.

<%@LANGUAGE="VBScript"%>
<%Option Explicit
Response.Expires = 0
Response.Buffer = False
Dim fMsg, sDll

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources