August 31, 2009 at 3:11 am
Dear all,
Can anyone help to list out possible rootcause that update command could fail to update a record?
Command:
update domsupdate set updated = @updated, LastUpdateTime = GETDATE() where reqid = @reqid and partnumber = @partnumber
Table domsupdate:
CREATE TABLE [dbo].[DOMSUpdate](
[ReqID] [int] NOT NULL,
[PartNumber] [varchar](10) NOT NULL,
[Description] [varchar](50) NOT NULL,
[Platform] [varchar](60) NOT NULL,
[LOBCode] [varchar](10) NULL,
[Status] [varchar](10) NOT NULL,
[ClassCode] [varchar](10) NULL,
[SubClass] [varchar](10) NULL,
[Updated] [varchar](2) NOT NULL,
[FamilyID] [smallint] NOT NULL,
[SectionID] [smallint] NOT NULL,
[LastUpdateTime] [datetime] NULL
) ON [PRIMARY]
The input parameter @reqid and @partnumber are correct but it failed to update sometimes.
I also used a trigger to keep the record before and after the update. Nothing wrong, for example, before updated = 'N', after updated = 'Y', but the record in table domsupdate still keep as 'N' and LastUpdateTime keep as null. That means the record was not updated.
Everytime, I run the same command catched by the profiler in the query window, it can be run successfully.
The update command is put in a transaction but I can't find any rollback record in the table to keep the trigger records of the table.
It made me confused for a long time. Any suggestion or help are highly appreciated.
Thank you.
coby
August 31, 2009 at 6:18 am
Assuming correct data in the parameters you're passing, this should update the data in the table. So, the problem has to exist elsewhere. What kind of trigger do you have on the table and what's the code in that trigger? If I were to point at anything as the source of the problem, this would probably be it. Are there other constraints on the table?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 1, 2009 at 3:26 am
Are your statistics up to date? Run this before your update:
UPDATE STATISTICS [tablename] WITH FULLSCAN
Once I encountered similar problems: affected rowcount seemed to be quite random on repeated runs with same data as I tried to update a table that had been heavily updated previously. Query above corrected that.
September 6, 2009 at 1:47 am
Hi, Fritchey,
Sorry for the late response. The trigger is as below and there is no constrains in this table.
USE [APJNPIDB]
GO
/****** Object: Trigger [dbo].[updated_statuslog] Script Date: 09/06/2009 15:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[updated_statuslog] ON [dbo].[DOMSUpdate]
FOR UPDATE
AS
insert into domsupdate_tracking
select [ReqID]
,[PartNumber]
,[Description]
,[Platform]
,[LOBCode]
,[Status]
,[ClassCode]
,[SubClass]
,[Updated]
,[FamilyID]
,[SectionID], 'OLD',getdate() from Deleted
insert into domsupdate_tracking
select [ReqID]
,[PartNumber]
,[Description]
,[Platform]
,[LOBCode]
,[Status]
,[ClassCode]
,[SubClass]
,[Updated]
,[FamilyID]
,[SectionID], 'NEW',getdate() from Inserted
GO
Hi, Salo,
I just tried your query command and found it will need some time to run it, about 1 second. My question is, since I need to update multi records at a time will this lead to performance slow down of the program?
BTW, I have daily job to rebuild index and update statistics.
Really appreciate you two's help.
Have a good day!
Thanks,
coby
September 6, 2009 at 5:53 am
When you say it failed to update, do you mean that it returned an error or that the query ran fine, but no data was updated?
There has to be an explanation. SQL Server doesn't just randomly skip updates. Either the code is wrong, but this looks OK, or the inputs are incorrect or there's something externally affecting the process, such as a trigger, constraint... It has to be something.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 6, 2009 at 6:54 am
It is the query ran fine, but no data was updated.
September 6, 2009 at 9:43 am
maybe there is yet another trigger ?
September 6, 2009 at 11:09 am
I queried with below but can find the only one above in the database. Anything I missed?
select * from sys.trigger
September 6, 2009 at 5:48 pm
You do realize that a Rollback will undo your triggers actions also, don't you?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 6, 2009 at 7:27 pm
Thanks for the reply.
Yes, I did put a transaction rollback in the code. My question is: will rollback be recorded by the trigger above as well? I asked this because I can't find anyrecord of the rollback.
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["domsConnectionString"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
SqlTransaction trans;
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.CommandText = "npitool_upd_domsitems";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@reqid", SqlDbType.Int);
cmd.Parameters.Add("@partnumber", SqlDbType.VarChar, 10);
cmd.Parameters.Add("@classcode", SqlDbType.VarChar, 10);
cmd.Parameters.Add("@subclass", SqlDbType.VarChar, 10);
cmd.Parameters.Add("@updated", SqlDbType.Char, 1);
cmd.Parameters[0].Value = formnum;
GridViewRow gv_row;
for (int i = 0; i < gvItem.Rows.Count; i++)
{
gv_row = gvItem.Rows;
cmd.Parameters[1].Value = ((Label)gv_row.FindControl("Item")).Text;
cmd.Parameters[2].Value = ((TextBox)gv_row.FindControl("txtClass")).Text;
cmd.Parameters[3].Value = ((TextBox)gv_row.FindControl("txtSubClass")).Text;
if (((CheckBox)gv_row.FindControl("chkUpdate")).Checked == true)
{
cmd.Parameters[4].Value = "Y";
}
else
{
cmd.Parameters[4].Value = "N";
}
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
string line = cmd.CommandText;
trans.Rollback();
throw (ex);
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
September 6, 2009 at 7:29 pm
The redoubtable G2 has two SSC articles on auditing which you may find rather interesting:
http://qa.sqlservercentral.com/articles/Auditing/63247/
http://qa.sqlservercentral.com/articles/Auditing/63248/
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 6, 2009 at 7:31 pm
As Barry said, a ROLLBACK undoes all changes - including the actions performed by the trigger.
You can do imaginative stuff with table variables, but I'm not going down that track here.
Read G2's articles.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply