Collation on Navision 4.0 SP3 with SQL 2005

kjboloe
kjboloe Member Posts: 56
edited 2007-03-01 in SQL General
Hi
I have a problem, when I try to enable the MaintainSIFTIndex for the "Cust. Ledger Entry" I get the following error :

Error 468, Cannot resolve the collation between "Danish_Norwegian_CI_AS" and "Danish_Norwegian_CI_AI" in the equal to operation.

I hope that someone can explain why this error occurs. ](*,)

I Have tried to create an eviroment at the office, but when I check the Collation, I have the same for all tables/fields.

Kind regards
Kenneth J. Bolø
Kind regards

Kenneth Jarlshøi Bolø
Dynateam A/S

Comments

  • kjboloe
    kjboloe Member Posts: 56
    We have now tried to make a copy of the table 'Cust. Ledger Entry' in the 50000 area. First we tried to enable the MaintainSIFTIndex without data, no problem. Then we copied all the records and tried again, no problem.

    So I think that we have to do the following :

    1) Take a backup of the database.
    2) Export the table 'Cust. Ledger Entry' as a fob file.
    3) Copy all the records to out backup table.
    4) Delete the 'Cust. Ledger Entry'.
    5) Import the fob file with the 'Cust. Ledger Entry'.
    6) Enable the MaintainSIFTIndex.
    7) Copy the data back to 'Cust. Ledger Entry'.
    If this fails we have to delete the database and recreate it and restore the database again, do we have to sync. the users after the restore ?

    Kind regards
    Kenneth J. Bolø
    Kind regards

    Kenneth Jarlshøi Bolø
    Dynateam A/S
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Hi,

    Make an SQL backup - much faster to do and to restore, and after restore you won't need user accounts synchronization.

    Before applying described procedure try to disable all indexes with SIFT fields, or just remove SIFT fields from them, save T21, and redesing it again enabling indexes or restoring SIFT fields one by one, enabling MaintainSIFTIndex

    Hope this helps a bit.

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • ara3n
    ara3n Member Posts: 9,258
    From what i can see, it looks like somebody changed the collation of custl. ledger entry table. Just the table on sql directly.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • kine
    kine Member Posts: 12,562
    Yes, Ara3n is right. It looks like somebody changed the collation and the database is not consistent now.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Administrator
    Administrator Member, Moderator, Administrator Posts: 2,506
    [Topic moved from Navision to SQL General forum]

Categories