Nav 5.0 with SQL Server 2005, while importings objects

anilkumar

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!

Marije_Brummel

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.

kriki

Or someone created a clustered index directly in SQL and not through Navision.

anilkumar

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 - ?

kriki

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.

jlandeen

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).