INSERT key violation error, SQL Server

[Larry Johnson]

I have two similar SQL Server databases each with a table named
Payments. PaymentID is an identity field and the primary, unique, key
in both tables. There is one other key but it is not unique.

The same INSERT command works for one table but not the other. The
error is "...didn't add 1 record due to key violations". Since the
tables apper to be identical as far as I can tell, I suspect the error
message really means something else.

The INSERT command is:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
SELECT 5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47# ;

When I paste the command into Enterprise Manager's Query Analyzer (and
change the # to ') it runs for both tables.

I can INSERT records with identity fields in other tables in both
databases so there doesn't seem to be a global problem but something
with Payments.

Any ideas?

[Larry Johnson]

Thanks. I'll try it this evening.

On Tue, 02 Mar 2004 00:07:38 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

Access doesn't always write the best queries... Your query string
should be:

..>...stuff deleted

[Tom van Stiphout]

Do you perhaps have a trigger on the failing table, that is attempting to
insert a record somewhere else?
We're also assuming - of course - that ControlNumber=5000 doesn't already
exist. Test this using a SELECT statement, rather than by viewing the table
data.

-Tom.

"Larry Johnson" <la***@gjerager.com> wrote in message
news:pj********************************@4ax.com...

I have two similar SQL Server databases each with a table named
Payments. PaymentID is an identity field and the primary, unique, key
in both tables. There is one other key but it is not unique.

The same INSERT command works for one table but not the other. The
error is "...didn't add 1 record due to key violations". Since the
tables apper to be identical as far as I can tell, I suspect the error
message really means something else.

The INSERT command is:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
SELECT 5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47# ;

When I paste the command into Enterprise Manager's Query Analyzer (and
change the # to ') it runs for both tables.

I can INSERT records with identity fields in other tables in both
databases so there doesn't seem to be a global problem but something
with Payments.

Any ideas?

[Larry Johnson]

No triggers. ContolNumber is part of a non-unique index, clustered but
not unique. PaymentID is the unique key, but as that is an identity
field, it isn't in the INSERT.
On Mon, 1 Mar 2004 18:00:27 -0700, "Tom van Stiphout"
<no******************@kinetik-it.com> wrote:

Do you perhaps have a trigger on the failing table, that is attempting to
insert a record somewhere else?
We're also assuming - of course - that ControlNumber=5000 doesn't already
exist. Test this using a SELECT statement, rather than by viewing the table
data.

-Tom.

"Larry Johnson" <la***@gjerager.com> wrote in message
news:pj********************************@4ax.com.. .

I have two similar SQL Server databases each with a table named
Payments. PaymentID is an identity field and the primary, unique, key
in both tables. There is one other key but it is not unique.

The same INSERT command works for one table but not the other. The
error is "...didn't add 1 record due to key violations". Since the
tables apper to be identical as far as I can tell, I suspect the error
message really means something else.

The INSERT command is:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
SELECT 5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47# ;

When I paste the command into Enterprise Manager's Query Analyzer (and
change the # to ') it runs for both tables.

I can INSERT records with identity fields in other tables in both
databases so there doesn't seem to be a global problem but something
with Payments.

Any ideas?

[Larry Johnson]

The changes to the query didn't help. Not a surprise since they work
against one table but not both. I'll work with the other suggestions
in the AM. Thanks again.
On Tue, 02 Mar 2004 00:07:38 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

Access doesn't always write the best queries... Your query string
should be:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
VALUES (5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47#)

Better yet, change the date to ISO format (YYYY-MM-DD hh:nn:ss) and
put quotes around it.

But the above may not help. You may have an extra index or constraint
on the table that's causing the problem, or a constraint that's
declared global to the database, rather then the table.

On Mon, 01 Mar 2004 17:58:15 GMT, Larry Johnson <la***@gjerager.com>
wrote:

I have two similar SQL Server databases each with a table named
Payments. PaymentID is an identity field and the primary, unique, key
in both tables. There is one other key but it is not unique.
The same INSERT command works for one table but not the other. The
error is "...didn't add 1 record due to key violations". Since the
tables apper to be identical as far as I can tell, I suspect the error
message really means something else.
The INSERT command is:
INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
SELECT 5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47# ;
When I paste the command into Enterprise Manager's Query Analyzer (and
change the # to ') it runs for both tables.
I can INSERT records with identity fields in other tables in both
databases so there doesn't seem to be a global problem but something
with Payments.

[Tom van Stiphout]

On Tue, 02 Mar 2004 04:52:41 GMT, Larry Johnson <la***@gjerager.com>
wrote:

OK, sorry I didn't read well enough to realize the PK field was not in
the INSERT statement.

Have you tried DBCC CHECKTABLE?

Can you tell us more about how you're executing this statement. I'm
assuming a query in Access MDB against a ODBC-linked SQL Server table?
If so, delete the link, compact the db, and recreate the link. I have
seen links go bad if index changes were made after the table was
linked.

-Tom.

No triggers. ContolNumber is part of a non-unique index, clustered but
not unique. PaymentID is the unique key, but as that is an identity
field, it isn't in the INSERT.
On Mon, 1 Mar 2004 18:00:27 -0700, "Tom van Stiphout"
<no******************@kinetik-it.com> wrote:

Do you perhaps have a trigger on the failing table, that is attempting to
insert a record somewhere else?
We're also assuming - of course - that ControlNumber=5000 doesn't already
exist. Test this using a SELECT statement, rather than by viewing the table
data.

-Tom.

"Larry Johnson" <la***@gjerager.com> wrote in message
news:pj********************************@4ax.com. ..

I have two similar SQL Server databases each with a table named
Payments. PaymentID is an identity field and the primary, unique, key
in both tables. There is one other key but it is not unique.

The same INSERT command works for one table but not the other. The
error is "...didn't add 1 record due to key violations". Since the
tables apper to be identical as far as I can tell, I suspect the error
message really means something else.

The INSERT command is:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
SELECT 5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47# ;

When I paste the command into Enterprise Manager's Query Analyzer (and
change the # to ') it runs for both tables.

I can INSERT records with identity fields in other tables in both
databases so there doesn't seem to be a global problem but something
with Payments.

Any ideas?

[Larry Johnson]

The solution is to give the role public full permissions on all
tables. Your suggestion about a global issue made me look at it again.

The first SQL Server user for this app, Swift & Co (the big beef
company) has a complex set of users/roles/permissions and I couldn't
figure out how their DBA set it up.

Thanks for the help.

On Tue, 02 Mar 2004 00:07:38 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

Access doesn't always write the best queries... Your query string
should be:

INSERT INTO Payments ( ControlNumber, ContractNumber, CostCode,
Weight, AccruedAmount, PaidAmount, CreationDate )
VALUES (5000, 1, 'CROSS DOCK', 11190, 100, 0, #2/29/2004 21:44:47#)

Better yet, change the date to ISO format (YYYY-MM-DD hh:nn:ss) and
put quotes around it.

But the above may not help. You may have an extra index or constraint
on the table that's causing the problem, or a constraint that's
declared global to the database, rather then the table.