SQL SERVER 2008 Queries other databases

1. Access other local databases

--Enable ad Hoc distributed queries--execsp_configure'Show advanced Options',1Reconfigureexecsp_configure'Ad Hoc Distributed Queries',1Reconfigure--Close AD Hoc distributed queries--When you are finished usingexecsp_configure'Ad Hoc Distributed Queries',0Reconfigureexecsp_configure'Show advanced Options',0Reconfigure--SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=ip (or servername); User id= login name; password= password '). database. dbo. Table name (or view)SELECT *  from          OpenDataSource(          'SQLOLEDB',            'Data Source=itvs; User Id=sa; Password=sa')               . ANSVSP.dbo.serv

2. Access databases on other machines

(1) Remotely open SQL Server on the remote machine

(2) Turn off the firewall on the remote machine

SELECT *  from          OpenDataSource (          'SQLOLEDB',            'Data source=192.168.1.26 ;               ' )               . ANSVSP.dbo.serv

Inline

SELECTA.bah,a.mz,b.*  from          OpenDataSource(          'SQLOLEDB',            'Data source=192.168.1.26; User Id=sa; Password=sadan')               . ANSVSP.dbo.serv asAInner JoinDb_local_table b onA.bah=b.id collate Chinese_prc_90_ci_ai;--Collate Chinese_prc_90_ci_ai keep the collation uniform on both sides of the equals sign

SQL SERVER 2008 Queries other databases