Discussion:
Problem inserting records into a merge replicated DB
(too old to reply)
Jim H
2004-01-07 22:07:34 UTC
Permalink
I'm trying to insert a record into a table but get the following error:

Error: 0x80040e2f DB_E_INTEGRITYVIOLATION
Native Error: (25005)
Description: The column cannot contain null calues. [,,,,,]
Interface defining error: IID_IRowsetChange
Param.0: 0
Param.1: 0
Param.2: 0
Param.3:
Param.4:
Param.5:

I've tried inserting the record from my program code as well as from the
SQLCE Query Analyzer. I have no null fields. The only fields Idon't
include in my insert statement are the columns created by the replication.
s_Generation
s_RowLineage
rowguid

The same insert statement works fine on the SQLServer running on my PC, but
it fails on the PPC SQLCE

How do I add records? Do i need to create calues for these fields? How do
I fine what those values should be?

Thanks,
jim
Dave Nettleton [MSFT]
2004-01-08 18:04:20 UTC
Permalink
Jim
Are there any other constraints on the table (primary/foreign key)?
Note: You can use Enteprise Manager to generate SQL script of the table
(searach to tables for the DB, right click on table in qiestion, select All
Tasks | Generate SQL Script)

Could you also send the insert statement that works for SQL Server but not
SQL CE.

Thanks
Dave.

This posting is provided "AS IS" with no warranties, and confers no rights.
Post by Jim H
Error: 0x80040e2f DB_E_INTEGRITYVIOLATION
Native Error: (25005)
Description: The column cannot contain null calues. [,,,,,]
Interface defining error: IID_IRowsetChange
Param.0: 0
Param.1: 0
Param.2: 0
I've tried inserting the record from my program code as well as from the
SQLCE Query Analyzer. I have no null fields. The only fields Idon't
include in my insert statement are the columns created by the replication.
s_Generation
s_RowLineage
rowguid
The same insert statement works fine on the SQLServer running on my PC, but
it fails on the PPC SQLCE
How do I add records? Do i need to create calues for these fields? How do
I fine what those values should be?
Thanks,
jim
Jim H
2004-01-08 20:48:39 UTC
Permalink
I could send the insert statement to SQL Server and it worked fine. Just in
SQL CE it failed. I did not setup any constraints.

Here is the SQL for creating the table from Enterprize manager.

CREATE TABLE [dbo].[Activities] (
[ActivityKey] [int] IDENTITY (1, 1) NOT NULL ,
[ClientId] [int] NOT NULL ,
[ExerciseTypeId] [int] NOT NULL ,
[WorkoutDate] [datetime] NOT NULL ,
[SetNumber] [int] NOT NULL ,
[GoalWeight] [int] NULL ,
[GoalRepititions] [int] NULL ,
[TimeToNextSet] [int] NULL ,
[ActualWeight] [int] NULL ,
[ActualRepititions] [int] NULL ,
[ExerciseOrderId] [smallint] NOT NULL ,
[date_stamp] [datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]


Activity key is the Primary Key. SQLServer added rowguid when I set it up
for replication. Values in ExerciseTypeId and ClientId are keys into other
tables but I have't setup any constraints for that yet. Right now those
relations are just on paper, I'm still in the testing phase.

Thanks,
jim
Post by Dave Nettleton [MSFT]
Jim
Are there any other constraints on the table (primary/foreign key)?
Note: You can use Enteprise Manager to generate SQL script of the table
(searach to tables for the DB, right click on table in qiestion, select All
Tasks | Generate SQL Script)
Could you also send the insert statement that works for SQL Server but not
SQL CE.
Thanks
Dave.
This posting is provided "AS IS" with no warranties, and confers no rights.
Post by Jim H
Error: 0x80040e2f DB_E_INTEGRITYVIOLATION
Native Error: (25005)
Description: The column cannot contain null calues. [,,,,,]
Interface defining error: IID_IRowsetChange
Param.0: 0
Param.1: 0
Param.2: 0
I've tried inserting the record from my program code as well as from the
SQLCE Query Analyzer. I have no null fields. The only fields Idon't
include in my insert statement are the columns created by the replication.
s_Generation
s_RowLineage
rowguid
The same insert statement works fine on the SQLServer running on my PC,
but
Post by Jim H
it fails on the PPC SQLCE
How do I add records? Do i need to create calues for these fields? How
do
Post by Jim H
I fine what those values should be?
Thanks,
jim
Dave Nettleton [MSFT]
2004-01-08 23:56:16 UTC
Permalink
Post by Jim H
I could send the insert statement to SQL Server and it worked fine. Just in
SQL CE it failed. I did not setup any constraints.
Post by Dave Nettleton [MSFT]
Could you also send the insert statement that works for SQL Server but not
SQL CE.
Sorry - I meant could you post the query to the NG also:)
Dave.

This posting is provided "AS IS" with no warranties, and confers no rights.
Jon Pawley
2004-01-15 01:27:36 UTC
Permalink
Dave,

I'm also having a wee problem inserting a new record into a table.

Here's my table definition:


/****** Object: Table [dbo].[InspectionVehicle] Script Date: 15/01/2004
2:16:22 p.m. ******/
CREATE TABLE [dbo].[InspectionVehicle] (
[InspectionVehicleGUID] [uniqueidentifier] NOT NULL ,
[InspectionGUID] [uniqueidentifier] NOT NULL ,
[VehicleGUID] [uniqueidentifier] NOT NULL ,
[OwnerGUID] [uniqueidentifier] NULL ,
[VehicleRegistration] [nvarchar] (7) NULL ,
[Odometer] [int] NULL ,
[FuelLevelID] [int] NULL ,
[FirstRegistered] [smalldatetime] NULL ,
[WOFUntil] [smalldatetime] NULL ,
[RegistrationUntil] [smalldatetime] NULL ,
[RoadUserCharge] [int] NULL ,
[Hub] [int] NULL ,
[LastServiceKs] [int] NULL ,
[NextServiceKs] [int] NULL ,
[HistoryID] [int] NULL ,
[LogBookID] [int] NULL ,
[HandBookID] [int] NULL ,
[ToolsID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[InspectionVehicle] WITH NOCHECK ADD
CONSTRAINT [PK_InspectionVehicle] PRIMARY KEY CLUSTERED
(
[InspectionVehicleGUID]
) ON [PRIMARY]
GO


CREATE INDEX [IX_InspectionAppointments_VehicleInspectionGUID] ON
[dbo].[Appointments]([InspectionGUID]) ON [PRIMARY]
GO

CREATE INDEX [IX_InspectionAppointments] ON
[dbo].[Appointments]([AppointmentStatusCode], [InspectorLogonName]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [DF_InspectionVehicle_InspectionVehicleGUID] DEFAULT (newid())
FOR [InspectionVehicleGUID],
CONSTRAINT [DF__Inspectio__rowgu__3C0576A6] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_1410468449] ON
[dbo].[InspectionVehicle]([rowguid]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [FK_InspectionVehicle_Inspections] FOREIGN KEY
(
[InspectionGUID]
) REFERENCES [dbo].[Inspections] (
[InspectionGUID]
),
CONSTRAINT [FK_InspectionVehicle_Vehicle] FOREIGN KEY
(
[VehicleGUID]
) REFERENCES [dbo].[Vehicle] (
[VehicleGUID]
)
GO



And here's the insert query I am attempting to execute:

private const string _insertInspectionVehicleRecordSql =
@"INSERT INTO InspectionVehicle
(
InspectionGUID,
VehicleGUID
)
VALUES
(
?,
?
)";


(Obviously that'll be for a parameterized query, and the appropriate GUIDs
are being passed in. I've checked that they ar not null, they are not
Guid.Empty and they are not DBNull.Value).

I Googled a post back in 2001 which had something to do with not nullable
dates
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ugLKkL%24SBHA.17
40%40tkmsftngp03&rnum=3&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1%26q
%3D25005%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.ce), so I'll have a
look at that...

If I come up with anything, I'll let you know, but in the mean time, do you
have any insight as to why my insert may be failing?

Cheers,

Jon
Post by Dave Nettleton [MSFT]
Post by Jim H
I could send the insert statement to SQL Server and it worked fine.
Just
Post by Dave Nettleton [MSFT]
in
Post by Jim H
SQL CE it failed. I did not setup any constraints.
Post by Dave Nettleton [MSFT]
Could you also send the insert statement that works for SQL Server but
not
Post by Jim H
Post by Dave Nettleton [MSFT]
SQL CE.
Sorry - I meant could you post the query to the NG also:)
Dave.
This posting is provided "AS IS" with no warranties, and confers no rights.
Jon Pawley
2004-01-15 19:52:25 UTC
Permalink
I fixed my problem, but I think that there is a bit of a bug here somewhere.

I took out the "rowguid" column from the "InspectionVehicle" table; set the
"InspectionVehicleGUID" column as the "ROWGUIDCOL" and then reapplied
replication.

SQL Server CE just didn't handle inserting rows with default values in my
situation... maybe because the table's primary key wasn't the ROWGUIDCOL
column?

Cheers,
Jon
Post by Jon Pawley
Dave,
I'm also having a wee problem inserting a new record into a table.
/****** Object: Table [dbo].[InspectionVehicle] Script Date: 15/01/2004
2:16:22 p.m. ******/
CREATE TABLE [dbo].[InspectionVehicle] (
[InspectionVehicleGUID] [uniqueidentifier] NOT NULL ,
[InspectionGUID] [uniqueidentifier] NOT NULL ,
[VehicleGUID] [uniqueidentifier] NOT NULL ,
[OwnerGUID] [uniqueidentifier] NULL ,
[VehicleRegistration] [nvarchar] (7) NULL ,
[Odometer] [int] NULL ,
[FuelLevelID] [int] NULL ,
[FirstRegistered] [smalldatetime] NULL ,
[WOFUntil] [smalldatetime] NULL ,
[RegistrationUntil] [smalldatetime] NULL ,
[RoadUserCharge] [int] NULL ,
[Hub] [int] NULL ,
[LastServiceKs] [int] NULL ,
[NextServiceKs] [int] NULL ,
[HistoryID] [int] NULL ,
[LogBookID] [int] NULL ,
[HandBookID] [int] NULL ,
[ToolsID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] WITH NOCHECK ADD
CONSTRAINT [PK_InspectionVehicle] PRIMARY KEY CLUSTERED
(
[InspectionVehicleGUID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_InspectionAppointments_VehicleInspectionGUID] ON
[dbo].[Appointments]([InspectionGUID]) ON [PRIMARY]
GO
CREATE INDEX [IX_InspectionAppointments] ON
[dbo].[Appointments]([AppointmentStatusCode], [InspectorLogonName]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [DF_InspectionVehicle_InspectionVehicleGUID] DEFAULT (newid())
FOR [InspectionVehicleGUID],
CONSTRAINT [DF__Inspectio__rowgu__3C0576A6] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1410468449] ON
[dbo].[InspectionVehicle]([rowguid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [FK_InspectionVehicle_Inspections] FOREIGN KEY
(
[InspectionGUID]
) REFERENCES [dbo].[Inspections] (
[InspectionGUID]
),
CONSTRAINT [FK_InspectionVehicle_Vehicle] FOREIGN KEY
(
[VehicleGUID]
) REFERENCES [dbo].[Vehicle] (
[VehicleGUID]
)
GO
private const string _insertInspectionVehicleRecordSql =
@"INSERT INTO InspectionVehicle
(
InspectionGUID,
VehicleGUID
)
VALUES
(
?,
?
)";
(Obviously that'll be for a parameterized query, and the appropriate GUIDs
are being passed in. I've checked that they ar not null, they are not
Guid.Empty and they are not DBNull.Value).
I Googled a post back in 2001 which had something to do with not nullable
dates
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ugLKkL%24SBHA.17
40%40tkmsftngp03&rnum=3&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1%26q
Post by Jon Pawley
%3D25005%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.ce), so I'll have a
look at that...
If I come up with anything, I'll let you know, but in the mean time, do you
have any insight as to why my insert may be failing?
Cheers,
Jon
Post by Dave Nettleton [MSFT]
Post by Jim H
I could send the insert statement to SQL Server and it worked fine.
Just
Post by Dave Nettleton [MSFT]
in
Post by Jim H
SQL CE it failed. I did not setup any constraints.
Post by Dave Nettleton [MSFT]
Could you also send the insert statement that works for SQL Server but
not
Post by Jim H
Post by Dave Nettleton [MSFT]
SQL CE.
Sorry - I meant could you post the query to the NG also:)
Dave.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Dave Nettleton [MSFT]
2004-01-16 04:17:11 UTC
Permalink
Jon,
glad you fixed it.
I'll look into it some more.
Thanks
Dave.

This posting is provided "AS IS" with no warranties, and confers no rights.
Post by Jon Pawley
I fixed my problem, but I think that there is a bit of a bug here somewhere.
I took out the "rowguid" column from the "InspectionVehicle" table; set the
"InspectionVehicleGUID" column as the "ROWGUIDCOL" and then reapplied
replication.
SQL Server CE just didn't handle inserting rows with default values in my
situation... maybe because the table's primary key wasn't the ROWGUIDCOL
column?
Cheers,
Jon
Post by Jon Pawley
Dave,
I'm also having a wee problem inserting a new record into a table.
15/01/2004
Post by Jon Pawley
2:16:22 p.m. ******/
CREATE TABLE [dbo].[InspectionVehicle] (
[InspectionVehicleGUID] [uniqueidentifier] NOT NULL ,
[InspectionGUID] [uniqueidentifier] NOT NULL ,
[VehicleGUID] [uniqueidentifier] NOT NULL ,
[OwnerGUID] [uniqueidentifier] NULL ,
[VehicleRegistration] [nvarchar] (7) NULL ,
[Odometer] [int] NULL ,
[FuelLevelID] [int] NULL ,
[FirstRegistered] [smalldatetime] NULL ,
[WOFUntil] [smalldatetime] NULL ,
[RegistrationUntil] [smalldatetime] NULL ,
[RoadUserCharge] [int] NULL ,
[Hub] [int] NULL ,
[LastServiceKs] [int] NULL ,
[NextServiceKs] [int] NULL ,
[HistoryID] [int] NULL ,
[LogBookID] [int] NULL ,
[HandBookID] [int] NULL ,
[ToolsID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] WITH NOCHECK ADD
CONSTRAINT [PK_InspectionVehicle] PRIMARY KEY CLUSTERED
(
[InspectionVehicleGUID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_InspectionAppointments_VehicleInspectionGUID] ON
[dbo].[Appointments]([InspectionGUID]) ON [PRIMARY]
GO
CREATE INDEX [IX_InspectionAppointments] ON
[dbo].[Appointments]([AppointmentStatusCode], [InspectorLogonName]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [DF_InspectionVehicle_InspectionVehicleGUID] DEFAULT (newid())
FOR [InspectionVehicleGUID],
CONSTRAINT [DF__Inspectio__rowgu__3C0576A6] DEFAULT (newid()) FOR
[rowguid]
Post by Jon Pawley
GO
CREATE UNIQUE INDEX [index_1410468449] ON
[dbo].[InspectionVehicle]([rowguid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InspectionVehicle] ADD
CONSTRAINT [FK_InspectionVehicle_Inspections] FOREIGN KEY
(
[InspectionGUID]
) REFERENCES [dbo].[Inspections] (
[InspectionGUID]
),
CONSTRAINT [FK_InspectionVehicle_Vehicle] FOREIGN KEY
(
[VehicleGUID]
) REFERENCES [dbo].[Vehicle] (
[VehicleGUID]
)
GO
private const string _insertInspectionVehicleRecordSql =
@"INSERT INTO InspectionVehicle
(
InspectionGUID,
VehicleGUID
)
VALUES
(
?,
?
)";
(Obviously that'll be for a parameterized query, and the appropriate GUIDs
are being passed in. I've checked that they ar not null, they are not
Guid.Empty and they are not DBNull.Value).
I Googled a post back in 2001 which had something to do with not nullable
dates
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ugLKkL%24SBHA.17
40%40tkmsftngp03&rnum=3&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DISO-8859-1%26q
Post by Jon Pawley
Post by Jon Pawley
%3D25005%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.ce), so I'll have a
look at that...
If I come up with anything, I'll let you know, but in the mean time, do
you
Post by Jon Pawley
have any insight as to why my insert may be failing?
Cheers,
Jon
Post by Dave Nettleton [MSFT]
Post by Jim H
I could send the insert statement to SQL Server and it worked fine.
Just
Post by Dave Nettleton [MSFT]
in
Post by Jim H
SQL CE it failed. I did not setup any constraints.
Post by Dave Nettleton [MSFT]
Could you also send the insert statement that works for SQL Server
but
Post by Jon Pawley
Post by Dave Nettleton [MSFT]
not
Post by Jim H
Post by Dave Nettleton [MSFT]
SQL CE.
Sorry - I meant could you post the query to the NG also:)
Dave.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Loading...