Distributed transaction

• haichells

  SSCrazy

  Points: 2713

  More actions

  August 4, 2015 at 11:03 am

  #319550

  Hello everyone,

  I am trying to run a query from a Linked server "ABCD"

  Set @SQLCMD = 'Select * from TableName"

  Insert into @TempTableName

  Execute (@SQLCMD) AT ABCD

  I am getting below error while running the above statements. When I Remove the Insert into @TempTableName it is working fine.

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

  Please help.

  Thanks in Advance!

• JohnG69

  Hall of Fame

  Points: 3114

  More actions

  August 4, 2015 at 11:41 am

  #1817257

  Have you tried

  Set @SQLCMD = 'Select * from openquery(ABCD,"Select * from database.TableName")'

  insert into @TempTableName

  EXEC (@SQLCMD)

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  August 4, 2015 at 3:19 pm

  #1817337

  haichells (8/4/2015)

  ---

  Hello everyone,

  I am trying to run a query from a Linked server "ABCD"

  Set @SQLCMD = 'Select * from TableName"

  Insert into @TempTableName

  Execute (@SQLCMD) AT ABCD

  I am getting below error while running the above statements. When I Remove the Insert into @TempTableName it is working fine.

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

  Please help.

  Thanks in Advance!

  Quick question, what is the output of this code?

  EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries'

  😎