No transaction is active

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 /SnedkerHave you got MSDTC running? MSDTC would be responsible for managing the
inter-server (ie. distributed) transaction.
*mike hodgson*
http://sqlnerd.blogspot.com
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
>|||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/d...0676ec97786.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 Fri, 09 Jun 2006 21:48:49 +1000, Mike Hodgson <e1minst3r@.gmail.com>
wrote:
Yes, it is running.
But I suppose the transaction is the same, whether it is launched from
a stored procedure - or a trigger firing the stored procedure?
/Snedker

>Have you got MSDTC running? MSDTC would be responsible for managing the
>inter-server (ie. distributed) transaction.|||On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
<tracy.mckibben@.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/d...0676ec97786.asp
>
>Morten Snedker wrote:|||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/defaul...332&Product=sql
Good luck!
Morten Snedker wrote:
> On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
> <tracy.mckibben@.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.
>|||On 9 Jun 2006 06:24:56 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
Thanks for your suggestion and your efforts. However, I've been there
already and MSDTC has been set properly.
The solution for now is moving the stored procedure from the trigger
to a client application. Less than optimal, but it works.
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/defaul...332&Product=sql
>Good luck!