a customer of mine is getting the following error while trying to import a modified table into his sql database (4.02):
The following SQL error(s) occurred while accessing the Fattura table:
8111, “42000”,[Microsoft],[ODBC SQL Server Driver],[SQL server]Cannot dfine PRIMARY KEY constraint on nullable column in table ‘ADM Produzione$Fattura’
1750, “42000”,[Microsoft],[ODBC SQL Server Driver],[SQL server]Could not create constraint. See previous error
Hmmm … the problem seems to be, that when importing a modified table the existing CONSTRAINT is not dropped before creating the new (modified? was the PK changed?) one …
I guess you can not simply delete the existing table first as it contains some data?
You could try to drop the existing PK CONSTRAINT from SQL Server site, executing:
USE [navision}
GO
ALTER TABLE “dbo”.“ADM Produzione$Fattura” DROP CONSTRAINT “ADM Produzione$Fattura$0”
Then import the new FOB. Check if during the import the CONSTRAINT was created, if not you could add it manually:
USE [navision]
GO
ALTER TABLE “navision”.“dbo”.“ADM Produzione$Fattura” ADD CONSTRAINT “ADM Produzione$Fattura$0” PRIMARY KEY CLUSTERED (“Nr_Fattura”,“Nr_Riga_Fattura”,“Matricola”,“Anno_Fattura”)
Well, I might write a dataport, export all the data, delete the table, recreate it and inmport the data back… given my clumsiness where SQL is involved it might be easier! [:P]
I’ll try your suggestion and let you know, anyway. Thank you.
This script run without errors, but importing FOB is the same. I have 3 society on this db. As like I can’t delete a table because there are data on other society, have I to use script on each one?
Constraint is new to me (neither in Ben Gan sql bible I’ve seen before) but I see that when I drop constraint it drop index too. So I don’t try to drop on each table yet before a kindly backup [:$]
The CONSTRAINT is actually the Primary Key/Clustered Index. As the new FOB changes the Primary Key of the table(s), the existing CONSTRAINT is invalid. Unfortunately, C/SIDE does not recognize and handle it itself, so I proposed to drop the CONSTRAINT manually, hoping it would be re-created on importing the FOB.
With NAV each C/SIDE table is created per company. As you say you have 3 companies, you have 3 “Fattura” tables:
“Company1$Fattura”, “Company3$Fattura”, “Company3$Fattura”; each table with its own “Primary Key Clustered”. Hence, you have to frop all three CONSTRAINTS, just change the “Company” part of the table name as marked bold (same goes for manual createion of the CONSTRAINT):
USE [navision}
GO
ALTER TABLE “dbo”.“ADM Produzione$Fattura” DROP CONSTRAINT “ADM Produzione$Fattura$0”
ALTER TABLE “dbo”.“Company2$Fattura” DROP CONSTRAINT “Company2$Fattura$0”
ALTER TABLE “dbo”.“Company3$Fattura” DROP CONSTRAINT “Company3$Fattura$0”
If this works, you don’t have to delete the whole table.
I’ve dropped constraints but without PK fob could not replace table. It says I have no PK in table.
Now I try to insert the constraint again but SQL gives me back this:
Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘ADM Produzione$Fattura’.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
So you’ve dropped the CONSTRAINTS successfully, but the FOB till can’t be imported. You should not at a PK, especially with a “strange name” with Enterprise Manager. This will cause a “gap” between C/SIDE and SQL Server, resulting in even more problems …
Please try to add the new CONSTRAINT manually, also for all three tables:
USE [navision]
GO
ALTER TABLE “dbo”.“ADM Produzione$Fattura” ADD CONSTRAINT “ADM Produzione$Fattura$0” PRIMARY KEY CLUSTERED (“Nr_Fattura”,“Nr_Riga_Fattura”,“Matricola”,“Anno_Fattura”)
ALTER TABLE “dbo”.“Company2$Fattura” ADD CONSTRAINT “Company2$Fattura$0” PRIMARY KEY CLUSTERED (“Nr_Fattura”,“Nr_Riga_Fattura”,“Matricola”,“Anno_Fattura”)
ALTER TABLE “dbo”.“Company3$Fattura” ADD CONSTRAINT “Company3$Fattura$0” PRIMARY KEY CLUSTERED (“Nr_Fattura”,“Nr_Riga_Fattura”,“Matricola”,“Anno_Fattura”)
Please regards that the fields should be as in the FOB. If this works, then please try to import the FOB again …
If this all does not work, then restore all original settings, and just implemement the new Key directly with C/SIDE in the live system.
Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘ADM Produzione$Fattura’.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
… means, that one of the PK fields is allowed to NULL fields; you have to set it on NOT NULL (easiest via EM).
Indeed, I have an other customer who had troubles with table relations - while importing fobs - It said that it could not drop the constraint on a key because it was referenced by an other table…
Thank you for your help, Jörg. The fob was finally imported! [:)]