mibuso.com
Home› SQL General
Options

Nav 5.0 with SQL Server 2005, while importings objects

anilkumaranilkumar Member Posts: 136
edited 2007-10-02 in SQL General
Hi Experts!


I am facing a problem while importing objects.

Error message as follows:

Microsoft Dynamics NAV

The following SQL Server error(s) occurred while accessing the Sales Header table:


1902,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create more than one clustered index on table 'IFoods_MockDB Live.dbo.CHP Blenders$Sales Header'. Drop the existing clustered index 'CHP Blenders$Sales Header$0' before creating another.



SQL:

CREATE UNIQUE CLUSTERED INDEX "$1" ON "IFoods_MockDB Live"."dbo"."CHP Blenders$Sales Header" ("No_","Document Type")


Can any one help me - ?


Thanks!
Anil Kumar Korada
Technical Consultant

Comments

  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Looks like someone has changed the clustered index from the primairy to a new secondary index.

    Navision should handle this and this seems to me like a bug.

    A workaround can be to disable the current checkmark of the clustered index in the key menu of the table and import again.

    The clustered property is by default hidden in the key menu.

    Be aware that in large databases it can take quite a while to restructure the data on the disk for a new clustered index.

    I'll also test this myself and report the bug to Microsoft if nesesairy.
    Marije Brummel | Freelance Business Central Specialist
  • Options
    krikikriki Member, Moderator Posts: 9,089
    Or someone created a clustered index directly in SQL and not through Navision.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    anilkumaranilkumar Member Posts: 136
    After created a New database in Nav 5.0, then restored the backup of Nav 4.0 SP2, then importing the Nav 5.0 objects, that time showing the error, no one did manually created culstered index in SQL,

    Is there any problem with backup - ?
    Anil Kumar Korada
    Technical Consultant
  • Options
    krikikriki Member, Moderator Posts: 9,089
    Could be. Did you try to restore it in a 4.00SP3 DB?
    If this works, probably there is some bug in 5.00.

    Then I would work like Mark Brummel wrote.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    jlandeenjlandeen Member Posts: 524
    I've encountered similar problems to this when working with a large database. The only solution we were able to implement that got us up and running again was turning of MaintainSQLIndex and MaintainSIFTIndex. This was not the best option, but it got us up & running (thankfully it wasn't on GL or other big posted tables).
    Jeff Landeen - Sr. Consultant
    Epimatic Corp.
    http://www.epimatic.com
Sign In or Register to comment.