An attempt was made to insert a non-unique value into a unique index. Error: Attempt to insert non-unique value into unique index: microsoft sql server
You have come across a message containing the lines:
Microsoft OLE DB Provider for SQL Server: CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID
or
Cannot I_nsert duplicate key row in object
or
An attempt was made to insert a non-unique value into a unique index.
Solution options:
1. In SQL Server management studio, we physically destroy the failed index (in my case, it was an index on the totals table of the accounting register). In 1C, we will distribute faulty documents. In the testing and correction mode, check the checkboxes for reindexing tables + recalculating totals. 1C recreates the index without error. We carry out previously failed documents.
2.
1) With the help of Management Studio 2005, I generated a create script for creating an index, which was buggy, and saved it to a file.
2) Manually killed the index from the table _AccumRgTn19455
3) Launched a request like
SQL code S_elect count(*), index_fields
FR OM AccumRgTn19455
GROUP BY index_field
HAVING count(*)>1
After the index was killed, I displayed 15 duplicate records, although before step 2 the query returned nothing.
4) I looked through all the records and manually cleaned up the duplicates. In fact, I also used the "Report Structure" processing to understand what I was dealing with in general. It turned out that the table _AccumRgTn19455 stores the accumulation register "Product output (tax accounting)". I also poked around with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were being processed normally, without errors. Of course, it’s not worth cleaning tables at random just like that: it is important to understand what is being cleaned and what it can turn into.
5) Launched a query to create an index, which was saved in a file.
6) Switched the database to single user mode and ran dbcc checkdb - this time there were no errors.
7) Transferred the base back to single user mode.
Everything ... the problem is defeated. Well, even in 1C I launched "Testing and Correction", everything went fine there too, it stopped swearing at a non-unique index.
3. If non-uniqueness lies in dates with zero values, then the problem is solved by creating a base with an offset parameter of 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into the MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the base is already created with offset 0, then create a new one with 2000.
1.2. If it is possible to work with the database in the file version, then perform Testing and Fixing, as well as Configuration - Checking the configuration - Checking the logical integrity of the configuration + Searching for incorrect links.
1.3. If there is no file variant, try loading from DT to a DB2 client/server variant (which is less picky about uniqueness), and then run Test and Repair and Configuration - Configuration Check - Configuration Logical Integrity Check + Bad Reference Finder.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you must enable tracing at boot time in the Profiler utility, or enable logging to the DBMSSQL and EXCP process event log.
2. If the problem of non-uniqueness manifests itself during the work of users:
2.1. Find the problematic request using the method of paragraph 1.4.
2.1.2. Sometimes an error occurs during the execution of requests, for example:
This error occurs due to the fact that in the accumulation register module " Work time employees of organizations" in the procedure "RegisterRecalculations" in the request does not contain the service word "DIFFERENT".
Code 1C v 8.x I.e. should be:
Request = New Request(
"CHOOSE DIFFERENT
| Basic.Individual,
. . . . .
In the latest released releases of ZUP and UPP, the error does not occur, because. it says "VARIOUS".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique entry.
2.2.1. "Fish" script for defining non-unique records using SQL:
SQL Code S_elect COUNT(*) Counter,<перечисление всех полей соответствующего индекса>fr om<имя таблицы>
GROUP BY<перечисление всех полей соответствующего индекса>
HAVING Counter > 1
2.2.2 Example. The index in the error is called "_Document140_VT1385_IntKeyIndNG".
List of table fields:
_Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394, _Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef
Before following the procedure below, do backup Database.
Run in MS SQL Server Query Analyzer:
SQL Code S_elect count(*), _Document140_IDRRef, _KeyField
fr om _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1
Use it to find out the values of the columns _Document140_IDRRef, _KeyField, duplicate records (id, key).
With a request:
SQL Code S_elect *
fr om _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1 or _Document140_IDRRef = id2 and _KeyField = key2 or ...
look at the values of other columns of duplicate entries.
If both entries have meaningful values and these values are different, then fix the value of _KeyField to be unique. To do this, define the maximum occupied value of _KeyField(keymax):
SQL Code S_elect max(_KeyField)
fr om _Document140_VT1385
wh ere_Document140_IDRRef=id1
Replace the _KeyField value in one of the duplicate entries with the correct one:
SQL code update _Document140_VT1385
set _KeyField = keymax + 1
Here _LineNo1386 = is an additional condition that allows you to select one of two duplicate entries.
If one (or both) of the duplicate entries has an obviously wrong value, then it should be removed:
SQL code delete from _Document140_VT1385
wh ere _Document140_IDRRef = id1 and _LineNo1386 = lineno1
If duplicate entries have the same values in all columns, then one of them must be left:
SQL Code S_elect distinct *
into #tmp1
from _Document140_VT1385
Delete from _Document140_VT1385
wh ere _Document140_IDRRef = id1 and _KeyField = key1
I_nsert into _Document140_VT1385
S_elect #tmp1
D_rop table #tmp1
The described procedure must be performed for each pair of duplicate entries.
2.2.3. Second example:
SQL Code S_elect COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)
2.3.4 An example of defining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x CHOOSE Handbook. Link
FROM Directory. Directory AS Directory
GROUP BY
HAVING QUANTITY(*) > 1
Information taken from the site
You have come across a message containing the lines:
Microsoft OLE DB Provider for SQL Server: CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID
or
Cannot I_nsert duplicate key row in object
or
An attempt was made to insert a non-unique value into a unique index.
Solution options:
1. In SQL Server management studio, we physically destroy the failed index (in my case, it was an index on the totals table of the accounting register). In 1C, we will distribute faulty documents. In the testing and correction mode, check the checkboxes for reindexing tables + recalculating totals. 1C recreates the index without error. We carry out previously failed documents.
2.
1) With the help of Management Studio 2005, I generated a create script for creating an index, which was buggy, and saved it to a file.
2) Manually killed the index from the table _AccumRgTn19455
3) Launched a request like
SQL code S_elect count(*), index_fields
FROM AccumRgTn19455
GROUP BY index_field
HAVING count(*)>1
After the index was killed, I displayed 15 duplicate records, although before step 2 the query returned nothing.
4) I looked through all the records and manually cleaned up the duplicates. In fact, I also used the "Report Structure" processing to understand what I was dealing with in general. It turned out that the table _AccumRgTn19455 stores the accumulation register "Product output (tax accounting)". I also poked around with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were being processed normally, without errors. Of course, it’s not worth cleaning tables at random just like that: it is important to understand what is being cleaned and what it can turn into.
5) Launched a query to create an index, which was saved in a file.
6) Switched the database to single user mode and ran dbcc checkdb - this time there were no errors.
7) Transferred the base back to single user mode.
Everything ... the problem is defeated. Well, even in 1C I launched "Testing and Correction", everything went fine there too, it stopped swearing at a non-unique index.
3. If non-uniqueness lies in dates with zero values, then the problem is solved by creating a base with an offset parameter of 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into the MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the base is already created with offset 0, then create a new one with 2000.
1.2. If it is possible to work with the database in the file version, then perform Testing and Fixing, as well as Configuration - Checking the configuration - Checking the logical integrity of the configuration + Searching for incorrect links.
1.3. If there is no file variant, try loading from DT to a DB2 client/server variant (which is less picky about uniqueness), and then run Test and Repair and Configuration - Configuration Check - Configuration Logical Integrity Check + Bad Reference Finder.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you must enable tracing at boot time in the Profiler utility, or enable logging to the DBMSSQL and EXCP process event log.
2. If the problem of non-uniqueness manifests itself during the work of users:
2.1. Find the problematic request using the method of paragraph 1.4.
2.1.2. Sometimes an error occurs during the execution of requests, for example:
This error occurs due to the fact that in the module of the accumulation register "Working hours of employees of organizations" in the procedure "Register Recalculations" in the request there is no service word "DIFFERENT".
Code 1C v 8.x I.e. should be:
Request = New Request(
"CHOOSE DIFFERENT
| Basic.Individual,
. . . . .
In the latest released releases of ZUP and UPP, the error does not occur, because. it says "VARIOUS".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique entry.
2.2.1. "Fish" script for defining non-unique records using SQL:
SQL Code S_elect COUNT(*) Counter,<перечисление всех полей соответствующего индекса>from<имя таблицы>
GROUP BY<перечисление всех полей соответствующего индекса>
HAVING Counter > 1
2.2.2 Example. The index in the error is called "_Document140_VT1385_IntKeyIndNG".
List of table fields:
_Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394, _Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef
Please backup your database before following the procedure below.
Run in MS SQL Server Query Analyzer:
SQL Code S_elect count(*), _Document140_IDRRef, _KeyField
from _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1
Use it to find out the values of the columns _Document140_IDRRef, _KeyField, duplicate records (id, key).
With a request:
SQL Code S_elect *
from _Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or ...
look at the values of other columns of duplicate entries.
If both entries have meaningful values and these values are different, then fix the value of _KeyField to be unique. To do this, define the maximum occupied value of _KeyField(keymax):
SQL Code S_elect max(_KeyField)
from _Document140_VT1385
where _Document140_IDRRef = id1
Replace the _KeyField value in one of the duplicate entries with the correct one:
SQL code update _Document140_VT1385
set _KeyField = keymax + 1
Here _LineNo1386 = is an additional condition that allows you to select one of two duplicate entries.
If one (or both) of the duplicate entries has an obviously wrong value, then it should be removed:
SQL code delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1
If duplicate entries have the same values in all columns, then one of them must be left:
SQL Code S_elect distinct *
into #tmp1
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
Delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
I_nsert into _Document140_VT1385
S_elect #tmp1
D_rop table #tmp1
The described procedure must be performed for each pair of duplicate entries.
2.2.3. Second example:
SQL Code S_elect COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)
2.3.4 An example of defining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x CHOOSE Handbook. Link
FROM Directory. Directory AS Directory
GROUP BY
HAVING QUANTITY(*) > 1
You have come across a message containing the lines:
Microsoft OLE DB Provider for SQL Server: CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID
or
Cannot I_nsert duplicate key row in object
or
An attempt was made to insert a non-unique value into a unique index.
Solution options:
1. In SQL Server management studio, we physically destroy the failed index (in my case, it was an index on the totals table of the accounting register). In 1C, we will distribute faulty documents. In the testing and correction mode, check the checkboxes for reindexing tables + recalculating totals. 1C recreates the index without error. We carry out previously failed documents.
2.
1) With the help of Management Studio 2005, I generated a create script for creating an index, which was buggy, and saved it to a file.
2) Manually killed the index from the table _AccumRgTn19455
3) Launched a request like
SQL code S_elect count(*), index_fields
FROM AccumRgTn19455
GROUP BY index_field
HAVING count(*)>1
After the index was killed, I displayed 15 duplicate records, although before step 2 the query returned nothing.
4) I looked through all the records and manually cleaned up the duplicates. In fact, I also used the "Report Structure" processing to understand what I was dealing with in general. It turned out that the table _AccumRgTn19455 stores the accumulation register "Product output (tax accounting)". I also poked around with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were being processed normally, without errors. Of course, it’s not worth cleaning tables at random just like that: it is important to understand what is being cleaned and what it can turn into.
5) Launched a query to create an index, which was saved in a file.
6) Switched the database to single user mode and ran dbcc checkdb - this time there were no errors.
7) Transferred the base back to single user mode.
Everything ... the problem is defeated. Well, even in 1C I launched "Testing and Correction", everything went fine there too, it stopped swearing at a non-unique index.
3. If non-uniqueness lies in dates with zero values, then the problem is solved by creating a base with an offset parameter of 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into the MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the base is already created with offset 0, then create a new one with 2000.
1.2. If it is possible to work with the database in the file version, then perform Testing and Fixing, as well as Configuration - Checking the configuration - Checking the logical integrity of the configuration + Searching for incorrect links.
1.3. If there is no file variant, try loading from DT to a DB2 client/server variant (which is less picky about uniqueness), and then run Test and Repair and Configuration - Configuration Check - Configuration Logical Integrity Check + Bad Reference Finder.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you must enable tracing at boot time in the Profiler utility, or enable logging to the DBMSSQL and EXCP process event log.
2. If the problem of non-uniqueness manifests itself during the work of users:
2.1. Find the problematic request using the method of paragraph 1.4.
2.1.2. Sometimes an error occurs during the execution of requests, for example:
This error occurs due to the fact that in the module of the accumulation register "Working hours of employees of organizations" in the procedure "Register Recalculations" in the request there is no service word "DIFFERENT".
Code 1C v 8.x I.e. should be:
Request = New Request(
"CHOOSE DIFFERENT
| Basic.Individual,
. . . . .
In the latest released releases of ZUP and UPP, the error does not occur, because. it says "VARIOUS".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique entry.
2.2.1. "Fish" script for defining non-unique records using SQL:
SQL Code S_elect COUNT(*) Counter,<перечисление всех полей соответствующего индекса>from<имя таблицы>
GROUP BY<перечисление всех полей соответствующего индекса>
HAVING Counter > 1
2.2.2 Example. The index in the error is called "_Document140_VT1385_IntKeyIndNG".
List of table fields:
_Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394, _Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef
Please backup your database before following the procedure below.
Run in MS SQL Server Query Analyzer:
SQL Code S_elect count(*), _Document140_IDRRef, _KeyField
from _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1
Use it to find out the values of the columns _Document140_IDRRef, _KeyField, duplicate records (id, key).
With a request:
SQL Code S_elect *
from _Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or ...
look at the values of other columns of duplicate entries.
If both entries have meaningful values and these values are different, then fix the value of _KeyField to be unique. To do this, define the maximum occupied value of _KeyField(keymax):
SQL Code S_elect max(_KeyField)
from _Document140_VT1385
where _Document140_IDRRef = id1
Replace the _KeyField value in one of the duplicate entries with the correct one:
SQL code update _Document140_VT1385
set _KeyField = keymax + 1
Here _LineNo1386 = is an additional condition that allows you to select one of two duplicate entries.
If one (or both) of the duplicate entries has an obviously wrong value, then it should be removed:
SQL code delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1
If duplicate entries have the same values in all columns, then one of them must be left:
SQL Code S_elect distinct *
into #tmp1
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
Delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
I_nsert into _Document140_VT1385
S_elect #tmp1
D_rop table #tmp1
The described procedure must be performed for each pair of duplicate entries.
2.2.3. Second example:
SQL Code S_elect COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)
2.3.4 An example of defining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x CHOOSE Handbook. Link
FROM Directory. Directory AS Directory
GROUP BY
HAVING QUANTITY(*) > 1
An error occurs if some objects, attributes, subconto have a NULL value in the database, but they cannot have such a value. And this error appears only in SQL databases. Those. if such a database is unloaded into a file one, then this error will not be there. Because the file base has its own tables (4 in total), and SQL has its own. And the SQL database reacts critically to such values in its tables.
This problem is not solved by any tests (neither external nor internal) in any variants of databases (SQL or file) and even the _1sp_DBReindex Procedure in the SQL manager, which seems to be supposed to restructure tables in SQL.
Let's analyze the solution to the problem using the example of the transition from Accounting 3.0 PROF to CORP. After the transition, account 68.01 has a new subconto RegistrationIn Tax Authority. And then, in databases on SQL, all creation in the PROF version of the documents that use this account will not be rewired. The error shown above will come out. Because this new subconto for old documents, in postings, will be written with a NULL value (although it should be empty value, well, or somehow the tax authority).
To resolve this error, you need to remove NULL values where they should not be. In this case, in documents where the subconto RegistrationIn Tax Authority is used. You can do this by writing a processing that will replace NULL with an Empty value (ready processing can be downloaded from this article). Do processing, tk. an attempt to change the value of this subconto in document postings manually leads to the same error.
Processing for replacing NULL "s in all subcontacts RegistrationIn the Tax Authority can be downloaded from this article, below.
BUT it will not work to replace NULL in the SQL database, the same error will be generated during the processing. Therefore, you need to do this:
1. Upload the already working, translated to CORP version, SQL database into dt'snik (in the configurator Administration - Upload database - select where to upload the database in the form of a *.dt file)
2. Load dt’s into the file base (in an unnecessary or pre-prepared, clean, file database, in the configurator Administration - Load database - select the previously unloaded dt’s)
3. Perform processing in the file base (there will be no error and all NULLs will be correctly replaced) (how to perform processing is described below)
5. Now, on the contrary, unload the dt's from the file base and load it into the SQL database. Now, when posting processed documents, there will be no errors.
The processing from this article finds all documents for the specified period, in which the postings have the sub-contact RegistrationIn Tax Authority (which appears in the CORP version), which has a NULL value. And replaces this value with an empty value.
In the processing, you must specify the period for which you need to process the documents (it is possible for the entire period in which the records are kept in the database) and click "Fill in the tabular part". After that, you can check the checkboxes which documents to process (you can select all) and click the "Perform processing" button.
Accordingly, if someone has the same error, but NOT after switching to CORP, but for example after an exchange, loading some data, performing some processing, etc. Then it is necessary to identify where the NULL value was assigned in a particular document / directory and remove this NULL in a similar way, but by its own processing, but in the order as described above. Remember that NULL can be, as in document postings, incl. not only accounting, but somewhere on the form of a document / directory, in some details, but in this case it probably won’t even open.
Also, if you had this error when posting a document, after you transferred the Bukh CORP file base to SQL (and the database was once originally PROF), it means that those documents that were created in the PRO version are now also in the RegistrationIn Tax Authority subcont the NULL value and the SQL database does not accept this. And when loading the database in SQL, such an error will appear. Here, in fact, there will not be NULL values in the file base, but SQL will load exactly such values into its tables. Therefore, here it is necessary to force SQL database create these NULLs and then fix them in the file base. But I won’t tell you how to do this.
