Collation error message importing physical inventory count TXT file

(0) Share

Report

Posted on by Community Member Microsoft Employee

I have not been able to import physical inventory counts from a TXT file into RMS. I have even tried "making" a simple, three column txt file with data in it but I still receive the same error message:

----------------------

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

(Source: Microsoft OLE DB Provider for SQL Server)

(SQL State: 42000)

(NativeError: 468)

No Help file available

UPDATE [# # ST_Validation_6]

SET ImportStatus = 3

, ItemID = Item.ID

FROM Item INNER JOIN [# #ST_Validation_6] ON Item.ItemLookupCode = [# #ST_Validation_6].ItemLookupCode

----------------------

Any ideas? I was expecting it to be a fairly smooth process - and I'm pretty sure I'm doing the import steps correctly.

Thanks

*This post is locked for comments

All responses (9)

Answers (2)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Hi

In the middle of inventory counting and I need to get this issue solved, ASAP. Any help would be appreciated.

RMS 2.0.2022 (one lane)
Windows 10 Pro 64-bit

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29 Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17134: )

Server Collation: Latin1_General_CI_AS
RMS Database Collation: SQL_Latin1_General_CP1_CI_AS
Systems DB (master, tempdb, model, msdb) Collation: Latin1_General_CI_AS

Error message while i was importing a manual inventory cout csv file.

Error #-2147217900
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
(Source: Microsoft OLE DB Provider for SQL Server)
(SQL State: 42000)
(NativeError: 468)
No Help file available

UPDATE [# # ST_Validation_2]
SET ImportStatus = 3
, ItemID = Item.ID
FROM Item INNER JOIN [# #ST_Validation_2] ON Item.ItemLookupCode = [# #ST_Validation_2].ItemLookupCode

So I concluded that my sql server collation needs to be the same as my RMS database collation. Any help would be appreciated on how to do this?

Thanks

Danny
Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Hi

I am having the same problem. How did you solve the issue?

Thanks

Danny
Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Thanks for your help. Partner fixed issue - and your response helped me to formulate an email that got a response. Thanks.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Thanks, yes they were definitely different. Partner has recognised issue and now fixed.

Is it likely to have caused any other problems i should be aware of or look into? Many thanks.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Thanks for your help.

Partner has now fixed collation error.

Appreciate your assistance in helping me to explain the issue.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Thank you for your question.

it appears that your SQL and Database collations are different and can cause errors.

if you go to Store Operations Administrator, sign in by going to File | Connect and then to Database | Change Collation, do the Server Collation and Database Collation match?

Thank you,

Scott Wardzinski

Microsoft Dynamics RMS & POS Support Engineer
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Found this that might help: https://groups.google.com/forum/#!searchin/microsoft.public.pos/collation$20conflict/microsoft.public.pos/x0poRopaieM/Q6fX_rYaEvcJ
Community Member Microsoft Employee on at

Like (0)

Report
RE: Collation error message importing physical inventory count TXT file

Hi. The file I tried was very simple - two rows of data only. The lookup codes definitely exist.

Not sure how to find the SQL version - but here is what I can find based on applications partner has installed on my PC:

RMS is version 2.0.0166 (definitely know this is right)

SQL - not sure where to find this.

SQL Server 2008 R2 version information (from application) - does this help

Microsoft SQL Server Management Studio 10.50.1617.0

Microsoft Data Access Components (MDAC) 6.1.7601.17514

Microsoft MSXML 3.0 4.0 6.0

Microsoft Internet Explorer 9.11.9600.16521

Microsoft .NET Framework 2.0.50727.5477

Operating System 6.1.7601

I dont know how to check the region/language settings. I had a partner implement for me. I have raised the issue with them but haven't gotten anywhere and stocktake time is looming.

Appreciate your help - may also mean I can go back to partner with better information to explain the issue.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Collation error message importing physical inventory count TXT file

Weird looking error. Seems like it is pointing out a collation error at the table level, ie, the temp table it creates (# #ST_Validation_6) to store your import file has a different collation than other tables in the database. Check your regional / language settings make sure they match your locale. Try importing the same file but just the first few lines; maybe the issue is on one of the lookup codes (although this would show as an import error after the file is read). What versions of RMS and SQL are you running?