How to select data from 2 tables on 2 different SQL server - Need correct query

Question

How to select data from 2 tables on 2 different SQL server - Need correct query

Parthipan-244146

SSCrazy

Points: 2178
More actions
September 21, 2008 at 2:01 am

#193141

Hi All,
Please help to correct this query to select data from 2 tables on 2 different servers using linked server
select SYS.Netbois_Name0 as ComputerName, SYS.AD_Site_Name0, SYS.Operating_System_Name_and0 as OperatingSystem,
as select * from OPENQUERY(test, select ENA.Name as UserName, ENA.Title, ENA.Department, ENA.TelephoneNumber,
ENA.PhysicalDeliveryOfficeName as OfficeName from dbo.v_enabledusers ENA) join V_R_System SYS
on SYS.UserName0=ENA.sAMAccountName where SYS.Operating_System_Name_and0='Microsoft Windows NT Workstation 5.1'
order by SYS.Netbois_Name0
Am I using right way to join the tables. Please advice any other method to join the tables.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Well, the as shouldn't be there, you need to alias the open query, the statement within openquery needs to be in quotes, the column sAMAccountName needs to be added to the select of the openquery and the query could really use some formatting to make it easier to read.

Oh, and you shouldn't use sys as an alias, as it's a system schema in SQL 2005.

Try this:

SELECT System.Netbois_Name0 as ComputerName,

System.AD_Site_Name0,

System.Operating_System_Name_and0 as OperatingSystem,

ENA.UserName,

ENA.Title,

ENA.Department,

ENA.TelephoneNumber,

ENA.OfficeName

FROM OPENQUERY(test,

'SELECT ENA.Name as UserName,

ENA.Title,

ENA.Department,

ENA.TelephoneNumber,

ENA.PhysicalDeliveryOfficeName as OfficeName,

ENA.sAMAccountName

FROM dbo.v_enabledusers ENA') AS ENA

INNER JOIN V_R_System System on System.UserName0=ENA.sAMAccountName

WHERE System.Operating_System_Name_and0='Microsoft Windows NT Workstation 5.1'

ORDER BY System.Netbois_Name0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 2

Hi Gail Shaw,

Thanks alot for your technical advice. Now I can select the data.

You are the master..

BR,

Parthi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Pleasure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhesje SSC Enthusiast Points: 163 More actions · Answer 4

Will this syntax work when doing an update statement? I've found a couple posts from people having the same problem that I am, namely, can't apply an update using a pass-through query and a table in my Sql server 2000 database.

update

test

set

test.Email = a.Email

from

openquery

(

LinkedServer

, 'select

ConId

, Email

from

WebStage.con_Contacts

'

) as test

inner join con.contacts a on test.ConId = a.Id

this give me an "invalid object name 'test'" error. I can get this to work within a cursor where i'm running one update per record that I want to change but i would really rather do the whole thing in one step as there are 3000+ records to update and the cursor is rather slow.