OLE-DB problems with triggers - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OLE-DB problems with triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-29 : 10:07:41

Henrik Stahle writes "Hello
I hope you can help me with this because I am stuck!
I have two databases for a publishing-system, one on the inside and one on the outside. When I write a publishing on the inside I use a trigger to also write it in the outside-database. But now I have entered a hard problem with this trigger. When I try it from the QueryAnalyser it works just fine but when I make a change from the webpage I get this errormessage:
=========================================================
Microsoft OLE DB Provider for SQL Server error '80040e14'

[OLE/DB provider returned message: Cannot start more transactions on this session.]
=========================================================

The trigger is written like this:
========================================================
CREATE TRIGGER newsWWWContactUpdate ON Contacts
FOR INSERT, UPDATE, DELETE
AS
Declare @ContactID int
If exists (Select ID from Inserted)
Begin
Select @ContactID = Cont.ID From Contacts as Cont Inner Join inserted AS Ins On Cont.ID = Ins.ID
Exec sp_newsWWWInsertContact @ContactID
End
Else if exists (Select ID From Deleted)
Begin
Select @ContactID = Cont.ID From Contacts as Cont Inner Join deleted AS del On Cont.ID = del.ID
Exec sp_NewsWWWDeleteContact @ContactID
End
=============================================================

And the SP that enters the data into the external DB is written like this:
==============================================================
CREATE PROCEDURE sp_newsWWWInsertContact
@ContactID int

AS

Declare @ID int

Select @ID = ID
From [nm-media].news.dbo.Contacts
Where ID = @ContactID

If not @ID is Null
Begin
Delete From [nm-media].news.dbo.Contacts
Where ID = @ID
End

Insert Into [nm-media].news.dbo.Contacts
(ID,UnitID,Editorial_staff,Official_address,City,Visitor_address,Streetaddress,Boxnr,Postalcode,Tip_Phone,Phone,Fax,Email,Contact_orderby)
Select ID,UnitID,Editorial_staff,Official_address,City,Visitor_address,Streetaddress,Boxnr,Postalcode,Tip_Phone,Phone,Fax,Email,Contact_orderby
From Contacts
Where ID = @ContactID
GO
===========================================================

I have no Ide'a what to do, I have tried with SET IMPLICIT_TRANSACTIONS But It does not work.

Please help me!

Sincerely

Henrik Stahle
Sveriges Radio
Sweden"

ray@x-webz.net
Starting Member

1 Post

Posted - 2005-05-24 : 10:16:53

Did you ever find a fix for this problem? I'm having the same problem as well using the trigger.

Thanks,

Ray...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources