DTEXEC /Config

This was directly from BOL.

/Conf[igFile] filespec

(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.

Does this mean that I can specify which configuration file to use during runtime?

Or is just because I'm too desperate for that, I understood that way

Thanks

You are correct. You can specify which config file to use at run time via the command line switch /CONF|||

Phil Brammer wrote:

You are correct. You can specify which config file to use at run time via the command line switch /CONF

So if I use config1 as my package configuration file (using enable package configurations, this needs an absolute path and this is emdedded in the designer code, if I'm not wrong) during runtime I should be able to specify config2 as the package configuration file?

If this is possible, then I believe /conf flag ignores the config file passed.

Thanks

|||I believe if you are going to specify a config file from the command line, you would not use "enable package configurations".

If you have config1 in the enable package configurations dialog, and specify config2 on the command line, config2 will also be applied, but config1 should have precedence, I believe.|||

Let me try a sample package and see what happens.

Thanks for the clarifications, I wish BOL was more clear.

|||

I just tested this with a package that had a configuration (config1) set at design-time. The configuration was setting the value of a variable. I copied the configuration (config2), and changed the value that was being set. I then ran the package from the IDE and got the value from config1 (as expected). Then I ran it from DTEXEC, specifying config2 using the /conf switch and got the value from config2.

So it appears that configurations specified at runtime take precedence over the the configurations specified at design time.

|||

Can the config file be in the network?

|||

Karunakaran wrote:

Can the config file be in the network?

Sure, but it can't be a mapped drive if you are going to schedule the package.

You should be able to get away with \\server\share as the path to the config file, provided that the security on that network share is setup correctly.

DTCPing works, but distributed transaction cannot be started

Hi!
I'm getting an error when trying to start simple distributed
transaction.
Configuration is Win2k3/SQL2K <-> Win2k3 Cluster / SQL2K
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
DTCPing works in both directions, but there is one strange thing in log
on cluster server:
Cluster Environment detected
03-06, 06:52:43.296-->Invalid IP Address:207.x.x.253
(Mask:255.255.255.248) (38)
MSDTC Virtual Name:DTC1, IP:
When used internal an IP address, there is no such message.
Here is a full log of DTC ping on a cluster:
Platform:Windows 2003
cluster environment detected:
Windows 2003,sp1 environment is detected:
Reading MSDTC settings from cluster registry:
NetworkDtcAccess :true
NetworkDtcAccessAdmin :true
NetworkDtcAccessClients :true
NetworkDtcAccessTransactions:true
NetworkDtcAccessTip :false
XaTransactions :true
TurnOffRpcSecurity :true
NetworkDtcAccessOutbound :true
NetworkDtcAccessInbound :true
FallbackToUnsecureRPCIfNecessary:false
AllowOnlySecureRpcCalls :false
AccountName :NT AUTHORITY\NetworkService
IP Configure Information
Host Name . . . . . . . . . : HOSTA1
DNS Servers . . . . . . . . : 192.168.0.8
192.168.0.7
207.x.x.250
63.x.x.5
Node Type . . . . . . . . . :
NetBIOS Scope ID. . . . . . :
IP Routing Enabled. . . . . : no
WINS Proxy Enabled. . . . . : no
NetBIOS Resolution Uses DNS : no
Ethernet adapter {BBA542FC-1328-4246-A31B-16379406424B}:
Description . . . . . . . . : BASP Virtual Adapter
Physical Address. . . . . . : 00-10-18-14-4C-4E
DHCP Enabled. . . . . . . . : no
IP Address. . . . . . . . . : 192.168.0.103
Subnet Mask . . . . . . . . : 255.255.255.0
IP Address. . . . . . . . . : 192.168.0.101
Subnet Mask . . . . . . . . : 255.255.255.0
IP Address. . . . . . . . . : 192.168.0.100
Subnet Mask . . . . . . . . : 255.255.255.0
IP Address. . . . . . . . . : 192.168.0.105
Subnet Mask . . . . . . . . : 255.255.255.0
IP Address. . . . . . . . . : 192.168.0.107
Subnet Mask . . . . . . . . : 255.255.255.0
IP Address. . . . . . . . . : 192.168.0.8
Subnet Mask . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . :
DHCP Server . . . . . . . . : 255.255.255.255
Primary WINS Server . . . . : 0.0.0.0
Secondary WINS Server . . . : 0.0.0.0
Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
Ethernet adapter {52ED9D48-488E-4265-AC55-3CE5BBFDBF23}:
Description . . . . . . . . : Broadcom NetXtreme Gigabit
Ethernet
Physical Address. . . . . . : 00-14-22-73-F9-C9
DHCP Enabled. . . . . . . . : no
IP Address. . . . . . . . . : 207.x.x.253
Subnet Mask . . . . . . . . : 255.255.255.248
IP Address. . . . . . . . . : 207.x.x.251
Subnet Mask . . . . . . . . : 255.255.255.248
IP Address. . . . . . . . . : 207.x.x.250
Subnet Mask . . . . . . . . : 255.255.255.248
Default Gateway . . . . . . : 207.x.x.249
DHCP Server . . . . . . . . : 255.255.255.255
Primary WINS Server . . . . : 0.0.0.0
Secondary WINS Server . . . : 0.0.0.0
Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
++++++++++++lmhosts.sam++++++++++++
++++++++++++hosts ++++++++++++
127.0.0.1 localhost
193.x.x.147 HOSTB1
Cluster Environment detected
03-06, 06:52:43.296-->Invalid IP Address:207.x.x.253
(Mask:255.255.255.248) (38)
MSDTC Virtual Name:DTC1, IP:
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for
++++++++++++++++++++++++++++++++++++++++++++++
Firewall Port Settings:
Port:5000-5200
RPC server is ready
03-06, 06:52:51.078-->RPC server: received following information:
Network Name: HOSTA1
Source Port: 5069
Partner LOG: HOSTB15232.log
Partner CID: CA74E8B7-5274-40DF-97F0-CCF7B89895A9
++++++++++++Start Reverse Bind Test+++++++++++++
Received Bind call from HOSTB1
Network Name: HOSTA1
Source Port: 5069
Hosting Machine:
03-06, 06:52:51.296-->Trying to Reverse Bind to HOSTB1...
Test Guid:CA74E8B7-5274-40DF-97F0-CCF7B89895A9
Name Resolution:
HOSTB1-->193.x.x.147-->HOSTB1
Reverse Binding success: -->HOSTB1
++++++++++++Reverse Bind Test ENDED++++++++++
03-06, 06:52:52.296-->Called POKE from Partner:HOSTB1
Network Name: HOSTA1
Source Port: 5069
Hosting Machine:
++++++++++++Validating Remote Computer Name++++++++++++
03-06, 06:52:55.140-->Start DTC connection test
Name Resolution:
HOSTB1-->193.x.x.147-->HOSTB1
03-06, 06:52:55.156-->Start RPC test (-->HOSTB1)
RPC test is successful
Partner's CID:CA74E8B7-5274-40DF-97F0-CCF7B89895A9
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to HOSTB1
03-06, 06:52:55.296--> Initiating DTC Binding Test...
Test Guid:66C8FDED-CEC3-4C56-A4F6-F6EA236DAD5F
Received reverse bind call from HOSTB1
Network Name: HOSTA1
Source Port: 5069
Hosting Machine:
Binding success: -->HOSTB1
++++++++++++DTC Binding Test END+++++++++++++
Anyone can help me?
|||See if this helps.
HOWTO: Enable DTC Between Web Servers and SQL Servers Running Windows Server
2003
http://support.microsoft.com/kb/555017/en-us
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<maqdev@.gmail.com> wrote in message
news:1141651435.453798.152890@.u72g2000cwu.googlegr oups.com...
> Hi!
> I'm getting an error when trying to start simple distributed
> transaction.
> Configuration is Win2k3/SQL2K <-> Win2k3 Cluster / SQL2K
> ----
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> ----
> DTCPing works in both directions, but there is one strange thing in log
> on cluster server:
> ----
> Cluster Environment detected
> 03-06, 06:52:43.296-->Invalid IP Address:207.x.x.253
> (Mask:255.255.255.248) (38)
> MSDTC Virtual Name:DTC1, IP:
> ----
> When used internal an IP address, there is no such message.
> Here is a full log of DTC ping on a cluster:
> ----
> Platform:Windows 2003
> cluster environment detected:
> Windows 2003,sp1 environment is detected:
> Reading MSDTC settings from cluster registry:
> NetworkDtcAccess :true
> NetworkDtcAccessAdmin :true
> NetworkDtcAccessClients :true
> NetworkDtcAccessTransactions:true
> NetworkDtcAccessTip :false
> XaTransactions :true
> TurnOffRpcSecurity :true
> NetworkDtcAccessOutbound :true
> NetworkDtcAccessInbound :true
> FallbackToUnsecureRPCIfNecessary:false
> AllowOnlySecureRpcCalls :false
> AccountName :NT AUTHORITY\NetworkService
> IP Configure Information
> Host Name . . . . . . . . . : HOSTA1
> DNS Servers . . . . . . . . : 192.168.0.8
> 192.168.0.7
> 207.x.x.250
> 63.x.x.5
> Node Type . . . . . . . . . :
> NetBIOS Scope ID. . . . . . :
> IP Routing Enabled. . . . . : no
> WINS Proxy Enabled. . . . . : no
> NetBIOS Resolution Uses DNS : no
>
> Ethernet adapter {BBA542FC-1328-4246-A31B-16379406424B}:
> Description . . . . . . . . : BASP Virtual Adapter
> Physical Address. . . . . . : 00-10-18-14-4C-4E
> DHCP Enabled. . . . . . . . : no
> IP Address. . . . . . . . . : 192.168.0.103
> Subnet Mask . . . . . . . . : 255.255.255.0
> IP Address. . . . . . . . . : 192.168.0.101
> Subnet Mask . . . . . . . . : 255.255.255.0
> IP Address. . . . . . . . . : 192.168.0.100
> Subnet Mask . . . . . . . . : 255.255.255.0
> IP Address. . . . . . . . . : 192.168.0.105
> Subnet Mask . . . . . . . . : 255.255.255.0
> IP Address. . . . . . . . . : 192.168.0.107
> Subnet Mask . . . . . . . . : 255.255.255.0
> IP Address. . . . . . . . . : 192.168.0.8
> Subnet Mask . . . . . . . . : 255.255.255.0
> Default Gateway . . . . . . :
> DHCP Server . . . . . . . . : 255.255.255.255
> Primary WINS Server . . . . : 0.0.0.0
> Secondary WINS Server . . . : 0.0.0.0
> Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
> Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
> Ethernet adapter {52ED9D48-488E-4265-AC55-3CE5BBFDBF23}:
> Description . . . . . . . . : Broadcom NetXtreme Gigabit
> Ethernet
> Physical Address. . . . . . : 00-14-22-73-F9-C9
> DHCP Enabled. . . . . . . . : no
> IP Address. . . . . . . . . : 207.x.x.253
> Subnet Mask . . . . . . . . : 255.255.255.248
> IP Address. . . . . . . . . : 207.x.x.251
> Subnet Mask . . . . . . . . : 255.255.255.248
> IP Address. . . . . . . . . : 207.x.x.250
> Subnet Mask . . . . . . . . : 255.255.255.248
> Default Gateway . . . . . . : 207.x.x.249
> DHCP Server . . . . . . . . : 255.255.255.255
> Primary WINS Server . . . . : 0.0.0.0
> Secondary WINS Server . . . : 0.0.0.0
> Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
> Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
>
> ++++++++++++lmhosts.sam++++++++++++
>
> ++++++++++++hosts ++++++++++++
> 127.0.0.1 localhost
> 193.x.x.147 HOSTB1
>
> Cluster Environment detected
> 03-06, 06:52:43.296-->Invalid IP Address:207.x.x.253
> (Mask:255.255.255.248) (38)
> MSDTC Virtual Name:DTC1, IP:
> ++++++++++++++++++++++++++++++++++++++++++++++
> DTCping 1.9 Report for
> ++++++++++++++++++++++++++++++++++++++++++++++
> Firewall Port Settings:
> Port:5000-5200
> RPC server is ready
> 03-06, 06:52:51.078-->RPC server: received following information:
> Network Name: HOSTA1
> Source Port: 5069
> Partner LOG: HOSTB15232.log
> Partner CID: CA74E8B7-5274-40DF-97F0-CCF7B89895A9
> ++++++++++++Start Reverse Bind Test+++++++++++++
> Received Bind call from HOSTB1
> Network Name: HOSTA1
> Source Port: 5069
> Hosting Machine:
> 03-06, 06:52:51.296-->Trying to Reverse Bind to HOSTB1...
> Test Guid:CA74E8B7-5274-40DF-97F0-CCF7B89895A9
> Name Resolution:
> HOSTB1-->193.x.x.147-->HOSTB1
> Reverse Binding success: -->HOSTB1
> ++++++++++++Reverse Bind Test ENDED++++++++++
> 03-06, 06:52:52.296-->Called POKE from Partner:HOSTB1
> Network Name: HOSTA1
> Source Port: 5069
> Hosting Machine:
> ++++++++++++Validating Remote Computer Name++++++++++++
> 03-06, 06:52:55.140-->Start DTC connection test
> Name Resolution:
> HOSTB1-->193.x.x.147-->HOSTB1
> 03-06, 06:52:55.156-->Start RPC test (-->HOSTB1)
> RPC test is successful
> Partner's CID:CA74E8B7-5274-40DF-97F0-CCF7B89895A9
> ++++++++++++RPC test completed+++++++++++++++
> ++++++++++++Start DTC Binding Test +++++++++++++
> Trying Bind to HOSTB1
> 03-06, 06:52:55.296--> Initiating DTC Binding Test...
> Test Guid:66C8FDED-CEC3-4C56-A4F6-F6EA236DAD5F
> Received reverse bind call from HOSTB1
> Network Name: HOSTA1
> Source Port: 5069
> Hosting Machine:
> Binding success: -->HOSTB1
> ++++++++++++DTC Binding Test END+++++++++++++
>

DTC Error and unresolved SQL transaction

I have a procedure that reads data from linked server, a SQL2005 box, and
writes a row in a SQL2000 database. This procedure and configuration have
been working successfully for several years. This Sunday at 4am, this
procedure failed to complete, leaving an unresolved transaction. The symptom
is an insert on this table will timeout and fail because the unresolved
transaction has a lock on the table, and it shows as a blocking transaction.
Otherwise the database is functional and responsive. I have tried to KILL the
unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
under the Activity Monitor command column.
I had this same problem last weekend, and re-starting the SQL Server Service
resolved the transaction. However, this is not a viable option during
production hours.
I tried using "KILL 51 WITH STATUSONLY", and it returned:
SPID 51: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
It it cam back with:
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
being used by another user. KILL command failed.
My two questions are:
1) Is there anyway to clear this blocking transaction short of re-starting
the SQL Server?
2) Is there anyway to figure out the root cause of the problem? I believe it
is some sort of MSDTC issue, that seems to only happen early on Sunday
mornings.
The following DTC error happened at exactly the same timestamp as the SQL
procedure was executed.
___________________________________________
Application Event Log Error
___________________________________________
Date7/1/2007 4:09:31 AM
LogWindows NT (Application)
SourceMSDTC
Category(3)
Event3221229829
ComputerSERVER002
Message
The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the
event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
___________________________________________
Thank you in advance,
Ken
Hi Ken
"KenL" wrote:

> I have a procedure that reads data from linked server, a SQL2005 box, and
> writes a row in a SQL2000 database. This procedure and configuration have
> been working successfully for several years. This Sunday at 4am, this
> procedure failed to complete, leaving an unresolved transaction. The symptom
> is an insert on this table will timeout and fail because the unresolved
> transaction has a lock on the table, and it shows as a blocking transaction.
> Otherwise the database is functional and responsive. I have tried to KILL the
> unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> under the Activity Monitor command column.
> I had this same problem last weekend, and re-starting the SQL Server Service
> resolved the transaction. However, this is not a viable option during
> production hours.
> I tried using "KILL 51 WITH STATUSONLY", and it returned:
> SPID 51: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> It it cam back with:
> Server: Msg 6114, Level 16, State 1, Line 1
> Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> being used by another user. KILL command failed.
> My two questions are:
> 1) Is there anyway to clear this blocking transaction short of re-starting
> the SQL Server?
> 2) Is there anyway to figure out the root cause of the problem? I believe it
> is some sort of MSDTC issue, that seems to only happen early on Sunday
> mornings.
> The following DTC error happened at exactly the same timestamp as the SQL
> procedure was executed.
> ___________________________________________
> Application Event Log Error
> ___________________________________________
> Date7/1/2007 4:09:31 AM
> LogWindows NT (Application)
> SourceMSDTC
> Category(3)
> Event3221229829
> ComputerSERVER002
> Message
> The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display the message, or you may not have permission to
> access them. The following information is part of the
> event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> ___________________________________________
> Thank you in advance,
> Ken
I am not a MSDTC expert!!! Which process did you kill? I would expect a
process on the remote and local (originator) machines, and if there was an
order to be killed then local would be the first. If you stopped the DTC
services (NET STOP MSDTC) it should also rollback, but all distributed
transactions would be affected.
Is this the only time distributed transaction are used? If not then it would
narrow the issue down to either something with the process or something that
happens at that time. If the process is scheduled and works at other times
then it would rule the process out. If it is something that happens at a
specific time, check things like firewalls or antivirus updates/scans etc.
http://support.microsoft.com/default.aspx/kb/306843
Also look for blocking occuring during the process and how you handle errors
such as deadlocks in the code.
You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
check that DTC works ok.
John
|||Thank you for the response John.
<Is this the only time distributed transaction are used?
No, there are many procedures on this server that link to databases on
another server. The stored procedure that is failing runs hundreds of times
in a day. It had been reliable for years, up until last Sunday and this
Sunday when I have seen the two failures
<Which process did you kill?
I killed the spid on the SQL server initiating the link
I will review the kb's you referenced
Thanks,
Ken
"John Bell" wrote:

> Hi Ken
> "KenL" wrote:
>
> I am not a MSDTC expert!!! Which process did you kill? I would expect a
> process on the remote and local (originator) machines, and if there was an
> order to be killed then local would be the first. If you stopped the DTC
> services (NET STOP MSDTC) it should also rollback, but all distributed
> transactions would be affected.
> Is this the only time distributed transaction are used? If not then it would
> narrow the issue down to either something with the process or something that
> happens at that time. If the process is scheduled and works at other times
> then it would rule the process out. If it is something that happens at a
> specific time, check things like firewalls or antivirus updates/scans etc.
> http://support.microsoft.com/default.aspx/kb/306843
> Also look for blocking occuring during the process and how you handle errors
> such as deadlocks in the code.
> You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
> check that DTC works ok.
> John

DTC Error and unresolved SQL transaction

I have a procedure that reads data from linked server, a SQL2005 box, and
writes a row in a SQL2000 database. This procedure and configuration have
been working successfully for several years. This Sunday at 4am, this
procedure failed to complete, leaving an unresolved transaction. The symptom
is an insert on this table will timeout and fail because the unresolved
transaction has a lock on the table, and it shows as a blocking transaction.
Otherwise the database is functional and responsive. I have tried to KILL th
e
unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
under the Activity Monitor command column.
I had this same problem last weekend, and re-starting the SQL Server Service
resolved the transaction. However, this is not a viable option during
production hours.
I tried using "KILL 51 WITH STATUSONLY", and it returned:
SPID 51: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
It it cam back with:
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00}
is
being used by another user. KILL command failed.
My two questions are:
1) Is there anyway to clear this blocking transaction short of re-starting
the SQL Server?
2) Is there anyway to figure out the root cause of the problem? I believe it
is some sort of MSDTC issue, that seems to only happen early on Sunday
mornings.
The following DTC error happened at exactly the same timestamp as the SQL
procedure was executed.
________________________________________
___
Application Event Log Error
________________________________________
___
Date 7/1/2007 4:09:31 AM
Log Windows NT (Application)
Source MSDTC
Category (3)
Event 3221229829
Computer SERVER002
Message
The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
found. The local computer may not have the necessary registry information o
r
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the
event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
________________________________________
___
Thank you in advance,
KenHi Ken
"KenL" wrote:

> I have a procedure that reads data from linked server, a SQL2005 box, and
> writes a row in a SQL2000 database. This procedure and configuration have
> been working successfully for several years. This Sunday at 4am, this
> procedure failed to complete, leaving an unresolved transaction. The sympt
om
> is an insert on this table will timeout and fail because the unresolved
> transaction has a lock on the table, and it shows as a blocking transactio
n.
> Otherwise the database is functional and responsive. I have tried to KILL
the
> unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BAC
K"
> under the Activity Monitor command column.
> I had this same problem last weekend, and re-starting the SQL Server Servi
ce
> resolved the transaction. However, this is not a viable option during
> production hours.
> I tried using "KILL 51 WITH STATUSONLY", and it returned:
> SPID 51: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> It it cam back with:
> Server: Msg 6114, Level 16, State 1, Line 1
> Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D0
0} is
> being used by another user. KILL command failed.
> My two questions are:
> 1) Is there anyway to clear this blocking transaction short of re-starting
> the SQL Server?
> 2) Is there anyway to figure out the root cause of the problem? I believe
it
> is some sort of MSDTC issue, that seems to only happen early on Sunday
> mornings.
> The following DTC error happened at exactly the same timestamp as the SQL
> procedure was executed.
> ________________________________________
___
> Application Event Log Error
> ________________________________________
___
> Date 7/1/2007 4:09:31 AM
> Log Windows NT (Application)
> Source MSDTC
> Category (3)
> Event 3221229829
> Computer SERVER002
> Message
> The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display the message, or you may not have permission t
o
> access them. The following information is part of the
> event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe
'
> ________________________________________
___
> Thank you in advance,
> Ken
I am not a MSDTC expert!!! Which process did you kill? I would expect a
process on the remote and local (originator) machines, and if there was an
order to be killed then local would be the first. If you stopped the DTC
services (NET STOP MSDTC) it should also rollback, but all distributed
transactions would be affected.
Is this the only time distributed transaction are used? If not then it would
narrow the issue down to either something with the process or something that
happens at that time. If the process is scheduled and works at other times
then it would rule the process out. If it is something that happens at a
specific time, check things like firewalls or antivirus updates/scans etc.
http://support.microsoft.com/default.aspx/kb/306843
Also look for blocking occuring during the process and how you handle errors
such as deadlocks in the code.
You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
check that DTC works ok.
John|||Thank you for the response John.
<Is this the only time distributed transaction are used?
No, there are many procedures on this server that link to databases on
another server. The stored procedure that is failing runs hundreds of times
in a day. It had been reliable for years, up until last Sunday and this
Sunday when I have seen the two failures
<Which process did you kill?
I killed the spid on the SQL server initiating the link
I will review the kb's you referenced
Thanks,
Ken
"John Bell" wrote:

> Hi Ken
> "KenL" wrote:
>
> I am not a MSDTC expert!!! Which process did you kill? I would expect a
> process on the remote and local (originator) machines, and if there was an
> order to be killed then local would be the first. If you stopped the DTC
> services (NET STOP MSDTC) it should also rollback, but all distributed
> transactions would be affected.
> Is this the only time distributed transaction are used? If not then it wou
ld
> narrow the issue down to either something with the process or something th
at
> happens at that time. If the process is scheduled and works at other times
> then it would rule the process out. If it is something that happens at a
> specific time, check things like firewalls or antivirus updates/scans etc.
> http://support.microsoft.com/default.aspx/kb/306843
> Also look for blocking occuring during the process and how you handle erro
rs
> such as deadlocks in the code.
> You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing
to
> check that DTC works ok.
> John

DTC Error and unresolved SQL transaction

I have a procedure that reads data from linked server, a SQL2005 box, and
writes a row in a SQL2000 database. This procedure and configuration have
been working successfully for several years. This Sunday at 4am, this
procedure failed to complete, leaving an unresolved transaction. The symptom
is an insert on this table will timeout and fail because the unresolved
transaction has a lock on the table, and it shows as a blocking transaction.
Otherwise the database is functional and responsive. I have tried to KILL the
unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
under the Activity Monitor command column.
I had this same problem last weekend, and re-starting the SQL Server Service
resolved the transaction. However, this is not a viable option during
production hours.
I tried using "KILL 51 WITH STATUSONLY", and it returned:
SPID 51: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
It it cam back with:
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
being used by another user. KILL command failed.
My two questions are:
1) Is there anyway to clear this blocking transaction short of re-starting
the SQL Server?
2) Is there anyway to figure out the root cause of the problem? I believe it
is some sort of MSDTC issue, that seems to only happen early on Sunday
mornings.
The following DTC error happened at exactly the same timestamp as the SQL
procedure was executed.
___________________________________________
Application Event Log Error
___________________________________________
Date 7/1/2007 4:09:31 AM
Log Windows NT (Application)
Source MSDTC
Category (3)
Event 3221229829
Computer SERVER002
Message
The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the
event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
___________________________________________
Thank you in advance,
KenHi Ken
"KenL" wrote:
> I have a procedure that reads data from linked server, a SQL2005 box, and
> writes a row in a SQL2000 database. This procedure and configuration have
> been working successfully for several years. This Sunday at 4am, this
> procedure failed to complete, leaving an unresolved transaction. The symptom
> is an insert on this table will timeout and fail because the unresolved
> transaction has a lock on the table, and it shows as a blocking transaction.
> Otherwise the database is functional and responsive. I have tried to KILL the
> unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> under the Activity Monitor command column.
> I had this same problem last weekend, and re-starting the SQL Server Service
> resolved the transaction. However, this is not a viable option during
> production hours.
> I tried using "KILL 51 WITH STATUSONLY", and it returned:
> SPID 51: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> It it cam back with:
> Server: Msg 6114, Level 16, State 1, Line 1
> Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> being used by another user. KILL command failed.
> My two questions are:
> 1) Is there anyway to clear this blocking transaction short of re-starting
> the SQL Server?
> 2) Is there anyway to figure out the root cause of the problem? I believe it
> is some sort of MSDTC issue, that seems to only happen early on Sunday
> mornings.
> The following DTC error happened at exactly the same timestamp as the SQL
> procedure was executed.
> ___________________________________________
> Application Event Log Error
> ___________________________________________
> Date 7/1/2007 4:09:31 AM
> Log Windows NT (Application)
> Source MSDTC
> Category (3)
> Event 3221229829
> Computer SERVER002
> Message
> The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display the message, or you may not have permission to
> access them. The following information is part of the
> event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> ___________________________________________
> Thank you in advance,
> Ken
I am not a MSDTC expert!!! Which process did you kill? I would expect a
process on the remote and local (originator) machines, and if there was an
order to be killed then local would be the first. If you stopped the DTC
services (NET STOP MSDTC) it should also rollback, but all distributed
transactions would be affected.
Is this the only time distributed transaction are used? If not then it would
narrow the issue down to either something with the process or something that
happens at that time. If the process is scheduled and works at other times
then it would rule the process out. If it is something that happens at a
specific time, check things like firewalls or antivirus updates/scans etc.
http://support.microsoft.com/default.aspx/kb/306843
Also look for blocking occuring during the process and how you handle errors
such as deadlocks in the code.
You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
check that DTC works ok.
John|||Thank you for the response John.
<Is this the only time distributed transaction are used?
No, there are many procedures on this server that link to databases on
another server. The stored procedure that is failing runs hundreds of times
in a day. It had been reliable for years, up until last Sunday and this
Sunday when I have seen the two failures
<Which process did you kill?
I killed the spid on the SQL server initiating the link
I will review the kb's you referenced
Thanks,
Ken
"John Bell" wrote:
> Hi Ken
> "KenL" wrote:
> > I have a procedure that reads data from linked server, a SQL2005 box, and
> > writes a row in a SQL2000 database. This procedure and configuration have
> > been working successfully for several years. This Sunday at 4am, this
> > procedure failed to complete, leaving an unresolved transaction. The symptom
> > is an insert on this table will timeout and fail because the unresolved
> > transaction has a lock on the table, and it shows as a blocking transaction.
> > Otherwise the database is functional and responsive. I have tried to KILL the
> > unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> > under the Activity Monitor command column.
> > I had this same problem last weekend, and re-starting the SQL Server Service
> > resolved the transaction. However, this is not a viable option during
> > production hours.
> >
> > I tried using "KILL 51 WITH STATUSONLY", and it returned:
> > SPID 51: transaction rollback in progress. Estimated rollback completion:
> > 100%. Estimated time remaining: 0 seconds.
> >
> > I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> > It it cam back with:
> > Server: Msg 6114, Level 16, State 1, Line 1
> > Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> > being used by another user. KILL command failed.
> >
> > My two questions are:
> > 1) Is there anyway to clear this blocking transaction short of re-starting
> > the SQL Server?
> > 2) Is there anyway to figure out the root cause of the problem? I believe it
> > is some sort of MSDTC issue, that seems to only happen early on Sunday
> > mornings.
> >
> > The following DTC error happened at exactly the same timestamp as the SQL
> > procedure was executed.
> > ___________________________________________
> > Application Event Log Error
> > ___________________________________________
> > Date 7/1/2007 4:09:31 AM
> > Log Windows NT (Application)
> >
> > Source MSDTC
> > Category (3)
> > Event 3221229829
> > Computer SERVER002
> >
> > Message
> > The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> > found. The local computer may not have the necessary registry information or
> > message DLL files to display the message, or you may not have permission to
> > access them. The following information is part of the
> > event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> > ___________________________________________
> >
> > Thank you in advance,
> > Ken
> I am not a MSDTC expert!!! Which process did you kill? I would expect a
> process on the remote and local (originator) machines, and if there was an
> order to be killed then local would be the first. If you stopped the DTC
> services (NET STOP MSDTC) it should also rollback, but all distributed
> transactions would be affected.
> Is this the only time distributed transaction are used? If not then it would
> narrow the issue down to either something with the process or something that
> happens at that time. If the process is scheduled and works at other times
> then it would rule the process out. If it is something that happens at a
> specific time, check things like firewalls or antivirus updates/scans etc.
> http://support.microsoft.com/default.aspx/kb/306843
> Also look for blocking occuring during the process and how you handle errors
> such as deadlocks in the code.
> You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
> check that DTC works ok.
> John