Otdfbt

I'm not a great mssql admin so this may be a lot easier, I just don't know where to start looking.

ADO Err Description: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot insert duplicate key row in object 'dbo.posALL'
with unique index 'PK_tposALL'.
Originated at: Business.Cls.DayChange
ADO Err Number: -2147217900
Source: Microsoft OLE DB Provider for ODBC Drivers
SQL State: 23000
Native Err: 2601

When using a program that detects the current day and requests to change it on a new day, fails with the report above.

I'm looking at the database, I can see the table and rows, but I don't know how to see key rows or determine where or why there may be a duplicate, especially for something like which day of the year it is.

Cannot insert duplicate key row in object 'dbo.posALL' with unique index 'PK_tposALL'

Breaking down this error message a little:

• You have a table named dbo.posALL
  


• There is a unique index named PK_tposALL defined on this table


• The program you are using tries to insert a record with values for the key columns defined in PK_tposALL that already exist in the table

To understand which columns constitute a duplicate, you can go to the table in SSMS, expand the "Keys" folder, and then choose "script key as." Check out this screenshot:

ALTER TABLE [dbo].[Badges] 
ADD CONSTRAINT [PK_Badges__Id] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
);

Note that in this case, the unique index is defined using a primary key constraint. I imagine this is the case in your scenarios as well. Yours may or may not be CLUSTERED, and that part doesn't matter for this situation.

In any case, my dbo.Badges requires the Id column to be unique in the whole table. Your column list will be different.

To find the duplicate data, you'll write a query that specifies each column from the unique index in the WHERE clause:

SELECT b.*
FROM dbo.Badges b
WHERE 
 b.Id = 128;

In your case, it will look something like this:

SELECT *
FROM dbo.posALL a
WHERE 
 a.ColumnA = 123
 AND a.ColumnB = 456
 AND a.ColumnC = 'abc';

Where,

• ColumnA, ColumnB, and ColumnC are the columns in PK_tposALL


• 123, 456, and 'abc' are the values your program was trying to insert into the table

In the end you'll have to decide what to do from there, possibly either deleting the existing row (so that you can insert the new row) or updating the existing row with the values from the new row.