Netyjnd

In the event of Brexit being postponed beyond the EU elections, will UK voters in EU countries be eligible to participate?

Why does the frost depth increase when the surface temperature warms up?

Control width of columns in a tabular environment

Sort with assumptions

Can you take a "free object interaction" while incapacitated?

Checking @@ROWCOUNT failing

Reason why a kingside attack is not justified

Why is indicated airspeed rather than ground speed used during the takeoff roll?

Taking the numerator and the denominator

Does capillary rise violate hydrostatic paradox?

Output visual diagram of picture

categorizing a variable turns it from insignificant to significant

What is the period/term used describe Giuseppe Arcimboldo's style of painting?

Highest stage count that are used one right after the other?

Error in master's thesis, I do not know what to do

Strange behavior in TikZ draw command

What is the tangent at a sharp point on a curve?

PTIJ: Which Dr. Seuss books should one obtain?

How can I, as DM, avoid the Conga Line of Death occurring when implementing some form of flanking rule?

What is this high flying aircraft over Pennsylvania?

Why do Radio Buttons not fill the entire outer circle?

Why didn’t Eve recognize the little cockroach as a living organism?

is this saw blade faulty?

Why is "la Gestapo" feminine?

Checking @@ROWCOUNT failing

Using SSIS to get record counts for tables that may not existHandling exceptions in stored procedures called using insert-exec blocksSQL 2005 Unused proceduresDuplicate records in primary key during big selectHandling exceptions with INSERT-EXEC with linked server stored proc and table resultsBulk insert and fast ordered search large data 3B+ recordsExpensive Insert Operation pause Other Insert OperationProblems with insert trigger and primary keyImplicit conversion causing error part of the timeINSERT/SELECT xml column from one table to another

1

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  Have you tried wrapping this code inside a transaction? I'm wondering if some other process is sliding in between the update and the insert.
  
  – Scott Hodgin
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no transaction. My proc is like below, create procedure my_procname AS BEGIN set nocount on -- sp code here as in the question end
  
  – blue
  2 hours ago
  

  
  
  
  

add a comment | 

1

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  Have you tried wrapping this code inside a transaction? I'm wondering if some other process is sliding in between the update and the insert.
  
  – Scott Hodgin
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no transaction. My proc is like below, create procedure my_procname AS BEGIN set nocount on -- sp code here as in the question end
  
  – blue
  2 hours ago
  

  
  
  
  

add a comment | 

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

sql-server primary-key

share|improve this question

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
 insert into table1(id, value1,value2...)
 select (@id, @value1, @value2...)
 end 

share|improve this question

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 hours ago

blueblue

1061

New contributor

blue is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 hours ago

blueblue

1061

1 Answer
1

active

oldest

votes

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

blue is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232558%2fchecking-rowcount-failing%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

5

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

add a comment | 

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

set transaction isolation level serializable;
begin transaction;

update dbo.table1
 set value = @value
 where id = @id;

if (@@ROWCOUNT = 0)
begin
 insert dbo.table1(id, value1,value2...)
 values(@id, @value1, @value2...);
end 

commit transaction;

share|improve this answer

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490

answered 2 hours ago

Aaron Bertrand♦Aaron Bertrand

153k18294490