Store Procedure not run correctly (MSSQL Server 2012)

Dear Expert,

I am having issue that I have no idea why it happens:
- I have a rest api service waiting for http post.
- whenever received a http post , a store procedure called to update only one distinct row in a table. (Each http post contain distinct data to update)
- There might be multiple http posts received at the same time.
- As you know, to update db, I open a new sqlconnection and create new sql command.
- I found that almost rows got updated but some not to updated for no reason. I use profiler to see why that happens, Amazingly for me, when I copy store procedure text traced from profiler and run manually in management studio. those rows got updated okie.

So I have no idea why some rows not got updated. Please spark me some lights,

Thanks
David_DuongAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the only way I see to "trace" this is to have a "log" table, that your procedure always INSERTS into the data, along with the @@rowcount value of the update.
then, you can start to analyze that log table to see if there is any correlation of the rows that do not get updated.
David_DuongAuthor Commented:
Hi angelIII,

Sure, I already created "log" table to log any row that not got updated. So I am able to see any row with ExecuteNonQuery return 0 in that table.

So I have code to insert into log table that causes exception or row not got update.  Pseudocode as follow:

            SqlConnection conn = new SqlConnection(this.connection_string);
            try
            {
                conn.Open();
                
                // here I create new sql command store procedure type with parameterized parameters.
                
                cmd.CommandTimeout = 0;
                ret = cmd.ExecuteNonQuery();
                
                if (ret == 0) { // Insert to log table }
            }
            catch (Exception ex)
            {
                // insert in log table too, along with e.message
            }
            finally
            {
                conn.Close();
            }

Open in new window


Then based on that log table, I get the query that not update data and run its manually. The query runs okie and update 1 row as expected. I was thinking of locking or something in sql server, but I am not sure ...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, without seeing the actual "logic/code", I cannot help more.
the issue is either some of the typical "you are not looking at the same table/proc/database", or some logical issue which has a "timing" relation issue
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

David_DuongAuthor Commented:
Sure angellll,

I could upload the code but tell me which actual "logic/code" are you mentioning?

I could upload source code related but please do not share. If you have gmail that I could send source code should be great!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have UPDATE commands?
And the WHERE clause is correct? You can confirm that it will always find a record to be updated?
There are any errors?
David_DuongAuthor Commented:
Hi Vitor,

YES, this is all about UPDATE command.

I confirm that the WHERE clause is correct since I open SQL Profiler to record the UPDATE command text. Store procedure not run at the time of recording. However, after the session finished, I copy and pasted exact store procedure text and run manually in sql management studio, the UPDATE command update desire record.

I have try/catch in C# code but no exception occurs.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the stored procedure code here?
David_DuongAuthor Commented:
Hi Vitor, below is the code:

CREATE PROCEDURE [dbo].[sp_tblEmailApiSentTracking_Update_Verified]
	@MailingId varchar(100), 
	@MessageId varchar(100),
	@ToEmail varchar(100),
	@DateTimeLastUpdate DATETIME,
	@Delivered varchar(2)
AS 

BEGIN

	Update tblEmailApiSentTracking 
	SET DateTimeLastUpdate = @DateTimeLastUpdate,
	Delivered = @Delivered

	WHERE MailingId = @MailingId AND 
	MessageId = @MessageId AND
	ToEmail = @ToEmail

END

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
There's a way for you to add, and only for testing purposes, the following query to control the exact behavior of that SP?
IF NOT EXISTS (SELECT 1 FROM tblEmailApiSentTracking 
	WHERE MailingId = @MailingId AND MessageId = @MessageId AND ToEmail = @ToEmail)
        PRINT 'No update for MailingId = ' + @MailingId ' AND MessageId = ' + @MessageId + ' AND ToEmail = ' + @ToEmail
   ELSE
        Update tblEmailApiSentTracking 
	SET DateTimeLastUpdate = @DateTimeLastUpdate,
	Delivered = @Delivered
	WHERE MailingId = @MailingId AND 
	MessageId = @MessageId AND
	ToEmail = @ToEmail

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David_DuongAuthor Commented:
Thanks Vitor,  Follow your steps

I think I solved the issue.
David_DuongAuthor Commented:
Very helpful comment
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.