Violation of PRIMARY KEY constraint

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Violation of PRIMARY KEY constraint

Violation of PRIMARY KEY constraint

Rate Topic

Display Mode

Topic Options

Author

Message

polo.csit

Posted Friday, February 06, 2009 2:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 6:40 AM
Points: 45, Visits: 502

Hai, ALl

my store procedure is:

CREATE PROC SP_TD_SURVEY_RESULTS
(
@pin_sParam varchar(10) ,
@pin_nResultID int ,
@pin_nReferenceID int ,
@pin_nSurveyID int ,
@pin_sConductedPlace varchar(20) ,
@pin_dtConductedPlace datetime ,
@pin_nActionOperatorID int ,
@pin_nMandalID int ,
@pin_nAssemblyID int
)
AS
BEGIN

--SV - Save New Agenda
IF @pin_sParam = 'SV'
BEGIN
IF @pin_nActionOperatorID NOT IN(33,36)
BEGIN
EXEC SP_TD_GEN_NEXTID 'GN', 'TD_SURVEY_RESULTS', 'nResultID', @pin_nResultID OUT

INSERT INTO TD_SURVEY_RESULTS (nResultID, nReferenceID,nSurveyID, sConductedPlace, dtConductedPlace , nSortOrder, bActive, nCreatedOperatorID, dtCreated,nMandalID,nAssemblyID)
VALUES (@pin_nResultID , @pin_nReferenceID, @pin_nSurveyID, @pin_sConductedPlace, @pin_dtConductedPlace, @pin_nResultID ,'Y', @pin_nActionOperatorID, getdate(),@pin_nMandalID,@pin_nAssemblyID)

SELECT @pin_nResultID 'nResultID', 'Success' 'sStatus'
END
ELSE
SELECT -999 'nResultID', 'You dont have write Permissions' 'sStatus'
END

END

Above storeprocedure i am using but in these application 50 users are used at a time.
The following the error is comming how to slove that one.......

Error Message:Violation of PRIMARY KEY constraint 'PK_TD_SURVEY_RESULTS'. Cannot insert duplicate key in object 'TD_SURVEY_RESULTS'. The statement has been terminated.
TraceLog:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at TDPORTAL.DataAccess.DAManager.ExecuteDataset(SqlCommand cmdObject) in D:\My Projects\TDPPORTAL2005\05-Development\SourceCode\TDDataAccess\DAManager.cs:line 40

regard's
polaiah M

Post #651462

vikas.saxena

Posted Friday, February 06, 2009 4:45 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 10:50 PM
Points: 14, Visits: 24

since more than one user is inserting record at a time so in your tabe duplicate values are going in primary key column. you can use transactions to check the error or can use locking so that only one person at a time can use your table...

i guess it will help you

Post #651511

Dbs-887309

Posted Friday, February 06, 2009 5:03 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 12, 2009 10:38 PM
Points: 12, Visits: 44

Before answering

What is the primary key of your table?

Is that user defined... or generated by any user defined function?
If So,
You may have to use the locking method.

Else

Try to set the primary key of your table with identity Insert - Yes

So it will automatically increment per insert..

Post #651518

Grant Fritchey

Posted Friday, February 06, 2009 6:30 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

The answer is the error message. You're trying to insert data that already exists and the primary key constraint, which ensures that the value in a column or columns is absolutely unique across all rows is preventing you. Figure out one of two things. 1) Is the primary key correct? 2) Is the data correct. One of the two is messed up.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #651563

gwinters421

Posted Sunday, February 08, 2009 3:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 01, 2010 7:38 AM
Points: 2, Visits: 16

It looks like the problem is in the [SP_TD_GEN_NEXTID] procedure. If it is getting the next ID by getting the current ID from a table, and is not locking the table to do so, 50 concurrent calls to that SP will get the exact same value.

Can you post the definition of [SP_TD_GEN_NEXTID]?

Post #652476

« Prev Topic | Next Topic »

Permissions