July 30, 2008 at 11:25 am
I'm executing a stored procedure through a linked server like this:
EXEC LinkedServer.dbName.dbo.sprocName
This code is executed on a SQL 2005 instance (where linked server is located). Let's call this instance SQL2005.
The target is a SQL 2000 instance. Call it SQL2000. Stored Procedure sprocName is contained in instance SQL2000.
The code of the stored procedure contains a linked-server call back to SQL2005 to fetch some data.
So this is kind of a circular call from SQL2005 to SQL2000 back to SQL2005 through the stored procedure.
Now, I have followed the recommendations of MS for configuring the DTS on both machines and just restarted first the DTS and then the SQL services on both machines. But I'm still getting the error below. Any ideas how to fix it would be appreciated.
Msg 7391, Level 16, State 1, Procedure sprocName2, Line 223
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Msg 8520, Level 16, State 1, Procedure sprocName, Line 83
Internal Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed to commit: 0x8004d019(XACT_E_ABORTED).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 30, 2008 at 11:36 am
I've had problems getting SQL 2000 procs to distribute transactions with SQL 2005. My solution has been to move the proc to the 2005 server and make it do its work there. That's worked so far.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 11:39 am
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 30, 2008 at 11:43 am
GSquared (7/30/2008)
I've had problems getting SQL 2000 procs to distribute transactions with SQL 2005. My solution has been to move the proc to the 2005 server and make it do its work there. That's worked so far.
Thank you, admittedly, it's very ugly what our devs are doing here, and that's good advice for a workaround.
I have suggested that they use Integration Services for transferring data.
I think linked servers should be used primarily for user-initiated functions, while tasks run through scheduled SQL jobs can best be done through Integration Services. SSIS is primed for moving data between servers/instances.
What's your opinion?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 30, 2008 at 11:51 am
Jason Crider (7/30/2008)
Try this
thanks, I will go through these steps.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 30, 2008 at 12:56 pm
SSIS is great for that. For scheduled data transfers between servers, SSIS would be very high on my list of potential tools.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 1:22 pm
GSquared (7/30/2008)
SSIS is great for that. For scheduled data transfers between servers, SSIS would be very high on my list of potential tools.
So I'm not crazy after all. I mentioned SSIS to our developers, and they looked at me as if I'm from Mars!
One even dared me to convert their functionality to an SSIS package! The nerve... :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 30, 2008 at 1:39 pm
If you are able to then SSIS would be the route to try.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 5, 2009 at 1:51 pm
I was getting this on one single custom replication proc. It turns out that the linked server referenced in the proc on the subscriber was referencing the publisher, and this was causing the failure. I did a SQL compare with a similar enviornment, and the linked server reference should have been to another server (not the publisher, although the data is the same).
Once I fixed this, the publication worked like a charm. I thought I read somewhere that linking back to the publisher could cause this error, and that's what it was!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply