Wednesday, March 7, 2012

Error :Execute Trigger From Remote Server To another Server By Linked Server

i did "Linked server" between To Servers , and it's Working.

For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2

and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from inserted

END

**************************************************

result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

how i can execute this trigger

Just to clarify: does the insert statement work outside of the trigger from the same S1 server?|||

Yes.

it's Work from Stored Procduers without TRAN (Transaction) .

if i use Transaction From Stored Procduers is not work.

Give me same result above.

|||Is MSDTC enabled on both servers? I think insert from the trigger into a linked server is done inside the distributed transaction.
But I would expect some transaction specific error if it was not so.|||

Microsoft proived some solution, how to overcome this problem. Try it.

http://support.microsoft.com/kb/873160

I tried in the LAN its working fine. But, still in the WAN is not working.

No comments:

Post a Comment