trigger calls stored procedure help 2

[oakpark5]

Hopefully someone can help me with this....
I created a trigger to insert 4 records into a SQL server databse from another SQL server database. The two databases are linked by linked servers...
CREATE trigger trig_insertcalls
on dbo.testcalls
for insert
as
print 'hi'
insert into fserver6.pro30.dbo.calls (cdatetime,cnumber,cdur,cext)
(select cdatetime,cnumber,cdur,cext from inserted)

This gave me an error: so I decided to go with a stored procedure....but how do I pass the records I want from the "inserted" table from the trigger into the stored procedure......any help would be great, thanks in advance.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[mrdenny]

The way you are doing it is the correct method. What error are you getting? Also the select statement shouldn't be in ().

CREATE trigger trig_insertcalls
on dbo.testcalls
for insert
as
print 'hi'
insert into fserver6.pro30.dbo.calls
(cdatetime,cnumber,cdur,cext)
select cdatetime,cnumber,cdur,cext 
from inserted

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[oakpark5]

Server: Msg 7391, Level 16, State 1, Procedure trig_insertcalls, Line 7
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I've gone through all the microsoft steps to correct the problems but I still cant seem to get it to function.... My thought was to call a stored procedure and pass the information from inserted into the strored procedure, but I dont know how to do that, and cant find anything about that...

Software Engineer
Team Lead
damn bill gates, he has everything

 

[mrdenny]

Can you do the insert into the other servers table directly (not from within the trigger)?

I take it you can't modify the app to handle this on the front end?

Make sure the DTC is setup and running. If you are using Windows 2003 server you'll need to losen the security settings to get it to work correctly.

Have you thought about using DTS to move the data accross to the other server? That way if the server fserver6 is down the app will still work.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[oakpark5]

Well the linked server works if i dont use it in a trigger, bit I need a trigger to do this. Here is what is happening. We have cisco VOIP phones that insert information into a SQL database, i've put the trigger on that databse to fill a table on another database that runs more of our internal apps....to be honest I dont know what DTS is? All i need is 4 columns...I dont even need that much data, I just like triggers, I've worked with them in the past and think they are great, I've just never worked with them with linked servers which seems to be a real pain....

I think the problem either lies within the DTC, which is running on both sides, or the fact that I think my statement needs SET XACT_ABORT ON, but when i put that into my query it throws an error...I actually emailed a guy who wrote an article about this at microsoft, but who knows, this is driving me nuts....and I'm a C# programmer!!!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

Got it working I needed to install the DTC windows component, and add the SET XACT_ABORT ON in the code of the trigger,but now I have to set up another linked server that is on a VLAN and I know names dont resolve through, but IP'S do. Just have to figure out the naming convention....

Software Engineer
Team Lead
damn bill gates, he has everything

 

[ESquared]

Use the client network utility to add names that you want to resolve to IPs

 

[oakpark5]

OH!, I remember doing something with that...so if i give it an alias and designate its IP, can I then set it as a linked server? Thanks you're a big help...

Software Engineer
Team Lead
damn bill gates, he has everything

 

[ESquared]

yes