Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

Collation error message importing physical inventory count TXT file

Posted on by 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,316 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans