SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard Еще…Меньше
Проблемы
Предположим, что у вас есть резервная копия базы данных, содержащая объект последовательности, созданный с помощью параметра Cache в Microsoft SQL Server 2012. Затем вы пытаетесь восстановить базу данных из резервной копии. В этом случае может появиться следующее сообщение об ошибке:
Ошибка SQL 2627: нарушение УНИКАЛЬного ограничения ключа «%. * ls». Не удается вставить повторяющийся ключ в объект «%. * ls».
Причина
Эта проблема возникает из-за неправильной очистки кэшированной последовательности при выполнении резервного копирования базы данных. Это делает значение кэшированной последовательности больше значения на диске. В этой ситуации ошибка 2627 вызывается.
Решение
Сведения о накопительном пакете обновления
Накопительное обновление 4 для SQL Server 2012 с пакетом обновления 1 (SP1)
Исправление для этой проблемы впервые выпущено в накопительном обновлении 4. За дополнительными сведениями о том, как получить этот накопительный пакет обновления для SQL Server 2012 с пакетом обновления 1 (SP1), щелкните следующий номер статьи базы знаний Майкрософт:
2833645 Накопительный пакет обновления 4 для SQL Server 2012 с пакетом обновления 1 (SP1)Примечание. Поскольку сборки являются кумулятивными, каждый новый набор исправлений содержит все исправления и все исправления для системы безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2012 с пакетом обновления 1 (SP1). Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
2772858 Сборки SQL Server 2012, выпущенные после выпуска пакета обновления 1 (SP1) для SQL Server 2012
Статус
Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard More…Less
Symptoms
Assume that you have a database backup that contains a sequence object that is created by using the cache option in Microsoft SQL Server 2012. Then, you try to restore a database from the backup. In this situation, you may receive the following error message:
SQL Error 2627:
Violation of UNIQUE KEY constraint ‘%.*ls’. Cannot insert duplicate key in object ‘%.*ls’.
Cause
This issue occurs because the cached sequence is flushed incorrectly when you perform the database backup. This makes the value of the cached sequence larger than the value on the disk. In this situation, error 2627 is triggered.
Resolution
Cumulative update information
Cumulative Update 4 for SQL Server 2012 Service Pack 1
The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2012 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:
2833645 Cumulative update package 4 for SQL Server 2012 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 Service Pack 1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
SQL error 2627 is a common error that triggers during database restorations. It generally happens when the backup file contains a sequence object that is created by using the cache option in Microsoft SQL Server 2012
As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.
Let us today discuss the possible causes and fixes for this error.
What causes the SQL error 2627?
The SQL error 2627 triggers commonly on tables that contain PRIMARY KEY constraint. Whenever we try to insert a new record into such tables and the key of the record being inserted already exists in the table, it triggers the 2627 error.
It generally happens when the cached sequence created by the cache option in Microsoft SQL Server 2012 is flushed incorrectly during the database backup. This makes the value of the cached sequence larger than the value on the disk.
Thus when restoring the database with that backup, it shows the error message:
Another possible reason for it can be a bug in the spool and spool is generating more than expected rows. Let us now look into the steps to fix the violation of primary key constraint error.
How to fix the SQL error 2627?
To fix SQL Server Error 2627 you can use the Cumulative Update 4. It is recommended that you should use the most recent cumulative update that contains the hotfix.
Another possible fix would be to find the duplicate records in the database and remove them. We would need to use an SQL query for this purpose. The exact query to be used varies based on each scenario. For instance, a simple query to find if duplicates exist in a table would be:
SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING COUNT(*) > 1To display each of the duplicate rows, the format to be used would be:
SELECT a.* FROM users a JOIN (SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING count(*) > 1 ) b ON a.username = b.username AND a.email = b.email ORDER BY a.emailThe exact format of the query to be used varies based on the parameters in the table. Once the duplicate entry is identified, we can manually delete the duplicate ones using SQL Server Management Studio.
At times these steps may not help to fix the error completely and the violation error of the primary key constraint may remain the same. It happens only if the indexes were not created properly. In this situation, we need a backup plan of the database to restore the data.
Also, it is important to not rename an existing table as a backup of tables as this will leave the indexes in place which will prevent the recreation of the indexes during restoration.
[Need help to fix SQL errors? We are available 24×7]
Conclusion
In short, the SQL error 2627 triggers during database restorations when the backup file contains a sequence object that is created by using the cache option in Microsoft SQL Server 2012. Today we saw how our Support Engineers fix this error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
I need to catch violation of UNIQUE constraints in a special way by a C# application I am developing. Is it safe to assume that Error 2627 will always correspond to a violation of this kind, so that I can use
if (ThisSqlException.Number == 2627)
{
// Handle unique constraint violation.
}
else
{
// Handle the remaing errors.
}
?
Mark Sowul
10.2k1 gold badge44 silver badges51 bronze badges
asked Jun 26, 2011 at 11:47
2627 is unique constraint (includes primary key), 2601 is unique index
SELECT * FROM sys.messages
WHERE text like '%duplicate%' and text like '%key%' and language_id = 1033
answered Jun 26, 2011 at 12:19
gbngbn
420k81 gold badges585 silver badges674 bronze badges
4
Here is a handy extension method I wrote to find these:
public static bool IsUniqueKeyViolation(this SqlException ex)
{
return ex.Errors.Cast<SqlError>().Any(e => e.Class == 14 && (e.Number == 2601 || e.Number == 2627 ));
}
answered Dec 18, 2015 at 18:41
jhildenjhilden
12.1k5 gold badges52 silver badges76 bronze badges
1
Within an approximation, yes.
If you search the MS error and events site for SQL Server, error 2627, you should hopefully reach this page1, which indicates that the message will always concern a duplicate key violation (note which parts are parameterized, and which not):
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.
1As @2020-06-18, Database engine errors and events would be the correct page to go to
answered Jun 26, 2011 at 11:52
3
On Transact SQL language the Msg 2627 Level 14 — Violation of PRIMARY KEY constraint means that you cannot insert duplicate key in object.
Msg 2627 Level 14 Example:
We have the table TEST:
USE model;
GO
CREATE TABLE TEST(
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
BIRTHDAY date );
GO
| ID | NAME | BIRTHDAY |
|---|---|---|
| 1 | Tom | 1982-07-15 |
Invalid insert:
USE model;
GO
INSERT INTO TEST(id, name, birthday) VALUES (1, 'Tom','1982-07-15');
GO
| Message |
|---|
| Msg 2627, Level 14, State 1, Line 1 |
| Violation of PRIMARY KEY constraint ‘PK__TEST__3214EC27C70092B2’. Cannot insert duplicate key in object ‘dbo.TEST’. The duplicate key value is (1). The statement has been terminated. |
Correct insert:
USE model;
GO
INSERT INTO TEST(id, name, birthday) VALUES (2, 'Tom','1982-07-15');
GO
| Message |
|---|
| (1 row(s) affected) |
Other error messages:
- Specified scale is invalid
- Foreign key references invalid column in referenced table
- Table already has a primary key defined on it
- Column name does not exist in the target table or view
- Column names in each table must be unique
- There is already an object named in the database
- Could not find stored procedure