Linked server problem - 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 2005 Forums

SQL Server Administration (2005)

Linked server problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-23 : 10:08:19

PS writes "I have two machines having XP OS,running sql server 2005.In one machine i have created a table.I need to send the data from this machine to another using linked sever,i.e. as soon as the data gets inserted into local table the same will be transferred to another table in other machine using the database trigger which i have created and mentioned below.
MS DTC is running in both the machines.
I have created the linked server as
>>>>>>Creation of linked server
sp_addlinkedserver @server = N'linkserver',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'SUMITESH',
@catalog = N'test'
GO
>>>>>>Login permission
sp_addlinkedsrvlogin
@rmtsrvname=linkserver
,@useself=false
,@locallogin=sa
,@rmtuser=sa
,@rmtpassword=123
>>>>>>>Linked server options set
sp_serveroption linkserver,[collation compatible],true
sp_serveroption linkserver,[Rpc],true
sp_serveroption linkserver,[Rpc out],true
>>>>>>>>>Following two querries work fine
SELECT * FROM linkserver.test.dbo.test

SELECT * FROM linkserver.AdventureWorks.HumanResources.Department
>>>>>>>Trigger on the local table
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [trg1] ON [dbo].[test]

AFTER INSERT AS
BEGIN
SET NOCOUNT ON;

INSERT INTO linkserver.test.dbo.test

([test1]
)
SELECT
inserted.[test1]

FROM inserted;

END
>>>>>>>>Error generated on trigger above is
Msg 8197, Level 16, State 4, Procedure trg1, Line 4
Object 'dbo.test' does not exist or is invalid for this operation.>>>>>>>>
>>>>>>When i write a simple query for testing the data insertion into another table in other machine like:
BEGIN DISTRIBUTED TRANSACTION;

INSERT INTO linkserver.test.dbo.test

([test1])
VALUES
(20)

COMMIT TRANSACTION;
>>>>>>>>The query runs for couple of mins and then throws an error message as :"OLE DB provider "SQLNCLI" for linked server "linkserver" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "linkserver" was unable to begin a distributed transaction.
>>>>>>>>>>>>>>>>
Please help
>>>>>>>>.Features enables by me:
SQLNCLI Provider options:Enabled "allow in process" only"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-23 : 10:18:14

you have to enable MS DTC.
look here:
http://support.microsoft.com/kb/839279

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources