MID Server reported error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.

[joshehren]

Hello.   I get the above error when trying to run the query below.   I don't understand the message because there is no "Where" in the query.  

The original query is in black below, and it runs fine without my red adjustments:

SELECT

v_GS_COMPUTER_SYSTEM.Name0 as name,

v_GS_COMPUTER_SYSTEM.domain0 as domain,

v_GS_COMPUTER_SYSTEM.Model0 as model,

v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,

v_GS_COMPUTER_SYSTEM.ResourceID,

v_GS_COMPUTER_SYSTEM.UserName0 as username,

v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,

v_GS_SYSTEM.SystemRole0 as SystemRole,

v_GS_OPERATING_SYSTEM.Caption0 as caption,

v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,

v_GS_WORKSTATION_STATUS.LastHWScan,

v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,

v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,

v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,

v_r_system.User_Name0 as LastLoggedUser,

v_r_system.Last_Logon_Timestamp0 as LastLogOnDate,

v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.topConsoleUser0 as TopConsoleUser,

v_CH_ClientSummary.ClientActiveStatus as Active,

max(v_RA_System_SystemOUName.System_OU_Name0) as OU

FROM [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_COMPUTER_SYSTEM

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_RA_System_SystemOUName ON v_GS_COMPUTER_SYSTEM.ResourceID = v_RA_System_SystemOUName.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_r_system ON v_GS_COMPUTER_SYSTEM.ResourceID = v_r_system.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID

LEFT JOIN [SQLSCCM01.UNIVERSITY.LIBERTY.EDU].CM_MAX.dbo.v_CH_ClientSummary ON v_GS_COMPUTER_SYSTEM.ResourceID =   v_CH_ClientSummary.ResourceID

Group by v_GS_COMPUTER_SYSTEM.Name0,

v_GS_COMPUTER_SYSTEM.domain0,

v_GS_COMPUTER_SYSTEM.Model0,

v_GS_COMPUTER_SYSTEM.Manufacturer0,

v_GS_COMPUTER_SYSTEM.ResourceID,

v_GS_COMPUTER_SYSTEM.UserName0,

v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,

v_GS_SYSTEM.SystemRole0,

v_GS_OPERATING_SYSTEM.Caption0,

v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_GS_WORKSTATION_STATUS.LastHWScan,

v_GS_PC_BIOS.SerialNumber0,

v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0,

v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0,

v_GS_SYSTEM_ENCLOSURE.SerialNumber0,

v_GS_BASEBOARD.SerialNumber0,

v_r_system.User_Name0,

v_r_system.Last_Logon_Timestamp0,

v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.topConsoleUser0,

v_CH_ClientSummary.ClientActiveStatus

Thanks in advance for your help.

josh

[Chuck Tomasi]

This may help:

Adding where clause to SCCM 2012 Software to ignore updates...

Check your column names/values (or whatever.)

That's all I could find searching the community for similar error messages.

[joshehren · Accepted solution]

I ended up adjusting the query so that I used a common table expression instead of grouping all of the fields at the end of the sql statement.   This works:

WITH CTE as (select max(System_OU_Name0) as OU,

                                      ResourceID

                                              from v_RA_System_SystemOUName

                                              group by ResourceID)

SELECT distinct

v_GS_COMPUTER_SYSTEM.Name0 as name,

v_GS_COMPUTER_SYSTEM.domain0 as domain,

v_GS_COMPUTER_SYSTEM.Model0 as model,

v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,

v_GS_COMPUTER_SYSTEM.ResourceID,

v_GS_COMPUTER_SYSTEM.UserName0 as username,

v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,

v_GS_SYSTEM.SystemRole0 as SystemRole,

v_GS_OPERATING_SYSTEM.Caption0 as caption,

v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,

v_GS_WORKSTATION_STATUS.LastHWScan,

v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,

v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,

v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,

v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,

v_r_system.User_Name0 as LastLoggedUser,

v_r_system.Last_Logon_Timestamp0 as LastLogOnDate,

v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.topConsoleUser0 as TopConsoleUser,

v_CH_ClientSummary.ClientActiveStatus as Active,

CTE.OU

FROM v_GS_COMPUTER_SYSTEM

LEFT JOIN v_RA_System_SystemOUName ON v_GS_COMPUTER_SYSTEM.ResourceID = v_RA_System_SystemOUName.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID

LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID

LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID

LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID

LEFT JOIN v_r_system ON v_GS_COMPUTER_SYSTEM.ResourceID = v_r_system.ResourceID

LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID

LEFT JOIN v_CH_ClientSummary ON v_GS_COMPUTER_SYSTEM.ResourceID =   v_CH_ClientSummary.ResourceID

LEFT JOIN CTE on v_GS_COMPUTER_SYSTEM.ResourceID = CTE.ResourceID

[Pravin17]

Hi Josh,

I am also facing the same issue & i have corrected that with common table expression but after few successful runs it is again throwing the same error though there is no use of AND in my SQL. Could you please suggest anything here?

MID Server reported error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'AND'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatem

WITH CTE as (select v_GS_SYSTEM.SystemRole0, ResourceID

                                              from v_GS_SYSTEM)

SELECT * FROM

(SELECT

v_GS_Add_Remove_Programs.GroupID + 32000000 as GroupID,

displayname0 as DisplayName,

version0 as Version,

installdate0 as InstallDate,

prodID0 as ProdID,

publisher0 as Publisher,

v_GS_Add_Remove_Programs.TimeStamp as TimeStamp,

v_GS_Add_Remove_Programs.RevisionID as revisionid,

v_GS_Add_Remove_Programs.ResourceID as resourceID,

CTE.SystemRole0

FROM v_GS_Add_Remove_Programs

LEFT JOIN CTE ON v_GS_Add_Remove_Programs.ResourceID = CTE.ResourceID WHERE CTE.SystemRole0 = 'Workstation'

UNION

SELECT

v_GS_Add_Remove_Programs_64.GroupID + 64000000 as GroupID,

displayname0 as DisplayName,

version0 as Version,

installdate0 as InstallDate,

prodID0 as ProdID,

publisher0 as Publisher,

v_GS_Add_Remove_Programs_64.TimeStamp as TimeStamp,

v_GS_Add_Remove_Programs_64.RevisionID as revisionid,

v_GS_Add_Remove_Programs_64.ResourceID as resourceID,

CTE.SystemRole0

FROM v_GS_Add_Remove_Programs_64

LEFT JOIN CTE ON v_GS_Add_Remove_Programs_64.ResourceID = CTE.ResourceID WHERE CTE.SystemRole0 = 'Workstation')AS t

Regards,

Pravin