Linked Server Between Sql Server 2008 to 2005

• b17k17-870831

  Valued Member

  Points: 63

  More actions

  October 18, 2010 at 10:18 pm

  #72944

  Hi...

  i have set up a linked server between an Sql Server 2008 SP1 version on Windows Server 2008 R2 to SQL Server 2005 post SP3 cumulative update v 9.0.4.230 on Windows Server 2003 SP2

  I can query directly across the linked server...for eg

  Select columns from linkedserver.database.schema.tablename

  however, i need to execute a stored proc on the linked server from within a stored proc on the local server...for eg

  Insert Into LocalTable

  Exec linkedserver.database.schema.storedproc

  which errors with the following message:

  OLE DB provider "SQLNCLI10" for linked server "linkedserver" returned message "No transaction is active.".

  Msg 7391, Level 16, State 2, Procedure ins_cognoscontexpmth_sp, Line 17

  The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "linkedserver" was unable to begin a distributed transaction.

  With the linked server object i'm using the option "be made with this security context"

  Within the Local DTC Properties, under security i have the following options ticked:

  Network DTC Access

  Allow Remote Clients

  Allow Inbound

  Allow Outbound

  Mutual Authentication Required

  Enable SNA LU 6.2 Transactions

  DTC Logon Account: NT Authority\Network Service

  I've tried everything with DTS short of reinstalling it

  Any ideas on what could be causing the error message would much appreciated

• bjhogan

  SSC Eights!

  Points: 820

  More actions

  December 14, 2010 at 6:20 am

  #1262149

  It looks like 2008 SP2 might fix the problem you are having.

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