OPENQUERY results

• tom.eanes

  Old Hand

  Points: 355

  More actions

  October 1, 2008 at 8:40 am

  #191568

  Hello,

  I'm trying to use results from Sybase, but having trouble

  INSERT INTO

  EXEC

  I can get results, but get the following message, when trying to insert into..any help greatly appreciated.

  The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.

  OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  IF EXISTS(SELECT name

  FROM sysobjects

  WHERE name = N'amistile'

  AND type = 'U')

  DROP TABLE amistile

  GO

  CREATE TABLE amistile (

  room_code nvarchar(100),

  asset_tag nvarchar(100),

  description nvarchar(1000)

  )

  DECLARE @TileName nvarchar (10),@TSQL varchar(8000)

  set @tilename = 'CW10'

  select @TSQL = 'select * FROM OPENQUERY(amisole, ''select room_code,asset_tag,description FROM asset where location_code = ''''' + @TileName + ''''''')'

  insert into amistile

  EXEC (@TSQL)

  select * from amistile

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 1, 2008 at 9:06 am

  #879541

  Same method from the previous question should fix this. Select the results into a temp table and insert from there.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]