syntax issue - 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

syntax issue

Author

Topic

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 11:20:52

I have this code that is inside of a SCCM Report.

SELECT Sys.Netbios_Name0 as 'Computer Name',IPAddr.IP_Addresses0 as 'IP Address',Sys.User_Domain0 as 'User Domain', Sys.User_Name0 as 'UserName', Comp.Manufacturer0 as 'Make', Comp.Model0 as 'Model', Process.Name0 as 'Processor', BIOS.SerialNumber0 as 'Serial#', Memory.TotalPhysicalMemory0/1000 as 'RAM (MB)', Sys.Operating_System_Name_and0 as 'Operating System', OS.CSDVersion0 as 'Service Pack', Status.LastHWScan as 'Last H/W Scan', OS.InstallDate0 as 'Install_Date', OS.LastBootUpTime0 as 'Last Bootup', Sys.AD_SITE_NAME0 as 'Location'
FROM v_R_System Sys
LEFT JOIN v_GS_COMPUTER_SYSTEM Comp on Sys.ResourceID = Comp.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS on Sys.ResourceID = BIOS.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY Memory on Sys.ResourceID = Memory.ResourceID
LEFT JOIN v_GS_PROCESSOR Process on Sys.ResourceID = Process.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS Status on Sys.ResourceID = Status.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OS on Sys.ResourceID = OS.ResourceID
LEFT JOIN dbo.v_RA_System_IPAddresses IPAddr ON SYS.ResourceID = IPAddr.ResourceID
WHERE OS.LastBootUpTime0 <> ' ' and (Sys.Name0 like '0___0-[PT]C-%' or Sys.Name0 like '63___-[PT]C-%')
ORDER BY Sys.Netbios_Name0

This works and I get all the machines from one domain (based on the Sys.Name0 like statement)

When I REMOVE this line "WHERE OS.LastBootUpTime0 <> ' ' and (Sys.Name0 like '0___0-[PT]C-%' or Sys.Name0 like '63___-[PT]C-%')"

and run the report it bombs and gives me a error. (The website cannot display the page....)

Why? I need to run this and have it return All machines from all domains. What am I failing to do in this code to get my results?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 11:27:11

might be the you're trying to cast some return field in your front end and removing the where brings up a record with invalid cast value on one of them.

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

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 11:34:32

I've tried the following.

WHERE OS.LastBootUpTime0 <> ' ' same error
WHERE OS.LastBootUpTime0 <> ' ' and Sys.Name0 <> ' ' same error
I've removed the entire WHERE line and I get the same error.

So how do I fix this? :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 11:40:28

is the sql code throwing any error? if yes, post the error message

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

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 11:45:07

Yes and no. I run this from within SCCM 2007. It is a report. When run I get this returned to me.

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.

But, if I run it and I Include this last two lines of code it all works.
WHERE OS.LastBootUpTime0 <> ' ' and (Sys.Name0 like '0___0-[PT]C-%' or Sys.Name0 like '63___-[PT]C-%')
ORDER BY Sys.Netbios_Name0

The issue is this line narrows the results down to machines with a certain NetBIOS name and I don't want that. I want all machines returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 12:14:22

sorry but if possible can you run this query in SSMS and post error if at all any returned. This will help us t segregate whether its a db error or application error.

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

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 12:31:15

I tried something different. I logged onto the SQL server. I ran my code and it returned (6800 row(s) affected). I then Removed ((and (Sys.Name0 like '0___0-[PT]C-%' or Sys.Name0 like '63___-[PT]C-%'))) and ran it again and it worked!!! it returned (25313 row(s) affected)

So now my question is why does it work in SQL but fail when run from within the SCCM console? We had issues getting SCCM to return more than 10,000 records but we did the following which worked.

In C:\inetpub\wwwroot\SMSReporting_ABC open up Report.asp and change the Response.Buffer to False. This will allow your reports to return more than 10,000 records

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

This worked and we can get other reports to return over 10,000 records. So why does this one report fail?

Also, using this code above how can I specify the domain? Right now, when run on the SQL server, it returns machines from all domains. How can I say "Domain=USA" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 12:37:39

so everything is fine from SQL Server. I'm not sure I'll be able to suggest anything on SCCM side as I've no idea about that application. Probably you can contact someone who has idea on SCCM to understand whats happening inside

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

hh1234
Starting Member

30 Posts

Posted - 2011-09-23 : 12:47:49

I am guessing it is the fact that this report returns over 25,000 records. but it worked and I was able to export the information out of the SQL server and give it to the people who needed it !!

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 12:49:55

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources