Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lavos
Posting Yak Master
200 Posts |
 Posted - 2005-09-15 : 14:08:14
|
| Hello, I'm trying to process some XML stored on a linked server using a proc from Ken Henderson. I only need read access to the other server. Unfortunately, I'm doing this within a local transaction which tries to enlist my remote server in a distributed transaction automatically. While I highly doubt it, is there any way to get SQL Server to stop automatically attempting to promote the transaction to a distributed transaction? SET REMOTE_PROC_TRANSACTIONS does not work because it's a linked server, not a remote server. I'm mainly getting failures because of it (Silly DTC is not configured to work between these particular servers), not to mention that starting a distributed transaction is completely unnecessary from my viewpoint.----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-15 : 19:07:27
|
| Edit the code/proc so that the remote data access happens before the transaction is started. If the remote data set is not too large, you can also copy it down to a local (even temp) table and use it there. This may be better even if the data set is large, because cross-server joins and such have significant processing overhead and will be much slower. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-16 : 00:14:56
|
Here's a stupid question...why don't you just configure DTC properly on the two servers?  It doesn't take that long.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2005-09-22 : 17:13:17
|
| 'lo again.The command is ran at the end of a long batch of commands, with the transaction managed by the calling application. So, doing the query outside of the transaction isn't as straight forward as I would like.As for getting DTC enabled, we've been discussing it. The issue is that requires a change to our production server by a different group, and they would have to come in on the weekend to do it for us. (one of those policies designed to keep someone from fubaring the cluster again. Plus, we're doing a large volume of business right now that I think has been spilling over into the weekend so who knows when we can schedule a window of opportunity.)BTW, went searching for the method of casting GETDATE to a numeric to clear the non-date part, and found this old thread:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=5126Comedy Gold----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
|
|
|
|
|
|
Any way to Not use a distributed transaction?