I'm attempting to run a stored proc into a table. The stored proc is on a sql server (2k) on a different domain. The table is on the sql server (also 2k) that I have a connection to. I have setup a linked server and the stored proc runs fine. When I attempt to insert the proc results into the table I get this error:Server: Msg 7391, Level 16, State 1, Line 6The 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].
This runs with no problem:Declare @SDate datetime, @Edate datetimeSelect @SDate = dateadd(d, -1, getdate()), @EDate = getdate()exec [sql2].somedb2.dbo.usp_transactions @start_datetime = @SDate, @end_datetime = @EDate
This fails with the above error:Declare @SDate datetime, @Edate datetimeSelect @SDate = dateadd(d, -1, getdate()), @EDate = getdate()insert into sql1.somedb.dbo.transexec [sql2].somedb2.dbo.usp_transactions @start_datetime = @SDate, @end_datetime = @EDate
DanielSQL Server DBA
problem with MSDTC
Posted - 2004-06-07 : 12:19:20
*Shoots self in head*Sorry. SQL1 is where the link server is setup and it is calling the proc on SQL2. SQL1 is SQL2k with WinNT4.SQL2 is SQL2k with Win2k Advanced Server. No SQL2k3 boxes here.DanielSQL Server DBA