Known Issue: Reading data from SCCM may fail at InsertIntoTempApplicationFile step when SCCM import is run on the application and server and the collation of the SCCM database is different from the collation of the compliance database (IOJ-1758508)

[#1]

Summary

The inventory import process may fail at the InsertIntoTempApplicationFile step when reading inventory data from SCCM with logging similar to the following:

2016-04-07 21:52:36,150 [INFO ]   Importing from 'JTSMS' [type: SMS] (primary connection)
[...]
2016-04-07 21:52:56,073 [INFO ]     InsertIntoTempApplicationFile (Transfer from FNMP to source 'JTSMS')
2016-04-07 21:52:58,491 [INFO ]       Failed to execute Reader 'InsertIntoTempApplicationFile' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\FileEvidence.xml
Error: The locale id '1033' of the source column 'FileName' and the locale id '1041' of the destination column 'FileName' do not match.

This occurs if the collation settings on columns in the SCCM database are different from the default database collation.

Workaround

Edit and modify the following file on the FlexNet Manager Suite batch server: C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\sms\FileEvidence.xml

Find the InsertIntoTempApplicationFile reader in this file.

The default query for this reader looks similar to the following (exact details may vary based on your version of FlexNet Manager Suite):

DECLARE @EscapeChar nchar(1)
SET @EscapeChar = '\'

SELECT
	CASE WHEN CHARINDEX('%', fe.FileName) <> 0 THEN 1 ELSE 0 END AS HasWildcards,
	FileName = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileName, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
	FileVersion = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileVersion, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
	Company = REPLACE(REPLACE(REPLACE(REPLACE(fe.Company, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
	Description = REPLACE(REPLACE(REPLACE(REPLACE(fe.Description, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']')
FROM dbo.FileEvidence AS fe
WHERE fe.FileEvidenceID IN (SELECT FileEvidenceID FROM dbo.SoftwareTitleFileEvidence AS stfe)

Add the clause COLLATE database_default to the relevant column in the SELECT statement that is identified in the error message. For example:

DECLARE @EscapeChar nchar(1)
SET @EscapeChar = '\'

SELECT
	CASE WHEN CHARINDEX('%', fe.FileName) <> 0 THEN 1 ELSE 0 END AS HasWildcards,
	FileName = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileName, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']') COLLATE database_default,
	FileVersion = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileVersion, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
	Company = REPLACE(REPLACE(REPLACE(REPLACE(fe.Company, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
	Description = REPLACE(REPLACE(REPLACE(REPLACE(fe.Description, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']')
FROM dbo.FileEvidence AS fe
WHERE fe.FileEvidenceID IN (SELECT FileEvidenceID FROM dbo.SoftwareTitleFileEvidence AS stfe)

Fix status

There are no plans to address this issue.

Other information

Affected components: Integration: SCCM, Inventory import (read/write/export)

Master issue ID: IOJ-1758508

Also known as: FNMS-32402