July 6, 2009 at 3:16 pm
Hello,
I posted this earlier but got no responses. I'm posting again to see if someone else will see it.
I have one additional piece of information (added at the end of the this post) based on my efforts to trace the transaction via the MSDTC trace utility.
--------------------------
Background
--------------------------
We recently had an upgrade to our SAN storage that required a brief stop and restart of our SQL 2005 cluster services.
Since that time, we have started to receive reports that updates to a table ([dbo].[myTable] below) are failing.
The table in question has a trigger on it that updates a table on a linked server that we have set up.
--------------------------
What we've looked at
--------------------------
1. When the trigger is disabled, the updates work fine. When the trigger is enabled again, the errors start happening again.
2. I also believe the firewall settings are correct, though if anyone has specific advice on how to confirm this with a linked server, I would greatly appreciate it.
3. I've tried searching Google for the error without success. I see other errors that start with "The data in row 1 was not committed." But the specific Error Message is different (too many rows, etc.). There was one page that described the same error message - http://204.9.76.233/Community/forums/t/1187.aspx - but it had no details for a resolution.
--------------------------
Questions
--------------------------
1. Does anyone know why this is happening or how I can further troubleshoot?
2. Does the problem look like a server issue resulting from the storage upgrade? (Or the stop/start of the services?) Or is there something wrong in the trigger?
The details are below.
Thanks for any help.
webrunner
--------------------------
Details
--------------------------
Expected to see: Updated row
Saw instead:
1. A delay of about 30-40 seconds.
2. Then, this error:
====
Error:
No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.
Correct the errors and retry or pres ESC to cancel the change(s).
====
Here is the trigger code (names made generic, which I hope won't matter). Note that the SET NOCOUNT ON statement is commented out. That is the way I found the code. But testing showed that the error above happens whether or not SET NOCOUNT ON is commented out.
EDIT: Also note that the linked server is running SQL 2000.
/****** Object: Trigger [dbo].[myTrigger] Script Date: 06/18/2009 14:53:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[myTrigger]
ON [dbo].[myTable]
FOR INSERT, UPDATE AS
DECLARE @field1 varchar(40),
@u_id int,
@i_email varchar(255),
@d_email varchar(255),
@a_id int
--SET NOCOUNT ON
IF (SELECT COUNT(*) FROM INSERTED) > 1
AND EXISTS
(SELECT *
FROM inserted i,
myTable2 m2
where i.u_id = m2.u_id
AND m2.ad_in = 1)
BEGIN
RAISERROR ('Error - multiple rows',1,1)
RETURN
END
IF NOT EXISTS
(SELECT *
FROM inserted i,
myTable2 m2
WHERE i.u_id = m2.u_id
AND m2.ad_in = 1)
RETURN
SELECT
@i_email = i.email,
@field1 = m3.field1
FROM inserted i,
myTable3
WHERE i.u_id = m3.u_id
SELECT @d_email = email FROM DELETED
IF ISNULL(@d_email,'') <> ISNULL(@i_email,'')
BEGIN
SELECT @adp_id = m4.p_id
FROM remoteserver.db1.dbo.myTable3 m3,
remoteserver.db1.dbo.myTable4 m4
WHERE m3.u_id = m4.u_id
AND m3.field1 = @field1
UPDATE remoteserver.db1.dbo.myTable4
SET
email = @i_email,
update_time = GETDATE(),
update_source = 'LINKED TRIGGER'
WHERE p_id = @a_id
END
-------------------------
MSDTC Trace Results
-------------------------
I reproduced the problem a couple of times by trying to update a record, then dumped the MSDTC trace information to a file. The contents of the tracetx.log file that it generated are listed below, except that I obfuscated part of the GUID in each case. I don't see any obvious errors in the trace results.
I still see the original error in the SSMS GUI. However, does the fact that the trace for each transaction ends with the eventid "RM_ENLISTED_IN_TRANSACTION" and not with an error indicate a possible firewall problem? If so, what ports should I ask the network group to check? If not, what else might be the problem?
pid=256 ;tid=2956 ;time=07/06/2009-12:23:08.454 ;seq=1 ;eventid=TRANSACTION_BEGUN ;tx_guid=574c09a6-dc5f-4e48-b5df-nnnnnnnnnnnn ;"transaction got begun
pid=256 ;tid=2956 ;time=07/06/2009-12:23:08.454 ;seq=2 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=574c09a6-dc5f-4e48-b5df-nnnnnnnnnnnn ;"resource manager #1001 enlisted as transaction enlistment #1. RM guid = 'a8039894-db6a-4c2e-88a2-c83f4ebfe16b'"
pid=256 ;tid=2956 ;time=07/06/2009-16:55:33.763 ;seq=3 ;eventid=TRANSACTION_BEGUN ;tx_guid=77d2f101-7b59-4e02-8ab5-mmmmmmmmmmmm ;"transaction got begun
pid=256 ;tid=2956 ;time=07/06/2009-16:55:33.763 ;seq=4 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=77d2f101-7b59-4e02-8ab5-mmmmmmmmmmmm ;"resource manager #1001 enlisted as transaction enlistment #1. RM guid = 'a8039894-db6a-4c2e-88a2-c83f4ebfe16b'"
Also, I have tried issuing an update to a record in the linked server table directly (not using the trigger), and the update works OK. Could there be something about trying to make an update in a trigger that can cause a problem? But can anyone think of a reason why was this not a problem before the storage upgrade?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 6, 2009 at 3:42 pm
I have to say that I don't have any experience with clustering, but, based on what you are experiencing, I am wondering if the restart of the cluster services caused a failover to a different node and there is something not setup on the node, like impersonation/delegation, that was failed over to? It certainly doesn't seem like the the change to your SAN should cause it.
Can you run sp_testlinkedserver @servername on each of the nodes and see if it works for all of them?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 7, 2009 at 3:17 pm
Thanks for this information! I tried this command in master:
sp_testlinkedserver [mylinkedserver]
and got this response:
Command(s) completed successfully.
Not a 1 or a 0 as the documentation says are the possible responses.
Does "Command(s) completed successfully." equate to 0 (success), or am I doing something wrong?
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 7, 2009 at 3:29 pm
The procedure does not return a result set. The 0 or 1 is the RETURN value of the procedure. So you need to run it like this:
-- variable to hold the return value
DECLARE @retval INT
EXEC @retval = sp_testlinkedserver LinkedServerName
-- display the return value
SELECT @retval
If you did not get an error then it probably connected successfully, but I'd still try it again using the syntax above.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 7, 2009 at 3:37 pm
Jack Corbett (7/7/2009)
The procedure does not return a result set. The 0 or 1 is the RETURN value of the procedure. So you need to run it like this:
-- variable to hold the return value
DECLARE @retval INT
EXEC @retval = sp_testlinkedserver LinkedServerName
-- display the return value
SELECT @retval
If you did not get an error then it probably connected successfully, but I'd still try it again using the syntax above.
Thanks again. I did get a 0. That adds to the puzzle, because this is the linked server against which the timeout error occurs when the table update fires the trigger. I will continue to investigate, but if you have any other ideas I'd be very grateful.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply