>I've google'd like a crazy, tried a lot of stuff, but still can't get
>it to work:
>
>When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
>doesn't:
>
>An UPDATE-trigger on a table calls a stored procedure that selects
>data and send them to a linked server.
>
>1.
>When running the stored procedure alone, everything is fine and data
>is send to linked server (so there is through-put to linked server).
>
>2.
>But when run normally and the SP is run via the trigger we get:
>
>OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
>"No transaction is active.".
>Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
>56
>The operation could not be performed because OLE DB provider "SQLNCLI"
>for linked server "WEBDEV1" was unable to begin a distributed
>transaction.
>
>Any ideas?
>
>
>Regards /Snedker
> 
>

>Have you got MSDTC running?  MSDTC would be responsible for managing the
>inter-server (ie. distributed) transaction.

> I've google'd like a crazy, tried a lot of stuff, but still can't get
> it to work:
>
> When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
> doesn't:
>
> An UPDATE-trigger on a table calls a stored procedure that selects
> data and send them to a linked server.
>
> 1.
> When running the stored procedure alone, everything is fine and data
> is send to linked server (so there is through-put to linked server).
>
> 2.
> But when run normally and the SP is run via the trigger we get:
>
> OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
> 56
> The operation could not be performed because OLE DB provider "SQLNCLI"
> for linked server "WEBDEV1" was unable to begin a distributed
> transaction.
>
> Any ideas?
>
>
> Regards /Snedker

>Are these two servers running Windows 2003 Server?  By default, MSDTC
>is locked down, preventing network connections and anonymous sessions.
>You need to modify those settings for SQL to be able to use MSDTC.
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-60b3-4d26-bc04-e0676ec97786.asp
>
>
>Morten Snedker wrote:
>> I've google'd like a crazy, tried a lot of stuff, but still can't get
>> it to work:
>>
>> When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
>> doesn't:
>>
>> An UPDATE-trigger on a table calls a stored procedure that selects
>> data and send them to a linked server.
>>
>> 1.
>> When running the stored procedure alone, everything is fine and data
>> is send to linked server (so there is through-put to linked server).
>>
>> 2.
>> But when run normally and the SP is run via the trigger we get:
>>
>> OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
>> "No transaction is active.".
>> Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
>> 56
>> The operation could not be performed because OLE DB provider "SQLNCLI"
>> for linked server "WEBDEV1" was unable to begin a distributed
>> transaction.
>>
>> Any ideas?
>>
>>
>> Regards /Snedker

> On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
> <tracy.mckib***@gmail.com> wrote:
>
> As stated previously in this thread, the MSDTC is running.
>
> If it wasn't running I wouldn't be able to run the stored procedure.
>
> That is the essence of the weirdness:
>
> I can insert data to a table on linked server 'WEBEDV1" from the
> stored procedure. But when I let the trigger launch the stored
> procedure, it doesn't work !?
>
> This is my trigger:
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER TRIGGER [trgUpdateVentDeltagere]
>     ON [dbo].[tblVENTTilsagn]
>     FOR  UPDATE
> AS
>
> --SET REMOTE_PROC_TRANSACTIONS OFF;
>    declare @count int
>    declare @journal varchar(50)
>    declare @params varchar(512)
> -- Find post som er blevet opdateret
> select @count = count(*)
> from Inserted i join Deleted d on i.journal =d.journal
>
> if @count > 0  and (Substring(columns_updated(),1,1) > 0  or
> Substring(columns_updated(),2,1) >0)
>
>   begin
>
>          select @journal = i.journal from inserted i
>         exec spVENT_AppUpdVentDeltager @jnr = @journal
>   end
>
> Running spVENT_AppUpdVentDeltager from this trigger fails with error
> given in the subject-line.
>
> Running spVENT_AppUpdVentDeltager alone works fine.
>
>
> >Are these two servers running Windows 2003 Server?  By default, MSDTC
> >is locked down, preventing network connections and anonymous sessions.
> >You need to modify those settings for SQL to be able to use MSDTC.
> >
> >http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-60b3-4d26-bc04-e0676ec97786.asp
> >
> >
> >Morten Snedker wrote:
> >> I've google'd like a crazy, tried a lot of stuff, but still can't get
> >> it to work:
> >>
> >> When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
> >> doesn't:
> >>
> >> An UPDATE-trigger on a table calls a stored procedure that selects
> >> data and send them to a linked server.
> >>
> >> 1.
> >> When running the stored procedure alone, everything is fine and data
> >> is send to linked server (so there is through-put to linked server).
> >>
> >> 2.
> >> But when run normally and the SP is run via the trigger we get:
> >>
> >> OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
> >> "No transaction is active.".
> >> Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
> >> 56
> >> The operation could not be performed because OLE DB provider "SQLNCLI"
> >> for linked server "WEBDEV1" was unable to begin a distributed
> >> transaction.
> >>
> >> Any ideas?
> >>
> >>
> >> Regards /Snedker

>Without knowing what else that sproc does, and based on what you've
>given us, I'm still going to suggest that your problem is due to MSDTC
>permissions.  If your servers are Windows 2003, there are some MSDTC
>properties that you must change.  If the information provided in the
>first link I posted doesn't work, then try this one:
>
>http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
>
>Good luck!