Mryntu

Does capillary rise violate hydrostatic paradox?

Why do Radio Buttons not fill the entire outer circle?

Why didn't Voldemort know what Grindelwald looked like?

Would this string work as string?

What do the positive and negative (+/-) transmit and receive pins mean on Ethernet cables?

Comic-book: Kids find a dead female superhero in the woods

Find a point shared by maximum segments

Toggle window scroll bar

New Order #2: Turn My Way

Why would five hundred and five same as one?

What 1968 Moog synthesizer was used in the Movie Apollo 11?

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

What is this high flying aircraft over Pennsylvania?

Should a narrator ever describe things based on a character's view instead of facts?

Is divisi notation needed for brass or woodwind in an orchestra?

Is there a distance limit for minecart tracks?

Air travel with refrigerated insulin

categorizing a variable turns it from insignificant to significant

Make a Bowl of Alphabet Soup

Do native speakers use "ultima" and "proxima" frequently in spoken English?

How to get directions in deep space?

How can a new country break out from a developed country without war?

Offset in split text content

Put the phone down / Put down the phone

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