Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?
I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.
The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.
Any Help?You can do testing in query analyzer by placing your select * from openquery statement. If it works in query analyzer then u could execute this sql statement in dts package.
By the way, since you are using DTS package, why not use MS Access connection which is more dynamic and stable than OpenQuery.
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Tuesday, March 27, 2012
Sunday, February 19, 2012
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
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
Friday, February 17, 2012
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
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
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
DTC error
Hi,
I have two MS SQL2000 servers in local network . Let's
say A and B. Server B is linked server for A.
When I'm starting following select on server A :
========================================= BEGIN TRAN
SELECT *
FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
p.AP_NAME, p.PT_NAME,
p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
(p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
p.AP_VOL_FACE, p.AP_TIME_FACE,
p.AP_EXPIRED_PERIOD
FROM dbo.AAA_DW_PROFILE_DIM p ')
ROLLBACK TRAN
============================================
I'm getting following error:
========================================= Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[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].
==========================================
DTC is started on both servers.
During investigation of this issue I've started
DTCPing utilite. After this, in DTCPing's log file
I have found only following problem string:
================================================= 07-14, 19:55:100-->WARNING:the CID values for both test
machines are the same
while this problem won't stop DTCping test, MSDTC
will fail for this
====================================================
Am I right? The error was connected with this issue?
What should I do next?What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no comma after
"andy" <akorotaev@.estylesoft.com> wrote in message
news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
> Hi,
> I have two MS SQL2000 servers in local network . Let's
> say A and B. Server B is linked server for A.
> When I'm starting following select on server A :
> =========================================> BEGIN TRAN
> SELECT *
> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
> p.AP_NAME, p.PT_NAME,
> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
> p.AP_VOL_FACE, p.AP_TIME_FACE,
> p.AP_EXPIRED_PERIOD
> FROM dbo.AAA_DW_PROFILE_DIM p ')
> ROLLBACK TRAN
> ============================================> I'm getting following error:
> =========================================> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [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].
> ==========================================> DTC is started on both servers.
> During investigation of this issue I've started
> DTCPing utilite. After this, in DTCPing's log file
> I have found only following problem string:
> =================================================> 07-14, 19:55:100-->WARNING:the CID values for both test
> machines are the same
> while this problem won't stop DTCping test, MSDTC
> will fail for this
> ====================================================> Am I right? The error was connected with this issue?
> What should I do next?
>
>
>
>
>|||dbo.UF_GET_FULL_SOURCE is function.
No any comma needed here.
>--Original Message--
>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
comma after
>"andy" <akorotaev@.estylesoft.com> wrote in message
>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [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].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>
>.
>|||Andy,
take a look at technet article Q306843.
Section 11 refers to duplicate CIDs and using GUIDGEN to
fix this...
You might also want to look at the following article :
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;162001
I am guessing this is on a ghosted or cloned server ?
Hope this helps.
Steve.
>--Original Message--
>
>dbo.UF_GET_FULL_SOURCE is function.
>No any comma needed here.
>
>>--Original Message--
>>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
>comma after
>>"andy" <akorotaev@.estylesoft.com> wrote in message
>>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [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].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>>
>>.
>.
>
I have two MS SQL2000 servers in local network . Let's
say A and B. Server B is linked server for A.
When I'm starting following select on server A :
========================================= BEGIN TRAN
SELECT *
FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
p.AP_NAME, p.PT_NAME,
p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
(p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
p.AP_VOL_FACE, p.AP_TIME_FACE,
p.AP_EXPIRED_PERIOD
FROM dbo.AAA_DW_PROFILE_DIM p ')
ROLLBACK TRAN
============================================
I'm getting following error:
========================================= Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[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].
==========================================
DTC is started on both servers.
During investigation of this issue I've started
DTCPing utilite. After this, in DTCPing's log file
I have found only following problem string:
================================================= 07-14, 19:55:100-->WARNING:the CID values for both test
machines are the same
while this problem won't stop DTCping test, MSDTC
will fail for this
====================================================
Am I right? The error was connected with this issue?
What should I do next?What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no comma after
"andy" <akorotaev@.estylesoft.com> wrote in message
news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
> Hi,
> I have two MS SQL2000 servers in local network . Let's
> say A and B. Server B is linked server for A.
> When I'm starting following select on server A :
> =========================================> BEGIN TRAN
> SELECT *
> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
> p.AP_NAME, p.PT_NAME,
> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
> p.AP_VOL_FACE, p.AP_TIME_FACE,
> p.AP_EXPIRED_PERIOD
> FROM dbo.AAA_DW_PROFILE_DIM p ')
> ROLLBACK TRAN
> ============================================> I'm getting following error:
> =========================================> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [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].
> ==========================================> DTC is started on both servers.
> During investigation of this issue I've started
> DTCPing utilite. After this, in DTCPing's log file
> I have found only following problem string:
> =================================================> 07-14, 19:55:100-->WARNING:the CID values for both test
> machines are the same
> while this problem won't stop DTCping test, MSDTC
> will fail for this
> ====================================================> Am I right? The error was connected with this issue?
> What should I do next?
>
>
>
>
>|||dbo.UF_GET_FULL_SOURCE is function.
No any comma needed here.
>--Original Message--
>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
comma after
>"andy" <akorotaev@.estylesoft.com> wrote in message
>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [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].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>
>.
>|||Andy,
take a look at technet article Q306843.
Section 11 refers to duplicate CIDs and using GUIDGEN to
fix this...
You might also want to look at the following article :
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;162001
I am guessing this is on a ghosted or cloned server ?
Hope this helps.
Steve.
>--Original Message--
>
>dbo.UF_GET_FULL_SOURCE is function.
>No any comma needed here.
>
>>--Original Message--
>>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
>comma after
>>"andy" <akorotaev@.estylesoft.com> wrote in message
>>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [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].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>>
>>.
>.
>
Tuesday, February 14, 2012
DSN Using Trusted Connection When SQL Authentication is selected
I am trying to help an in-house developer with his database. He has develope
d an Access front-end that links to SQL tables. The tables are linked using
an ODBC DSN with SQL Server Authentication selected. WHen a user opens the d
atabse, they get one of two
error messages, based on the user. THey eith get ODBC Call failed and can no
t go any further, or they get Connection failed, and the SQL Server login di
alog, where they have to uncheck Trusted connection and enter the appropriat
e username and password. W
e want it to just authenticate based on the settings in the DSN without open
ing a logn box.You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
d an Access front-end that links to SQL tables. The tables are linked using
an ODBC DSN with SQL Server Authentication selected. WHen a user opens the d
atabse, they get one of two
error messages, based on the user. THey eith get ODBC Call failed and can no
t go any further, or they get Connection failed, and the SQL Server login di
alog, where they have to uncheck Trusted connection and enter the appropriat
e username and password. W
e want it to just authenticate based on the settings in the DSN without open
ing a logn box.You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
DSN Using Trusted Connection When SQL Authentication is selected
I am trying to help an in-house developer with his database. He has developed an Access front-end that links to SQL tables. The tables are linked using an ODBC DSN with SQL Server Authentication selected. WHen a user opens the databse, they get one of two
error messages, based on the user. THey eith get ODBC Call failed and can not go any further, or they get Connection failed, and the SQL Server login dialog, where they have to uncheck Trusted connection and enter the appropriate username and password. W
e want it to just authenticate based on the settings in the DSN without opening a logn box.
You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
error messages, based on the user. THey eith get ODBC Call failed and can not go any further, or they get Connection failed, and the SQL Server login dialog, where they have to uncheck Trusted connection and enter the appropriate username and password. W
e want it to just authenticate based on the settings in the DSN without opening a logn box.
You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)