MSDTC and Win2K3: transaction

Showing posts with label transaction. Show all posts

Friday, March 30, 2012

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>> Not sure if this is possible, but a client wants to now if we can use a
>> backup we made on 2/24
>> and then pull transaction out of the database using the daily
>> transaction log backups to get the database to be an effective copy of
>> what it looked like on 2/13/06.
>> We have all the daily transaction logs up to today and the 2/24/06 is
>> the latest backup we have.
>> Any help would be greatly appreciated.
>> Regards
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
Regards
No. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
>
|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Monday, March 19, 2012

Msg 7391 and distributed transactions

Hi,
We are wondering what setting needs to be changed to get a large distributed
transaction to run.
We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that can
run a large distributed transaction to another W2K3 server, upgraded from
W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
did notice a difference in a setting for MSDTC that we made the same but
this has not worked.
Is there something that we missed because the new server is not an upgraded
W2K3 server from W2K?
Thanks
Chris Wood
Alberta Department of Energy
CANADASee "How to enable network DTC access in Windows Server 2003"
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OsrpVxPvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are wondering what setting needs to be changed to get a large
> distributed
> transaction to run.
> We have a W2K3 server, upgraded from W2K with SQL2000 SP3A applied that
> can
> run a large distributed transaction to another W2K3 server, upgraded from
> W2K with SQL2000 SP3A applied. When we try the same transaction to a newly
> created W2K3 server with SQL2000 SP3A applied we get the 7391 message. We
> did notice a difference in a setting for MSDTC that we made the same but
> this has not worked.
> Is there something that we missed because the new server is not an
> upgraded
> W2K3 server from W2K?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>

Monday, March 12, 2012

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

Thanks

Chandrakant Karale.

Msg 3616 Transaction doomed, Triggers and Error logging

I am trying to use the uspLogError and uspPrintError stored procedures found in AdventureWorks and I have a problem. I have a stored procedure called Is_uspProcessLeads that processes some customer information that is in xml format. Part of the process is to check to make sure that the xml is well formed and if it is validated. Each step along the way, Is_uspProcessLeads updates a status field to indicate how far along in the process the xml file got, and if there is an error, to capture that error in a log file. The error handling process uses a try..catch logic.

Is_uspProcessLeads does it's processing against a table called [Is.Leads] which contains the following fields...
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlValidated typed Xml data type with a related xsd file
IsLead_LeadStatus_Id Status code field

So here's the problem. If I run Is_uspProcessLeads manually and I get an error (like not well formed) then it blows up as I would expect and logs the error correctly. But what I want to do is run Is_uspProcessLeads from the Insert trigger of [Is.Leads]. When I try that, I get an unexpected error in uspLogError when it tries to insert error information in the Error log table, therefore preventing me from logging my errors.

Here is the errors I get
V
Error: 3930
Severity: 16
State: 1
Procedure: IsuspLogError
Line: 43
ProcessLocationMsg: Confirm that the raw xml is well formed
Lead_Id: 20
ERROR_MESSAGE...
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
^
Msg 3616, Level 16, State 1, Line 12
Transaction doomed in trigger. Batch has been aborted.

Got any Ideas why this is happening?

I searched for the 3616 error message, and got nothing (I do however like the message about being doomed). It looks like SQL Server is treating the trigger event as a transaction

FYI: The error message inside the dashed lines comes from uspPrintError, which is executed inside the catch block of uspLogError

This error occurs when you try to make any modifications after a transaction that needs to be rolled back. You can detect this using XACT_STATE function in the CATCH block. If the value is -1 it indicates that the transaction cannot be committed and you need to issue a rollback so subsequent transactions cannot proceed. If you can post a simple repro of the problem it will be easier to suggest the modification.|||

The stored procedured Is_uspLogError already checks XACT_STATE Function, see below. Below are the relative three stored procs.

1) Is_trgLead_InsertTrigger is the trigger,

2) Is_uspProcessLeads has the try catch block of code and

3) Is_uspLogError logs the error

ALTER TRIGGER [Is_trgLead_InsertTrigger]
ON [dbo].[Is.Lead]
AFTER INSERT --FOR INSERT ?
AS
BEGIN
SET NOCOUNT ON; -- prevent extra result sets from interfering with SELECT statements.

-- =============================================
-- Declaration
-- =============================================
DECLARE @.ReturnCode int
DECLARE @.Lead_Id int
DECLARE @.FTP_Id int
DECLARE @.ProcessLocationMsg varchar(255)

Set @.ProcessLocationMsg = 'Initialization'
If (select Skip_IsLead_Trigger from dbo.[Is.Environment]) = 1
Return

SELECT
@.Lead_Id = IsLead_Id,
@.FTP_Id = IsLead_FTP_Id
from Inserted

Set @.ProcessLocationMsg = 'Call sproc IsProcessLeads'
EXEC @.ReturnCode = [Is_uspProcessLeads]
@.IsLead_Id = @.Lead_Id,
@.IsFTP_Id = @.FTP_Id

END

ALTER PROC [dbo].[Is_uspProcessLeads]
@.IsLead_Id int,
@.IsFTP_Id int
AS
/*

USE [Integration]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[IsProcessLeads]
@.IsLead_Id = 1,
@.IsFTP_Id = 1

SELECT 'Return Value' = @.return_value
GO

select * from dbo.[Is.ErrorLog]
Go

*/
BEGIN

--Declarations.

DECLARE @.FTP_TYPE_ID_AHG int -- American Home Guides
DECLARE @.FTP_TYPE_ID_BHI int -- Builder Homesite Inc
DECLARE @.FTP_TYPE_ID_HB int -- Homebuilder
DECLARE @.FTP_TYPE_ID_INEST int -- iNest
DECLARE @.FTP_TYPE_ID_RYLAND int -- Ryland.Com

DECLARE @.LEAD_STATUS_ID_START int
DECLARE @.LEAD_STATUS_ID_Completed int
DECLARE @.LEAD_STATUS_ID_XmlWellFormed int
DECLARE @.LEAD_STATUS_ID_XmlValid int
--SET @.LEAD_STATUS_ID_RaisedError int
DECLARE @.LEAD_STATUS_ID_EmailedCLEAR int
DECLARE @.LEAD_STATUS_ID_EmailedCustomer int

Declare @.CLEAREmail nvarchar(255)
Declare @.CLEAREmailMsg nvarchar(255)

Declare @.AutoResponseEmail nvarchar(255)
Declare @.AutoResponseEmailMsg nvarchar(255)

DECLARE @.ReturnCode int
Declare @.ProcessLocationMsg nvarchar(255)

--Initialization.
SET @.FTP_TYPE_ID_AHG = 1
SET @.FTP_TYPE_ID_BHI = 4
SET @.FTP_TYPE_ID_HB = 6
SET @.FTP_TYPE_ID_INEST = 7
SET @.FTP_TYPE_ID_RYLAND = 0

SET @.LEAD_STATUS_ID_START = 1
SET @.LEAD_STATUS_ID_Completed = 2
SET @.LEAD_STATUS_ID_XmlWellFormed = 3
SET @.LEAD_STATUS_ID_XmlValid = 4
--SET @.LEAD_STATUS_ID_RaisedError
SET @.LEAD_STATUS_ID_EmailedCLEAR = 6
SET @.LEAD_STATUS_ID_EmailedCustomer = 7

-- Confirm that the raw xml is well formed and valid, by updating the specific xml field
-- which is determined by the FTP_TYPE_ID, with the Raw xml

BEGIN TRY

Set @.ProcessLocationMsg = 'Confirm that the raw xml is well formed'
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

Set @.ProcessLocationMsg = 'Confirm that the well formed xml is valid'
IF @.IsFTP_Id = @.FTP_TYPE_ID_AHG
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_BHI
UPDATE [Is.Lead]
SET IsLead_XmlBHI = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_HB
UPDATE [Is.Lead]
SET IsLead_XmlHB = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_INest
UPDATE [Is.Lead]
SET IsLead_XmliNest = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Update status to show the xml is valid'
UPDATE [Is.Lead]
SET IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlValid
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Get the count of leads'
UPDATE [Is.Lead]
SET IsLead_CountOfLeads = ([dbo].[Is_ufnCountOfLeads](@.IsLead_Id, @.IsFTP_Id))
WHERE IsLead_Id = @.IsLead_Id

Proc_Exit:

END TRY
BEGIN CATCH
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

RETURN --@.@.ERROR

End

/*
IsuspLogError logs error information in the ErrorLog table about the
error that caused execution to jump to the CATCH block of a
TRY...CATCH construct. This should be executed from within the scope
of a CATCH block otherwise it will return without inserting error
information.
*/
ALTER PROCEDURE [dbo].[Is_uspLogError]
@.Lead_Id int,
@.ProcessLocationMsg varchar(255) = '',
@.ErrorLogID int = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by IsuspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;

-- Output parameter value of 0 indicates that error
-- information was not logged
SET @.ErrorLogID = 0;

BEGIN TRY
-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;

-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing IsuspLogError in order to successfully log error information.';
RETURN;
END

PRINT 'Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]'
INSERT [dbo].[Is.ErrorLog]
(
[IsErrorLog_UserName],
[IsErrorLog_Number],
[IsErrorLog_Severity],
[IsErrorLog_State],
[IsErrorLog_Procedure],
[IsErrorLog_Line],
[IsErrorLog_ProcessLocationMsg],
[IsErrorLog_Message],
[IsErrorLog_Lead_Id]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
@.ProcessLocationMsg,
ERROR_MESSAGE(),
@.Lead_Id);
-- Pass back the ErrorLogID of the row inserted
SET @.ErrorLogID = @.@.IDENTITY;
END TRY
BEGIN CATCH
PRINT 'Inside Isusp_LogError!Begin Catch ';
EXECUTE [dbo].[Is_uspPrintError] @.Lead_Id, @.ProcessLocationMsg;
RETURN -1;
END CATCH
END;

|||

FYI: I did some more testing to see what the value of XACT_STATE() is, and it doesn't change, it's always zero. Inside Is_uspLogError, I did a print before the Insert statement, and inside the Catch statement, and it was always 0.

Error inside Is_uspProcessLeads; calling Is_uspLogError

1 XACT_STATE(): 0

Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]

2 XACT_STATE(): 0

Inside Isusp_LogError!Begin Catch

|||

Since you are calling the SP inside the trigger all errors needs to be rolled back before you performing any writes. So in the Is_uspProcessLeads CATCH block you are missing the XACT_STATE() check and rollback. See below lines:

BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

Or you may just need to rollback the transaction without checking for XACT_STATE().

|||

Thanks for the response,

The problem with your solution is that I don't want to roll back the data that gets inserted in table [Is.Lead]. I want it there, so I can report what went wrong. If do a rollback, I will have an entry in the [Is.ErrorLog] table but nothing in [Is.Lead].

I guess my design is flawed, do you have any suggestions?

I think I need to remove the insert trigger, and have Is_uspProcessLeads executed some other way. I suppose I could do this by putting Is_uspProcessLeads on the scheduler, or do something with Services Broker.

Thanks

|||The issue is that when errors happen inside trigger most of them are of the transaction abort nature and the only recourse is to rollback the transaction. Since there is no support for autonomous transactions in SQL Server there is really no way to do what you are trying. Best is to move this logic to a stored procedure so that in case of an error you can catch it appropriately and take action. Note that this still depends on the setting of XACT_ABORT.|||

Im not seeing it, I need help. ANY help would be greatly appreciated

How can I get the try block in a trigger to stop throwing errors that im trying to skip over?

here is my example:

IF (OBJECT_ID ('cwtest') IS NOT NULL) drop table cwtest
go
create table cwtest (test varchar(20))
go
IF (OBJECT_ID ('cwtest_tr_IUD') IS NOT NULL) DROP trigger cwtest_tr_IUD
go
create trigger cwtest_tr_IUD on cwtest
for insert, update, delete
as
DECLARE
@.dml char(1)
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH
select 'why is the query completed with errors? Is this a bug?,This works fine anywhere else!'
end
go

insert into cwtest values('dd')
go

-- here is the base test works wonderfully!
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH;
select 'error is not returned,query executed successfully, whats up? '
end
go

and the output :

(0 row(s) affected)

why is the query completed with errors?

(1 row(s) affected)

Msg 3616, Level 16, State 1, Line 2

Transaction doomed in trigger. Batch has been aborted.

(0 row(s) affected)

error is not returned,query executed successfully, whats up?

(1 row(s) affected)

Friday, March 9, 2012

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/defaul...kb;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason'
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/default...b;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason?
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason'
Thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/default.aspx?scid=kb;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason'
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

MSDTC: Receive Returning Out Of Resources Error

HI,

I am getting an error on my sqlserver database server. My clients are
getting kicked out of transaction, giving an error
"New transaction cannot enlist in the specified transaction coordinator"

When i saw the event log on the DB server i found the following message

************************************
Event Type: Information

Event Source: MSDTC

Event Category: CM

Event ID: 4156

Date: 6/13/2005

Time: 9:30:48 AM

User: N/A

Computer: XXXXXXX

Description:

String message: ProcID= 0x780 QMGR::Receive Returning Out Of Resources
Error.

************************************************** ***************

I am using SQLServer 2000 Advanced server, on Windows 2000 Advanced server.

Thanks for help.

Indushekar.indushekara (indushekara@.gmail.com) writes:
> I am getting an error on my sqlserver database server. My clients are
> getting kicked out of transaction, giving an error
> "New transaction cannot enlist in the specified transaction coordinator"
> When i saw the event log on the DB server i found the following message
> ************************************
> Event Type: Information
> Event Source: MSDTC
> Event Category: CM
> Event ID: 4156
> Date: 6/13/2005
> Time: 9:30:48 AM
> User: N/A
> Computer: XXXXXXX
> Description:
> String message: ProcID= 0x780 QMGR::Receive Returning Out Of Resources
> Error.
> ************************************************** ***************

I have no idea what that means, but I would try stopping and restarting
MSDTC and hope for an easy way out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||indushekara (indushekara@.gmail.com) writes:
> I am getting an error on my sqlserver database server. My clients are
> getting kicked out of transaction, giving an error
> "New transaction cannot enlist in the specified transaction coordinator"
> When i saw the event log on the DB server i found the following message
> ************************************
> Event Type: Information
> Event Source: MSDTC
> Event Category: CM
> Event ID: 4156
> Date: 6/13/2005
> Time: 9:30:48 AM
> User: N/A
> Computer: XXXXXXX
> Description:
> String message: ProcID= 0x780 QMGR::Receive Returning Out Of Resources
> Error.
> ************************************************** ***************

I have no idea what that means, but I would try stopping and restarting
MSDTC and hope for an easy way out.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't know myself, but if you haven't installed Windows SP3, then
this KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;312947

If all else fails, you could reinstall MSDTC, but that seems rather
drastic, so it might be worth opening a case with Microsoft first.

http://support.microsoft.com/kb/279786/EN-US/

Simon|||I don't know myself, but if you haven't installed Windows SP3, then
this KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;312947

If all else fails, you could reinstall MSDTC, but that seems rather
drastic, so it might be worth opening a case with Microsoft first.

http://support.microsoft.com/kb/279786/EN-US/

Simon

Saturday, February 25, 2012

MSDTC forcing distributed transaction

We're performing the following query from a stored procedure on our
SQL Server database:
Begin Transaction
insert into local SQL Server table
select *
from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
When this runs, SQL Server escalates it to a distributed transaction
and MSDTC manages the transaction.
The problem is that this transaction is not always successfully
committed on the DB2 side. As part of trying to figure out why, we're
trying to figure out why this is even a distributed transaction, since
the only update is occuring locally. Why does this query force a
distributed transaction? Is there some way to reconfigure it so that
a distributed transaction is not forced?
Kees VanTilburg
VanTilburg Enterprises
Hello kees,
If I learned correctly that you want to know why the following sql
statements use the distributed transaction.
Begin Transaction
insert into local SQL Server table
select * from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
As far as I know, When you execute a distributed query while in a local
transaction. If the OLE DB data source supports the ITransactionJoin
interface, the transaction is promoted to a distributed transaction, even
if the query is a read-only query. Therefore, I am afraid there is not an
easy way to reconfigure so that a distributed transaction is not used in
this situation.
I found the following article for your reference.
274348 INF: Distributed Queries Executed Within a Trigger Enlist MSDTC
http://support.microsoft.com/?id=274348
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

MSDTC forcing distributed transaction

We're performing the following query from a stored procedure on our
SQL Server database:
Begin Transaction
insert into local SQL Server table
select *
from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
When this runs, SQL Server escalates it to a distributed transaction
and MSDTC manages the transaction.
The problem is that this transaction is not always successfully
committed on the DB2 side. As part of trying to figure out why, we're
trying to figure out why this is even a distributed transaction, since
the only update is occuring locally. Why does this query force a
distributed transaction? Is there some way to reconfigure it so that
a distributed transaction is not forced?
Kees VanTilburg
VanTilburg EnterprisesHello kees,
If I learned correctly that you want to know why the following sql
statements use the distributed transaction.
Begin Transaction
insert into local SQL Server table
select * from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
As far as I know, When you execute a distributed query while in a local
transaction. If the OLE DB data source supports the ITransactionJoin
interface, the transaction is promoted to a distributed transaction, even
if the query is a read-only query. Therefore, I am afraid there is not an
easy way to reconfigure so that a distributed transaction is not used in
this situation.
I found the following article for your reference.
274348 INF: Distributed Queries Executed Within a Trigger Enlist MSDTC
http://support.microsoft.com/?id=274348
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

MSDTC forcing distributed transaction

We're performing the following query from a stored procedure on our
SQL Server database:
Begin Transaction
insert into local SQL Server table
select *
from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
When this runs, SQL Server escalates it to a distributed transaction
and MSDTC manages the transaction.
The problem is that this transaction is not always successfully
committed on the DB2 side. As part of trying to figure out why, we're
trying to figure out why this is even a distributed transaction, since
the only update is occuring locally. Why does this query force a
distributed transaction? Is there some way to reconfigure it so that
a distributed transaction is not forced?
Kees VanTilburg
VanTilburg EnterprisesHello kees,
If I learned correctly that you want to know why the following sql
statements use the distributed transaction.
Begin Transaction
insert into local SQL Server table
select * from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
As far as I know, When you execute a distributed query while in a local
transaction. If the OLE DB data source supports the ITransactionJoin
interface, the transaction is promoted to a distributed transaction, even
if the query is a read-only query. Therefore, I am afraid there is not an
easy way to reconfigure so that a distributed transaction is not used in
this situation.
I found the following article for your reference.
274348 INF: Distributed Queries Executed Within a Trigger Enlist MSDTC
http://support.microsoft.com/?id=274348
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

MSDTC fails on Windows 2000 Advanced Server SP4

I have a VB6 application that uses MTS in a COM+ package for
transaction based processing over MSDTC. This application was working
fine when run from the SQL Server machine on Windows 2000 Advanced
Server until the latest security updates were installed. Now the
application fails to connect to SQL Server when the call is made inside
a transaction context within MTS/COM+
I have verified that MSDTC is started
The DTCTester tool runs successfully
RPC port 135 is open
This application runs fine on a Windows 2000 Server machine with SP4.
What is different about Windows 2000 Advanced Server such that it no
longer works after applying security updates (SP4)'Ä°s your SQL server and application server are in same domain, do you have
firewall etc between SQL Server and appl. server?
We had problems after we install Windows 2003 SP1 with MSDTC, we select No
Authantication Required and the problem is gone. Maybe because of you have
applied security patches you are having the same problem.
Check out:
http://support.microsoft.com/?id=883960
Hope this helps.
"dhalb@.comcast.net" wrote:
> I have a VB6 application that uses MTS in a COM+ package for
> transaction based processing over MSDTC. This application was working
> fine when run from the SQL Server machine on Windows 2000 Advanced
> Server until the latest security updates were installed. Now the
> application fails to connect to SQL Server when the call is made inside
> a transaction context within MTS/COM+
> I have verified that MSDTC is started
> The DTCTester tool runs successfully
> RPC port 135 is open
> This application runs fine on a Windows 2000 Server machine with SP4.
> What is different about Windows 2000 Advanced Server such that it no
> longer works after applying security updates (SP4)'
>

MSDTC Error

MSDTC error

Hi,
I have econnection code in front end app to do the update. I got the
following error,
The transaction manager has disabled its support for remote/network
transaction...
I checked app server the MSDTC has enabled
Any ideas why?
ThanksRead the replies...think youre answer in there.
Just google my friend ;)
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1
First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is
not running
If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.
On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.
Hope it helps,|||the server is virtual server running window 2000 terminal
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23myijFJ8HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have econnection code in front end app to do the update. I got the
> following error,
> The transaction manager has disabled its support for remote/network
> transaction...
> I checked app server the MSDTC has enabled
> Any ideas why?
> Thanks
>

MSDTC Error

Setup: Win2K Server, SQL server 2000 sp4
"Microsoft OLE DB Provider for SQL Server error '8004d00a'
New transaction cannot enlist in the specified transaction coordinator. "
I've seen the KB atricles if you have this problem on server 2003 but the
items in the steps don't exist in win 2k server.
(http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql)Hi Ben
Did you check out the information on the related article
http://support.microsoft.com/kb/306843/ ?
Is this using a linked server?
John
"Ben" wrote:
> Setup: Win2K Server, SQL server 2000 sp4
> "Microsoft OLE DB Provider for SQL Server error '8004d00a'
> New transaction cannot enlist in the specified transaction coordinator. "
> I've seen the KB atricles if you have this problem on server 2003 but the
> items in the steps don't exist in win 2k server.
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql)
>
>
>|||I am also having Linked Server.
but not able to get out of this problem
"John Bell" wrote:
> Hi Ben
> Did you check out the information on the related article
> http://support.microsoft.com/kb/306843/ ?
> Is this using a linked server?
> John
> "Ben" wrote:
> > Setup: Win2K Server, SQL server 2000 sp4
> >
> > "Microsoft OLE DB Provider for SQL Server error '8004d00a'
> > New transaction cannot enlist in the specified transaction coordinator. "
> >
> > I've seen the KB atricles if you have this problem on server 2003 but the
> > items in the steps don't exist in win 2k server.
> > (http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql)
> >
> >
> >
> >
> >|||Hi
Have you verified that DTC is working correctly? Have you tried using DTCPing?
John
"Labhesh Shrimali - Bangalore" wrote:
> I am also having Linked Server.
> but not able to get out of this problem
> "John Bell" wrote:
> > Hi Ben
> >
> > Did you check out the information on the related article
> > http://support.microsoft.com/kb/306843/ ?
> >
> > Is this using a linked server?
> >
> > John
> >
> > "Ben" wrote:
> >
> > > Setup: Win2K Server, SQL server 2000 sp4
> > >
> > > "Microsoft OLE DB Provider for SQL Server error '8004d00a'
> > > New transaction cannot enlist in the specified transaction coordinator. "
> > >
> > > I've seen the KB atricles if you have this problem on server 2003 but the
> > > items in the steps don't exist in win 2k server.
> > > (http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql)
> > >
> > >
> > >
> > >
> > >

Friday, March 30, 2012

MSSQL 2000 Restore - can I roll back Transaction Logs

MSSQL 2000 Restore - can I roll back Transaction Logs

MSSQL 2000 Restore - can I roll back Transaction Logs

Monday, March 19, 2012

Msg 7391 and distributed transactions

Monday, March 12, 2012

Msg 3910 Transaction context in use by another session.

Msg 3910 Transaction context in use by another session.

Msg 3910 Transaction context in use by another session.

Msg 3616 Transaction doomed, Triggers and Error logging

Friday, March 9, 2012

MSDTS Transaction Failed.

MSDTS Transaction Failed.

MSDTS Transaction Failed.

MSDTC: Receive Returning Out Of Resources Error

Saturday, February 25, 2012

MSDTC forcing distributed transaction

MSDTC forcing distributed transaction

MSDTC forcing distributed transaction

MSDTC fails on Windows 2000 Advanced Server SP4

MSDTC Error

MSDTC Error

MSDTC error

MSDTC Error

MSDTC and Win2K3

Blog Archive

About Me