Views:

Problem:

This problem pertains to SCSM/Provance 2014 Installations that use versions of SQL Server older than SQL Server 2012.

The Computer Asset Exceptions Report fails.

Provance Health Check HC15_DeploymentsErrorSummary_30_STG.csv reports an error similar to the following:

<Errors><Error EventTime="2015-06-29T17:00:00.6960782Z">Logged: 2015-06-29T17:00:00.6960782Z - Incorrect syntax near the keyword 'LIKE'.</Error><Error EventTime="2015-06-29T17:00:30.2426445Z">Logged: 2015-06-29T17:00:30.2426445Z - Incorrect syntax near the keyword 'LIKE'.</Error><Error EventTime="2015-06-29T17:01:00.1329734Z">Logged: 2015-06-29T17:01:00.1329734Z - Incorrect syntax near the keyword 'LIKE'.</Error

Root Cause:

The Computer Asset Exceptions report calls a Provance SQL function that leverages the SQL Server 2012 IIF() function. Installation using versions of SQL Server older than SQL Server 2012 will not support the IIF() function.

The offending function is:

RETURN      IIF(@IP LIKE '%_.%_.%_.%_'                                                    -- 3 periods and no empty octets 
        AND @IP NOT LIKE '%.%.%.%.%'                                                         -- not 4 periods or more
        AND @IP NOT LIKE '%[^0-9.]%'                                                          -- no characters other than digits and periods
        AND @IP NOT LIKE '%[0-9][0-9][0-9][0-9]%'                                   -- not more than 3 digits per octet
        AND @IP NOT LIKE '%[3-9][0-9][0-9]%'                                            -- NOT 300 - 999
        AND @IP NOT LIKE '%2[6-9][0-9]%'                                                  -- NOT 260 - 299
        AND @IP NOT LIKE '%25[6-9]%'                                                        -- NOT 256 - 259
        , 1, 0)

Workaround:

Replacing the function with the following function will make the function compatible for installation of SQL Server 2008 R2 and above.

CREATE FUNCTION [dbo].[ProvanceIpIsValid]
(
                    @IP VARCHAR(8000)

)
RETURNS BIT
AS
BEGIN
/*
This function returns 1 is the @IP is a valid Ip address based on the comments below.
*/
Return Case When
                 @IP LIKE '%_.%_.%_.%_'                                                            -- 3 periods and no empty octets        
        AND @IP NOT LIKE '%.%.%.%.%'                                                         -- not 4 periods or more

        AND @IP NOT LIKE '%[^0-9.]%'                                                          -- no characters other than digits and periods
        AND @IP NOT LIKE '%[0-9][0-9][0-9][0-9]%'                                   -- not more than 3 digits per octet
        AND @IP NOT LIKE '%[3-9][0-9][0-9]%'                                            -- NOT 300 - 999
        AND @IP NOT LIKE '%2[6-9][0-9]%'                                                  -- NOT 260 - 299
        AND @IP NOT LIKE '%25[6-9]%'                                                        -- NOT 256 - 259
Then 1 Else 0
End
END
GO