ODBC-Error 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)

ODBC-Error with triggers

Author

Topic

hesta96
Starting Member

9 Posts

Posted - 2001-12-19 : 11:14:00

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"

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-19 : 11:52:26

Try SET XACT_ABORT ON

HTH
-Chad

hesta96
Starting Member

9 Posts

Posted - 2001-12-20 : 03:46:12

Hello!
Thanks!
Sometimes it is so simple that you can't see it!
It works alright!

Marry Christmas!

quote:

Try SET XACT_ABORT ON

HTH
-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources