Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Violation of PRIMARY KEY constraint Expand / Collapse
Author
Message
Posted Friday, February 6, 2009 2:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:24 AM
Points: 49, Visits: 526
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
Posted Friday, February 6, 2009 4:45 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, February 6, 2009 5:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, February 6, 2009 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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 Query Performance Tuning
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
Posted Sunday, February 8, 2009 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 1, 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 »

Add to briefcase

Permissions Expand / Collapse