An attempt was made to insert a non-unique value into a unique index. Error: Attempting to insert a non-unique value into a unique index: microsoft sql server. when switching from accounting professional to corporate and not only Remove non-unique indexes in 1c 8 file
You have received 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.
Solutions:
1. In SQL Server managment studio, we physically destroy the faulty index (in my case it was an index on the accounting register totals table). In 1C we will distribute the faulty documents. In testing and correction mode, check the boxes for table reindexing + recalculation of totals. 1C recreates the index without an error. We carry out previously failed documents.
2.
1) Using Management Studio 2005 generated a create script to create an index, which was buggy, and saved it to a file.
2) Manually killed the jamb 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 had 15 duplicate records displayed, although before step 2 the query did not return anything.
4) I went through all the entries and manually cleaned up duplicates. In fact, I also used the “Report Structure” processing to understand what I was dealing with. It turned out that the table _AccumRgTn19455 stores the accumulation register “Product output (tax accounting)”. I also tinkered with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were processed normally, without errors. Of course, you shouldn’t just clean tables at random: it’s important to understand what is being cleaned and how it can turn out.
5) Launched a request to create an index, which was saved in a file.
6) Switched the database to single-user mode and launched dbcc checkdb - this time no errors were generated.
7) Switched the base back to single-user mode.
That's it... the problem is overcome. Well, back in 1C I launched “Testing and Correction”, everything went fine there too, I stopped complaining about the non-unique index.
3. If the non-uniqueness lies in dates with zero values, then the problem is solved by creating a database with an offset parameter equal to 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into a MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the database has already been 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 Correction, 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 version, try loading from DT into a client-server version with DB2 (which is less demanding on uniqueness), and then perform Test and Correction, as well as Configuration - Verify Configuration - Check the Logical Integrity of the Configuration + Search for Invalid References.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you need to enable tracing in the Profiler utility during boot or enable recording in the DBMSSQL and EXCP process event log.
2. If the non-uniqueness problem occurs while users are working:
2.1. Find the problematic request using the method in paragraph 1.4.
2.1.2. Sometimes an error occurs while executing queries, for example:
This error occurs due to the fact that in the accumulation register module " Work time employees of organizations" in the "Register Recalculations" procedure, the request does not contain the service word "DIFFERENT".
Code 1C v 8.x I.e. should be:
Request = New request(
"SELECT VARIOUS
| Basic.Individual,
. . . . .
In the latest releases of ZUP and UPP, the error does not occur, because it says "DIFFERENT".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique record.
2.2.1. "Fish" script for identifying 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_RR Ref, _Fld1394,_Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RT Ref, _Fld22261_RRRef
Before performing the procedure below, do backup copy Database.
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 _Document140_IDRRef, _KeyField, duplicate records (id, key) columns.
Using the request:
SQL code S_elect *
from_Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or ...
look at the values of the other columns of the duplicate entries.
If both entries have meaningful values and the values are different, then change the _KeyField value to be unique. To do this, determine 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 repeating records.
If one (or both) of the duplicate entries has an obviously incorrect meaning, 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 you need to leave one of them:
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 records.
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 determining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x SELECT Directory.Link
FROM Directory.Directory AS Directory
GROUP BY Directory.Link
HAVING QUANTITY(*) > 1
This article will describe what to do if, when working with 1C:Enterprise 8.1, you encounter a message containing the lines:
Cannot insert duplicate key row in object
An attempt was made to insert a non-unique value into a unique index.
What is an index?
Indexes are a structure that allows rapid access to rows in a table based on the values of one or more of its columns.
An index contains keys, built from one or more columns of a table or view, and pointers that map to where the specified data is stored.
Indexes reduce the amount of data that must be read to return a result set.
Although an index is associated with a specific column (or columns) of a table, it is still a separate database object.
Indexes of tables in the 1C:Enterprise database are created implicitly when creating configuration objects, as well as during certain settings of configuration objects.
The physical essence of indexes in MS SQL Server 2005.
Physically the data is stored on 8Kb pages. Immediately after creation, while the table does not have indexes, the table looks like a heap of data. Records have no specific storage order.
When you want to access data, SQL Server will produce table scan(table scan). SQL Server scans the entire table to find the records it is looking for.
From here the basic functions of indexes become clear:
— increasing the speed of data access,
— support for data uniqueness.
Despite their advantages, indexes also have a number of disadvantages. The first one is indexes take up additional disk space and in random access memory. Every time you create an index, you store the keys in descending or ascending order, which can have a multi-level structure. And the larger/longer the key, the larger the index size. The second disadvantage is operations are slowing down inserting, updating and deleting records.
In the MS SQL Server 2005 environment, several types of indexes are implemented:
- non-clustered indexes;
- clustered (or clustered) indexes;
- unique indexes;
- indexes with included columns
- indexed views
- full text
Unique index
The uniqueness of the values in the indexed column is guaranteed by unique indexes. If they are present, the server will not allow you to insert a new value or change an existing value in such a way that as a result of this operation two identical values appear in the column.
A unique index is a kind of add-on and can be implemented for both clustered and non-clustered indexes. One table can have one unique clustered index and many unique nonclustered indexes.
Unique indexes should only be defined when truly necessary. To ensure data integrity on a column, you can define a UNIQUE or PRIMARY KEY integrity constraint rather than resorting to unique indexes. Using them solely to ensure data integrity is a waste of database space. In addition, CPU time is also spent on maintaining them.
1C:Enterprise 8.1, starting from version 8.1, actively uses clustered unique indexes. This means that when converting from 8.0 or migrating from 8.1.7 you may get a non-unique index error.
If the non-uniqueness lies in dates with zero values, then the problem is solved by creating a database with an offset parameter equal to 2000.
What to do?
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt file) into a MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the database has already been 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 Correction, 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 version, try loading from DT into a client-server version with DB2 (which is less demanding on uniqueness), and then perform Test and Correction, as well as Configuration - Verify Configuration - Check the Logical Integrity of the Configuration + Search for Invalid References.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you need to enable tracing in the Profiler utility during boot or enable recording in the DBMSSQL and EXCP technological event log.
1.5. If the node (exchange plans) is available, then perform the exchange. You can also additionally complete paragraph 2.3.5 before exchanging
2. If the non-uniqueness problem occurs while users are working:
2.1. Find the problematic request using the method in paragraph 1.4.
2.1.2. Sometimes an error occurs while executing queries, for example:
This error occurs due to the fact that in the accumulation register module “Working time of employees of organizations” in the “Register Recalculations” procedure, the service word “DIFFERENT” is not included in the request.
Those. should be:
Request = New Request(
"SELECT VARIOUS
| Basic.Individual,
In the latest releases of ZUP and UPP, the error does not occur, because it says “DIFFERENT”.
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique record.
2.2.1. "Fish" script for identifying non-unique records using SQL:
SELECT 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
Before performing the procedure below, please back up your database.
Run in MS SQL Server Query Analyzer:
select count(*), _Document140_IDRRef, _KeyField
from_Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1
Use it to find out the values of the _Document140_IDRRef, _KeyField, duplicate records (id, key) columns.
Using the request:
select *
from_Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or …
look at the values of the other columns of the duplicate entries.
If both entries have meaningful values and the values are different, then change the _KeyField value to be unique. To do this, determine the maximum occupied value of _KeyField (keymax):
select max(_KeyField)
from_Document140_VT1385
where _Document140_IDRRef = id1
Replace the _KeyField value in one of the duplicate entries with the correct one:
update_Document140_VT1385
set _KeyField = keymax + 1
Here _LineNo1386 = is an additional condition that allows you to select one of two repeating records.
If one (or both) of the duplicate entries has an obviously incorrect meaning, then it should be removed:
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1
If duplicate entries have the same values in all columns, then you need to leave one of them:
select distinct *
into #tmp1
from_Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1
insert into _Document140_VT1385
select #tmp1
drop table #tmp1
The described procedure must be performed for each pair of duplicate records.
2.2.3. Second example:
SELECT COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)
2.3.4 An example of determining non-unique records using a 1C:Enterprise query:
or for accounting
CHOOSE
Subquery.Period,
Subquery.Registrator,
<измерения>,
SUM(Subquery.Number of Records) AS Number of Records
FROM
(CHOOSE
Self-supporting. Period AS Period,
Self-supporting.Registrar AS Registrar,
<измерения>,
1 AS Number of Records
FROM
Accounting Register. Self-supporting AS Self-supporting) AS Subquery
GROUP BY
Subquery.Period,
Subquery.Registrator,
<измерения>
HAVING
SUM(Subquery.Number of Records) > 1
2.3.5 Make the subd index non-unique. Script the index using Management Studio.
2.3.6 A special case when exchanging in the RDB. The error occurs in “auxiliary” tables associated with the calculation of totals or analytics. For example:
Error when calling the context method (Write): Attempting to insert a non-unique value into a unique index:
Microsoft OLE DB Provider for SQL Server: Cannot insert duplicate key row in object 'dbo._AccntRegED10319' with unique index '_Accnt10319_ByPeriod_TRNRN'.
HRESULT=80040E2F, SQLSrvr: Error state=1, Severity=E, native=2601, line=1
In this case, before loading, turn off the use of totals, load the message, enable the use of totals and recalculate.
You have received 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.
Solutions:
1. In SQL Server managment studio, we physically destroy the faulty index (in my case it was an index on the accounting register totals table). In 1C we will distribute the faulty documents. In testing and correction mode, check the boxes for table reindexing + recalculation of totals. 1C recreates the index without an error. We carry out previously failed documents.
2.
1) Using Management Studio 2005, I generated a create script to create an index, which was buggy, and saved it to a file.
2) Manually killed the jamb 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 had 15 duplicate records displayed, although before step 2 the query did not return anything.
4) I went through all the entries and manually cleaned up duplicates. In fact, I also used the “Report Structure” processing to understand what I was dealing with. It turned out that the table _AccumRgTn19455 stores the accumulation register “Product output (tax accounting)”. I also tinkered with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were processed normally, without errors. Of course, you shouldn’t just clean tables at random: it’s important to understand what is being cleaned and how it can turn out.
5) Launched a request to create an index, which was saved in a file.
6) Switched the database to single-user mode and launched dbcc checkdb - this time no errors were generated.
7) Switched the base back to single-user mode.
That's it... the problem is overcome. Well, back in 1C I launched “Testing and Correction”, everything went fine there too, I stopped complaining about the non-unique index.
3. If the non-uniqueness lies in dates with zero values, then the problem is solved by creating a database with an offset parameter equal to 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into a MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the database has already been 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 Correction, 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 version, try loading from DT into a client-server version with DB2 (which is less demanding on uniqueness), and then perform Test and Correction, as well as Configuration - Verify Configuration - Check the Logical Integrity of the Configuration + Search for Invalid References.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you need to enable tracing in the Profiler utility during boot or enable recording in the DBMSSQL and EXCP process event log.
2. If the non-uniqueness problem occurs while users are working:
2.1. Find the problematic request using the method in paragraph 1.4.
2.1.2. Sometimes an error occurs while executing queries, for example:
This error occurs due to the fact that in the accumulation register module “Working time of employees of organizations” in the “Register Recalculations” procedure, the service word “DIFFERENT” is not included in the request.
Code 1C v 8.x I.e. should be:
Request = New Request(
"SELECT VARIOUS
| Basic.Individual,
. . . . .
In the latest releases of ZUP and UPP, the error does not occur, because it says "DIFFERENT".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique record.
2.2.1. "Fish" script for identifying 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_RR Ref, _Fld1394,_Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RT Ref, _Fld22261_RRRef
Before performing the procedure below, please back up your 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 _Document140_IDRRef, _KeyField, duplicate records (id, key) columns.
Using the 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 the other columns of the duplicate entries.
If both entries have meaningful values and the values are different, then change the _KeyField value to be unique. To do this, determine 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 upd ate _Document140_VT1385
set _KeyField = keymax + 1
Here _LineNo1386 = is an additional condition that allows you to select one of two repeating records.
If one (or both) of the duplicate entries has an obviously incorrect meaning, 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 you need to leave one of them:
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 records.
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 determining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x SELECT Directory.Link
FROM Directory.Directory AS Directory
GROUP BY Directory.Link
HAVING QUANTITY(*) > 1
Information taken from the site
You have received 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.
Solutions:
1. In SQL Server managment studio, we physically destroy the faulty index (in my case it was an index on the accounting register totals table). In 1C we will distribute the faulty documents. In testing and correction mode, check the boxes for table reindexing + recalculation of totals. 1C recreates the index without an error. We carry out previously failed documents.
2.
1) Using Management Studio 2005, I generated a create script to create an index, which was buggy, and saved it to a file.
2) Manually killed the jamb 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 had 15 duplicate records displayed, although before step 2 the query did not return anything.
4) I went through all the entries and manually cleaned up duplicates. In fact, I also used the “Report Structure” processing to understand what I was dealing with. It turned out that the table _AccumRgTn19455 stores the accumulation register “Product output (tax accounting)”. I also tinkered with sql queries, identified 15 non-unique documents, and after all the actions were completed, I checked in 1C that these documents were processed normally, without errors. Of course, you shouldn’t just clean tables at random: it’s important to understand what is being cleaned and how it can turn out.
5) Launched a request to create an index, which was saved in a file.
6) Switched the database to single-user mode and launched dbcc checkdb - this time no errors were generated.
7) Switched the base back to single-user mode.
That's it... the problem is overcome. Well, back in 1C I launched “Testing and Correction”, everything went fine there too, I stopped complaining about the non-unique index.
3. If the non-uniqueness lies in dates with zero values, then the problem is solved by creating a database with an offset parameter equal to 2000.
1. If the problem is loading the database, then:
1.1. If you are loading (using a dt-file) into a MS SQL Server database, then when creating the database, before loading, specify the date offset - 2000.
If the database has already been 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 Correction, 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 version, try loading from DT into a client-server version with DB2 (which is less demanding on uniqueness), and then perform Test and Correction, as well as Configuration - Verify Configuration - Check the Logical Integrity of the Configuration + Search for Invalid References.
1.4. To localize the problem, you can determine the data of the object whose loading failed. To do this, you need to enable tracing in the Profiler utility during boot or enable recording in the DBMSSQL and EXCP process event log.
2. If the non-uniqueness problem occurs while users are working:
2.1. Find the problematic request using the method in paragraph 1.4.
2.1.2. Sometimes an error occurs while executing queries, for example:
This error occurs due to the fact that in the accumulation register module “Working time of employees of organizations” in the “Register Recalculations” procedure, the service word “DIFFERENT” is not included in the request.
Code 1C v 8.x I.e. should be:
Request = New Request(
"SELECT VARIOUS
| Basic.Individual,
. . . . .
In the latest releases of ZUP and UPP, the error does not occur, because it says "DIFFERENT".
2.2. After finding the problematic index from the previous paragraph, you need to find a non-unique record.
2.2.1. "Fish" script for identifying 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_RR Ref, _Fld1394,_Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RT Ref, _Fld22261_RRRef
Before performing the procedure below, please back up your database.
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 _Document140_IDRRef, _KeyField, duplicate records (id, key) columns.
Using the request:
SQL code S_elect *
from_Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or ...
look at the values of the other columns of the duplicate entries.
If both entries have meaningful values and the values are different, then change the _KeyField value to be unique. To do this, determine 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 repeating records.
If one (or both) of the duplicate entries has an obviously incorrect meaning, 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 you need to leave one of them:
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 records.
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 determining non-unique records using a 1C:Enterprise query:
Code 1C v 8.x SELECT Directory.Link
FROM Directory.Directory AS Directory
GROUP BY Directory.Link
HAVING QUANTITY(*) > 1
