Paddy Cairns Paddy Cairns -4 years ago 146
SQL Question

Incorrect Syntax near ';' (Microsoft SQL Server Error 102)

First off, I have been searching all over for an answer to this, and I am also new to SQL coding, so please bear with me.

I am trying to discover the current health status of client machines in SCCM 2012r2 with a custom report, please see code below:

SELECT v_r_system.name0 AS 'Computer Name',
v_gs_antimalwarehealthstatus.lastquickscanage AS 'Days since last quick scan',
v_gs_antimalwarehealthstatus.lastfullscanage AS 'Days Since Last Full Scan',
v_gs_antimalwareinfectionstatus.computerstatus AS 'Current Status',
v_gs_antimalwareinfectionstatus.lastcleaneddectiontime AS 'Last Cleaned'
FROM v_gs_antimalwarehealthstatus
INNER JOIN v_gs_antimalwarehealthstatus
INNER JOIN v_r_system
ON v_gs_antimalwarehealthstatus.resourceid = v_r_system.resourceid
INNER JOIN v_gs_antimalwareinfectionstatus;


When I try it out in Report Builder 3.0 I keep getting following error:

Error 102 Syntax near';'

I can't seem to find the problem, it's driving me crazy...

Anyway, anything you can do to point me in the right direction would be great.

Cheers

P.

Answer Source

You just missed the ON Clause in the statement, also you can use table alias as it makes the query look clean.

SELECT     v_r_system.name0                                       AS 'Computer Name', 
           v_gs_antimalwarehealthstatus.lastquickscanage          AS 'Days since last quick scan',
           v_gs_antimalwarehealthstatus.lastfullscanage           AS 'Days Since Last Full Scan',
           v_gs_antimalwareinfectionstatus.computerstatus         AS 'Current Status', 
           v_gs_antimalwareinfectionstatus.lastcleaneddectiontime AS 'Last Cleaned' 
FROM       v_gs_antimalwarehealthstatus AWT
INNER JOIN v_r_system VRS
    ON  AWT.resourceid = VRS.resourceid 
INNER JOIN v_gs_antimalwareinfectionstatus AWI 
    ON [Place the Matching columns here];
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download