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 @retvalIf 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy