If the linked server is a SQL Server, you can also use fully qualified
object name, qualifying it with the linked server name like this:
Select * from <Linked Server name>.<database name>.<Owner>.<Object name>
-----Original Message-----
From: Music Everett
[mailto:mssqldba-ezmlmshield-x91839789.[Email address protected]
Sent: Thursday, June 28, 2007 3:12 PM
To: LazyDBA Discussion
Subject: RE: OPENQUERY()
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
---------------------------------------------------------------------
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