2

iam using sql server 2014 on windows 8.1 pro

and calling a procedure that calls a dynamic linked server query inside trigger

(i will not put the complete code but consider it as an algorithm 

CREATE PROCEDURE DynamicLinkedQuery(@InstanceName NVARCHAR(32),@DatabaseName NVARCHAR(64),@SchemaName NVARCHAR(64), @TableName NVARCHAR(64))
as
begin 
declare 
        @SqlQuery NVARCHAR(MAX)


Select @SQlQuery='INSERT INTO ['+ @InstanceName+ '].['+@DatabaseName+'].'  + @SchemaName + '.' + @TableName + ' Values(3)'
--assuming we have one int columns in destination table 

EXECUTE sp_Executesql @SQLQuery 

End

because i'm using dynamic linked server query it is considered as distributed query

so we have to run Distributed Transaction Coordinator service on both two linked servers

and of course configuring it first with following steps mentioned in this link

http://www.virtualizationhowto.com/2015/04/ole-db-provider-sqlncli11-linked-server-unable-distributed-transaction/

but iam still getting the error

OLE DB provider "SQLNCLI11" for linked server "ServerName" returned message "No transaction is active.".

but when i execute a non dynamic query like

INSERT INTO RemoteServerName.RemoteDataBaseName.RemoteSchemaName.RemoteTableName values(1,1)

every thing is ok and the operation is done

what can be the reason please and what are my missed configurations in LocalDtc

i applied this solution

Linked servers SQLNCLI problem. "No transaction is active"

but it never worked for me

Community
  • 1
  • 1
oula alshiekh
  • 843
  • 5
  • 14
  • 40

1 Answers1

0

i discovered that because of my two servers reside in two different domains

i had to resolve DNS problem

so i put first server name in hosts file in the second server name and my problem was solved

oula alshiekh
  • 843
  • 5
  • 14
  • 40