title: SQLServer跨库查询  
CreateTime: 2011-12-13 16:59:00  
UpdateTime: 2011-12-13 16:59:00  
CategoryName: web  
---

---
title: "SQLServer跨库查询"
date: 2011-12-13T16:59:00+08:00
draft: false
tags: ["web"]
categories: ["web"]
author: "springrain"
---
```sql
-- 跨库查询
select * FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=192.168.0.5,1433;User ID=sa;Password=sa'
).dbName.dbo.tableName

-- 打开
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

-- 关闭
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
```