Violation of PRIMARY KEY constraint

Question

Violation of PRIMARY KEY constraint

polo.csit

SSCrazy

Points: 2574
More actions
February 6, 2009 at 2:27 am

#94769

Hai, ALl
my store procedure is:
CREATE PROC SP_TD_SURVEY_RESULTS
(
@pin_sParamvarchar(10) ,
@pin_nResultIDint,
@pin_nReferenceIDint,
@pin_nSurveyIDint,
@pin_sConductedPlacevarchar(20),
@pin_dtConductedPlacedatetime,
@pin_nActionOperatorIDint,
@pin_nMandalIDint,
@pin_nAssemblyIDint
)
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

vikas.saxena SSC Veteran Points: 286 More actions · Answer 1

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

Dbs-887309 Old Hand Points: 342 More actions · Answer 2

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..

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gwinters421 SSC Journeyman Points: 84 More actions · Answer 4

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]?