Hi,
I can successfully run a query from a remote server using OPENROWSET as follows:
INSERT INTO MYTABLE
SELECT
A.MyData
,getdate()
,user
FROM
OPENROWSET('SQLOLEDB', 'RemoteServer'; 'username'; 'password',
'SELECT MyData FROM RemoteDatabase.dbo.RemoteTable') as A
However, when I change the above to use a join it doesnt work.
INSERT INTO MYTABLE
SELECT
A.MyData
,getdate()
,user
FROM
OPENROWSET('SQLOLEDB', 'RemoteServer'; 'username'; 'password',
'SELECT MyData FROM RemoteDatabase.dbo.RemoteTable R INNER JOIN RemoteDatabase.dbo.RemoteTable2 R2 ON R.[ID] = R2.[ID]') as A
Any ideas as to why this would be the case?
Thanks