Close. The remote query has to be wrapped in single quotes.

Select *
From OPENQUERY(SERVERB, 'select * from table') as B JOIN serverA on
B.Column = A.column

No trees were killed in the making of this email. However, a large
number of electrons were diverted from their original destination.

-----Original Message-----
From: Neal Sivley
[mailto:mssqldba-ezmlmshield-x40087551.[Email address protected]
Sent: Thursday, June 28, 2007 12:08 PM
To: LazyDBA Discussion
Subject: RE: OPENQUERY()

I think you may be calling your open query incorrectly... I think it
should be this

Select *
From OPENQUERY(SERVERB, 'select * from table ) as B JOIN serverA on
B.Column = A.column

Thanks,
Neal
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x5416390.[Email address protected]
Sent: Thursday, June 28, 2007 12:38 PM
To: LazyDBA Discussion
Subject: OPENQUERY()

Hello gentlmen and gentlewomen. I am playing around with OPENQUERY to
make joins across linked servers with the idea of distributing
processing to improve performance.



SERVERA



Select

Stuff

From OPENQUERY(SERVERB, 'select stuff

from serverB table

join serverB table

join linked serverA table)





The reason I want to do this, is the server B joins are large record
sets but the join to serverA reduces the records set to something small
enough to send over the network back to serverA. But I get this error.
Any clues?











Server: Msg 7391, Level 16, State 1, Line 1

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].



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


________________________________________________________________________
_____
Scanned by IBM Email Security Management Services powered by
MessageLabs. For more information please visit http://www.ers.ibm.com
________________________________________________________________________
_____

________________________________________________________________________
_____
Scanned by IBM Email Security Management Services powered by
MessageLabs. For more information please visit http://www.ers.ibm.com
________________________________________________________________________
_____


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page