No, we've possibly found another.
We've got a table called 'Parameter'. It has a column called 'comment'. Previously we allowed NULL values in this column. After changing this to NOT NULL we got an error during the Build().
SQLException Data:
Message = Der Wert NULL kann in die 'Comment'-Spalte, 'KLIBNET2_DO_ReferenceData.dbo.Parameter'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu.
Which means something like: The column 'Comment', 'KLIBNET2_DO_ReferenceData.dbo.Parameter'-Table does not accept NULL values
In the Script sent to the DB we see the following construct:
EXEC sp_rename '[dbo].[Parameter].[Comment]', 'TempComment', 'COLUMN';
ALTER TABLE [dbo].[Parameter] ADD [Comment] nvarchar(4000) DEFAULT NULL NOT NULL;
A field NOT allowing NULL has a DEFAULT of NULL?
Regards
Paul Sinnema
Diartis AG
Alex,
Please reread my original entry. I still think there's a problem which is not anwered above.
In the original entry I'm **NOT** talking about 'reference fields' but just 'plain fields'. The 'Comment' field in our table is just a nvarchar(4000) field. We tried modifing it's nullability which caused the funny SQL to be spit out.
Regards
Paul Sinnema
Diartis AG
Hi Alex,
Found another one:
We temporarily disabled Nullable references in our model. Today tried to restore that. During the Upgrade of the DB a script error caused an exception. Here's the produces script.
EXEC sp_rename '[dbo].[Address].[AddressTypeId]', 'TempAddressTypeId', 'COLUMN';
ALTER TABLE [dbo].[Address] ADD [AddressTypeId] integer DEFAULT 0 NOT NULL;
EXEC sp_rename '[dbo].[Address].[ContactId]', 'TempContactId', 'COLUMN';
ALTER TABLE [dbo].[Address] ADD [ContactId] integer DEFAULT 0 NOT NULL;
CREATE INDEX [Address.FK_Z_AddressType] ON [dbo].[Address] ([AddressTypeId] ASC) INCLUDE ([TypeId]);
CREATE INDEX [Address.FK_Z_Contact] ON [dbo].[Address] ([ContactId] ASC) INCLUDE ([TypeId]);
EXEC sp_rename '[dbo].[ContactAdditionalInformation].[ContactId]', 'TempContactId', 'COLUMN';
Caution: If you change parts of a Objektnamens, scripts and stored procedures may be inoperative.
Caution: If you change parts of a Objektnamens, scripts and stored procedures may be inoperative.
Msg 1913, Level 16, State 1, Line 5
errors in the process, because an Index or a statistics with the name 'Address.FK_Z_AddressType' for 'dbo.Address' (table) already exists.
I checked the DB and he's right (as always) the index is already in the DB. I think you need to drop the index before creating a new one.
Regards
Paul
My mistake. I see there are 2 scripts being sent to the DB. The first one drops the index. Now the error is different.
Msg 547, Level 16, State 0, the Line 166 ALTER TABLE statement is in conflict with the FOREIGN KEY restriction 'FK_Address-Z_AddressType-AddressType_Z_AddressType'. The conflict occurred in the 'KLIBNET2_DO_ReferenceData'-Database, Table 'dbo.AddressType', column 'Id' on
This is at line 166
ALTER TABLE [dbo].[Address] ADD CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType] FOREIGN KEY ([AddressTypeId]) REFERENCES [dbo].[AddressType] ([Id]);
This is the Table create script from the DB
USE [KLIBNET2_DO_ReferenceData]
GO
/****** Object: Table [dbo].[Address] Script Date: 07/15/2010 11:57:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Address](
[Id] [int] NOT NULL,
[TypeId] [int] NOT NULL,
[Supplement1] [nvarchar](4000) NULL,
[Supplement2] [nvarchar](4000) NULL,
[Street] [nvarchar](4000) NULL,
[BuildingNumber] [nvarchar](4000) NULL,
[PostOfficeBox] [nvarchar](4000) NULL,
[PostOfficeBoxNumber] [nvarchar](4000) NULL,
[Comment] [nvarchar](4000) NULL,
[IsPostal] [bit] NOT NULL,
[PostalCodeId] [int] NULL,
[AddressTypeId] [int] NULL,
[ContactId] [int] NULL,
CONSTRAINT [PK_Address.AbstractEntity] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address_AbstractHistory] FOREIGN KEY([Id])
REFERENCES [dbo].[AbstractHistory] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_AbstractHistory]
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType] FOREIGN KEY([AddressTypeId])
REFERENCES [dbo].[AddressType] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType]
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address-Z_Contact-Contact_Z_Contact] FOREIGN KEY([ContactId])
REFERENCES [dbo].[Contact] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_Contact-Contact_Z_Contact]
GO
ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address-Z_PostalCode-PostalCode_Z_PostalCode] FOREIGN KEY([PostalCodeId])
REFERENCES [dbo].[PostalCode] ([Id])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_PostalCode-PostalCode_Z_PostalCode]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [Supplement1]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [Supplement2]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [Street]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [BuildingNumber]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [PostOfficeBox]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [PostOfficeBoxNumber]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [Comment]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (CONVERT([bit],(0),0)) FOR [IsPostal]
GO
ALTER TABLE [dbo].[Address] ADD DEFAULT (NULL) FOR [PostalCodeId]
GO
Here's the DO4 Address Class
public partial class Address : AbstractIntersectingHistory , IComparable
{ // START partial class
#region DataObjectsFields
[Field(Nullable = true), FieldMapping("Supplement1")]
public string Z_Supplement1 { get; set; }
[Field(Nullable = true), FieldMapping("Supplement2")]
public string Z_Supplement2 { get; set; }
[Field(Nullable = true), FieldMapping("Street")]
public string Z_Street { get; set; }
[Field(Nullable = true), FieldMapping("BuildingNumber")]
public string Z_BuildingNumber { get; set; }
[Field(Nullable = true), FieldMapping("PostOfficeBox")]
public string Z_PostOfficeBox { get; set; }
[Field(Nullable = true), FieldMapping("PostOfficeBoxNumber")]
public string Z_PostOfficeBoxNumber { get; set; }
[Field(Nullable = true), FieldMapping("Comment")]
public string Z_Comment { get; set; }
[Field(Nullable = false), FieldMapping("IsPostal")]
public bool Z_IsPostal { get; set; }
[Field(Nullable = false), FieldMapping("AddressTypeId")]
[Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
public AddressType Z_AddressType { get; set; }
[Field(Nullable = false), FieldMapping("ContactId")]
[Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
public Contact Z_Contact { get; set; }
[Field(Nullable = true), FieldMapping("PostalCodeId")]
[Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
public PostalCode Z_PostalCode { get; set; }
}
Here's the DO4 AddressType Class
public partial class AddressType : AbstractEntity , IComparable
{ // START partial class
#region DataObjectsFields
[Field(Nullable = true), FieldMapping("TypeLanguage1")]
public string Z_TypeLanguage1 { get; set; }
[Field(Nullable = true), FieldMapping("TypeLanguage2")]
public string Z_TypeLanguage2 { get; set; }
[Field(Nullable = true), FieldMapping("TypeLanguage3")]
public string Z_TypeLanguage3 { get; set; }
[Field(Nullable = true), FieldMapping("TypeLanguage4")]
public string Z_TypeLanguage4 { get; set; }
[Field(Nullable = false), FieldMapping("CanBePerson")]
public bool Z_CanBePerson { get; set; }
[Field(Nullable = false), FieldMapping("CanBeOrganization")]
public bool Z_CanBeOrganization { get; set; }
[Field(Nullable = false), FieldMapping("IsRepresentative")]
public bool Z_IsRepresentative { get; set; }
[Field()]
[Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear, PairTo = "Z_AddressType")]
public EntitySet<Address> Z_AddressList { get; set; }
}
Here's the Data in Address
Id TypeId Supplement1 Supplement2 Street BuildingNumber PostOfficeBox PostOfficeBoxNumber Comment IsPostal PostalCodeId AddressTypeId ContactId
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------ ------------- -----------
2177 105 0 NULL 346 1025
(1 row(s) affected)
Here's the Data in AddressType
Id TypeId TypeLanguage1 TypeLanguage2 TypeLanguage3 TypeLanguage4 CanBePerson CanBeOrganization IsRepresentative
----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------- ----------------- ----------------
344 106 Home address2 Wohnadresse2 Wohnadresse2 Wohnadresse2 1 0 0
345 106 Organisation Address Firmensitz Firmensitz Firmensitz 0 1 0
346 106 Home address Wohnadresse Wohnadresse Wohnadresse 1 0 0
347 106 Work address Arbeitsadresse Arbeitsadresse Arbeitsadresse 1 1 0
(4 row(s) affected)
