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.
| Author |
Topic |
|
jparker
Posting Yak Master
118 Posts |
 Posted - 2007-03-07 : 11:06:10
|
| I have the following codedeclare @mysql varchar(150) SET ANSI_WARNINGS ON SET ANSI_NULLS ON set @mysql = 'select * from OPENQUERY(MYLINKEDSERVER, ''select jobid from job where jobid = '''''+@est_id+''''' and purchaseorderno is null '')' print @mysql exec(@mysql) create table #table (est_id varchar(10)) insert into #table exec(@mysql) set @myest_id = (select top 1 est_id from #table) select @myest_id as 'est_id' drop table #tableIt works fine in retrieving the value from the linked server but then I want to apply the scalar result to a variableI get the following error message : -The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].And this only occurs when I have the assignment of data to a table. I have scoured the interweb and this seems to be the approach to follow but not necessarily with a linked server.Could you help please |
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2007-03-07 : 12:01:00
|
| I've got round it by using the four part naming convention rather than the openquery statementA slight hit on pulling back the information but probably regained overall by not having to create a table |
 |
|
|
|
|
|
Returning a value from a linked server and exec