.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

with the transaction primary key violation error

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server


i notice with in the transaction primary key violation error but all data never rollback,


use tempdb
drop table T1
create table T1(c1 int primary key, c2 varchar(5));
select * from T1
begin tran
insert into T1 select 1,'A'
insert into T1 select 2,'B'
insert into T1 select 2,'B'
insert into T1 select 3,'D'
commit tran
select * from T1


i need to know what is the sql server logic behind this?


Tharindu Dhaneenja

Tharindu Dhaneenja (http://spaces.msn.com/dhaneenja)

View Complete Post

More Related Resource Links

CLR Trigger with stored procedures - transaction error

Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

Primary Key Constraint Violation on Update

When UPDATING a single column (NOT the primary key column) of a transactionally replicated table, within MS SQL Server Management Studio, the replication monitor gives  Violation of PRIMARY KEY constraint 'PK_my_table. Cannot insert duplicate key in object 'my_table'. Statement Delivery is via the standard SCALL of dbo.sp_MSupd_my_table. The problem correlates with Statement Delivery of DELETE operations. That is, the problem exists if I specify "Do not replicate Delete Statements" and goes away if I specify standard behaviour for delete i.e a call on dbo.sp_MSdel_My_Table dbo.sp_MSupd_my_table does not appear to invoke deletion in any way and Im not updating the primary key value Any ideas Henrietta

Transaction Error!!!

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-alt:"Calisto MT"; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-alt:"Century Gothic"; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> Main StoredProcedure STARTS Begin Try Begin Transaction                 Sub StoredPr

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ]

An Entity Data Model is used to generate the script to create  a SQL CE database.  It creates all of the 10+ tables and their primary keys without any problem. A lot of foreign key creations generate errors like the following: SQL Execution Error. Executed SQL statement ALTER TABLE [tblA] ADD CONSTRAINT [FK_tblA_tblB] FOREIGN KEY([A], [B], [C]) REFERENCES [tblB]([A], [BBB], [C]) Error Source: SQL Server Compact ADO.NET Data Provider Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ] The primary keys are there and correct. Could anyone offer some tips on how to figure out what is wrong?hz

error '8007202f' Automation error A constraint violation occurred

I have an old COM+ object that we've used to create AD user accounts with information from our HR system.  This process has been broken since a recent upgrade, so I'm attempting to modify the object to point to a new DC and a new inforamtion store during mailbox creation.  After modifying the code I am unable to get an account to be crated and the following error appears: error '8007202f' Automation error A constraint violation occurred I am not a developer and I know that this is a long shot but if anyone has any insight into if this is an AD issue or one with VB 6, any help at all would be greatly appreciated.  I think this is the relevent code but again, I'm an admin not a developer:     thenewuser.SetInfo        thenewuser.SetPassword "NewBie!!!"     thenewuser.AccountDisabled = False        Set objMailbox = thenewuser         objMailbox.CreateMailbox "LDAP://MyDCCN=Mailbox Store (MyExchServer),CN=First Storage Group,CN=InformationStore,CN=MARLEY,CN=Servers,CN=MyStuff,CN=Administrative Groups,CN=MyStuff,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=MyDomain,DC=com"thenewuser.SetInfo Thanks in advance for any help.

Violation of PRIMARY KEY constraint 'PK_droughtDataTable_1'.

 Hello, I have a droughtDataTableAdapter which has the sqlINSERT INTO [droughtDataTable] ([StateName], [Moist], [Drought], [region], [district], [ID]) VALUES (@StateName, @Moist, @Drought, @region, @district, @ID) In the code I have the codeDataSet1TableAdapters.droughtDataTableAdapter k1 = new DataSet1TableAdapters.droughtDataTableAdapter(); for (int a = 0; a <= 254; a++) { k1.FillDrought( k[a][0], double.Parse(k[a][9]), double.Parse(k[a][12]), 1, k[a][1],a); }   However I got an error Violation of PRIMARY KEY constraint 'PK_droughtDataTable_1'. Cannot insert duplicate key in object 'dbo.droughtDataTable'. The question is that I don't have 'droughtDataTable_1', where is from for "_1"? The definition of the table droughtDataTable isStateName nchar(10) Moist float Drought float region smallint district varchar(50) ID int (Primary Key)   Thanks for help.

Implementing Transaction in SSIS Package - [Execute SQL Task] Error: Failed to acquire connection "<

I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server. When I execute this package It works fine. So far so good. Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error. [Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection. "NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server. Please respond if anyone has any solution. Thanks! Anand

Violation of PRIMARY KEY constraint 'PK_User_Details'. Cannot insert duplicate key in object 'dbo

hi,i'm using sql server 2005 as backend for web development .In one of my database table User_Details, 'user_id' is the primary key ,and it is set to autoincrementing as follows.object count = objUser.countUid(); string count1 = Convert.ToString(count);            if (count1 == "0")            {                string uid5 = "U1";           objUser.addUser(string uid5);}else{               object uid = objUser.selectUid();                string usid = uid.ToString();                string uid2 = usid.Substring(1);                int uid3 = int.Parse(uid2);                uid3 += 1;                string uid4 = usid.Substring(0, 1);          

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object


i am unable to insert values into my table when i am using primary key and foreign key in my tables i am sure that i have not voilated the constraints rules and its working well when i remove the constraints.....

Serve to server Transaction Replication error


I have windows 2003 sp2 servers each hosts SQL Server 2008 SP1 standard edition. I am trying to replicate both servers, using the tutorial available in the online documentation <Tutorial: Replicating Data Between Continuously Connected Servers> which can be found at: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/9c55aa3c-4664-41fc-943f-e817c31aad5e.htm

I have created the users (repl_logreader, repl_distibution, repl_snapshot, and repl_merge and setting their security. Then I have created the distibutor, setting database security as mentioned in the document, and last creating the publication with a snapshot to be run immediately, distributor and publisher are on the same server and the publication will be pushed to the subscriber. Once the publication is created succesfully, I am checking the agents to see the errors. I am always stuck with the following two error messages:

1- snapshot agent error:

Error messages:

Message: Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1090".
The domain manager specified by the host could not be instantiated.
Failed to create AppDomain "mssqlsystemresource.dbo[runtime].1091".
The domain manager sp

WCF client with internet proxy server showing error The server committed a protocol violation. Secti


Our team trying to create a windows application(c#) to call a WCF service using internet proxy server

Showing exception "The server committed a protocol violation. Section=ResponseStatusLine" while calling WCF service

when i tried to solve the problem using this code then showing the same error


<httpWebRequest useUnsafeHeaderParsing="true" />


Please give suggestion to solve this problem/any alternative solution


Server crashing - Access Violation error



Would someone be able to help? We have a major problem with our SQL Server (2008 RC) installation. SQL Server keeps crashing. The key parts of the dump file seem to be these:

Log was backed up. Database: sage_broadata0506, creation date(time): 2010/06/30(13:26:32), first LSN: 18395:3126:1, last LSN: 18395:3126:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\Backup\sage_broadata0506\sage_broadata0506_backup_2010_10_14_085501_6709455.trn'}). This is an informational message only. No user action is required.
2010-10-14 08:55:03.37 spid61      Using 'dbghelp.dll' version '4.0.5'
2010-10-14 08:55:03.72 spid61      ***Stack Dump being sent to E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0112.txt
2010-10-14 08:55:03.74 spid61      SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2010-10-14 08:55:03.74 spid61      * *******************************************************************************
2010-10-14 08:55:03.74 spid61      *
2010-10-14 08:55:03.74 spid61      * BEGIN STACK DUMP:

Primary key violation on update statement for sqlserver2005 in asp.net


Violation of PRIMARY KEY constraint 'PK_StudentInf'. Cannot insert duplicate key in object 'dbo.StudentInf'.
The statement has been terminated.

the above message is appearing when i am upadating the record.

I am doing project  using asp.net 3.5 c#.net and sqlserver 2005,

i am executing sp for update using sqlhelper block, when i am updating record the above error occuring rarely(not for all records), if i update one record if get an error above second time it is updating nicely,

i am not able to finding the problem when and where it is occuring.

one thing here i am upadating primary column also like hallticket NO.

is there any problem if update primary column .

please tell me the solution it's urgent, and also it is on LIVE.

please give me solution as early as possible.

How to prevent uncommittable transaction and catch the error message when error occurs?


The situation is I have a daily batch job running SSIS package using the SQL Server Agent. In the daily batch job, the program will generate many dynamic SQL statements and put all SQL statements in a temp table #updateSQLTable. Then, use a cursor to loop through the temp table #updateSQLTable and execute the SQL statments.

The following is the code segment in the cursor:


begin tran t1

--start generating dynamic SQL statements


--after generating dynamic SQL statements

declare @updateSQL nvarchar(4000)

    FOR select updateSQL from #updateSQLTable where updateSQL is not null order by updateSQL
    OPEN mycursor
    FETCH NEXT FROM mycursor
    into @updateSQL

        BEGIN TRY
            --execute the generated dynamic Insert/Update SQL
            EXEC sp_executeSQL @updateSQL

        END TRY

Nested transactions, rollback causes error "Transaction count after EXECUTE indicates that a COMMIT



I have a stored proc that does something that requires a transaction, and so I've put in the begin tran, commit, and rollback on error logic in it. That works as expected. But what if I now want to use the proc in the context of a larger transaction, such as calling it multiple times or doing other things as part of the same transaction? This seems impossible to achieve without either raising an error (throwing an exception) or having SQL Server do so on my behalf, rather unhelpfully.

For instance, I wanted to test a proc and reckoned a simple way to do so would be to start a transaction, execute the procedure, and roll back the transaction if the procedure didn't abort it:

begin transaction 
exec someProc
if XACT_STATE() = 1 rollback

This works if someProc doesn't encounter any errors (so it begins and commits a transaction and the transaction count is 1 at entry and exit of the proc), but crashes and burns if the procedure rolls back the transaction, producing SQL error

Msg 266, Level 16, State 2, Procedure ExecuteImport, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

I don't understand why that is. To my mind, it is perfectly normal that the transaction count can go from any level at entry to ze

primary key violation but want to resume next


I need to automate a script that import some data to a sql server 2005 table. The table is in a database of a web application of a vendor, but we need to do some data integration with it. The table   basically stores data for login for the portal and matches with our AD account. The login we use is our students' parents email address.it will be mapped with our AD account.What I need to do is import parent email address from our student information system, then compare with this table, do update and delete and insert.

The applcation table is like this with a primary key, in our case we use emailaddress of parents as MappedUsername, and it is also the same for Emailaddress field.

CREATE TABLE [dbo].[Mapper_UserMaps](

[MappedUsername] [nchar](255)  NOT NULL,

[AdUsername] [nchar](255)  NULL,

[EmailAddress] [nchar](255)  NULL,

[Data1] [varchar](510)   NULL,

[Data2] [varchar](510)   NULL,

[Data3] [varchar](510)   NULL,

[Gnum] [smallint]  NULL,



[MappedUsername] ASC




The problem is since some of our parents share email address, so for insert to the table, it caused primary key violation issue

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend