同个实例的直接这样:
SELECT * FROM [数据库].[dbo].[scott]
不同实例的这样:
--
-- OPENROWSET 支持在连接时制定查询语句,比较灵活
-- OPENDATASOURCE 连接字符串可用ADO的连接字符串,但不支持在连接时制定查询语句
--
-- OPENROWSET:连接字符串
-- Windows身份:Server=数据库服务器;Trusted_Connection=Yes;
-- 账号密码登录:Server=数据库服务器;PWD=密码;UID=账号;
-- 账号密码登录:'数据库服务器';'账号';'密码'
--
-- OPENDATASOURCE:连接字符串
-- Windows身份:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=数据库服务器;Data Source=数据库名
-- 账号密码登录:Provider=SQLOLEDB.1;Password=密码;Persist Security Info=True;User ID=账号;Initial Catalog=数据库服务器;Data Source=数据库名
--
--开启高级设置
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
--开启外围设置
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
--
-- OPENROWSET
-- Windows身份
--
SELECT *
FROM OPENROWSET('SQLOLEDB', 'Server=127.0.0.1,2434;Trusted_Connection=Yes', 'SELECT * FROM jfdf1.dbo.xtsz UNION ALL SELECT * FROM jfdf2.dbo.xtsz')
--
-- 账号密码登录
--
SELECT *
FROM OPENROWSET('SQLOLEDB', 'Server=127.0.0.1,2434;PWD=932kj;UID=sa', 'SELECT * FROM jfdf1.dbo.xtsz')
--
-- 账号密码登录
--
SELECT *
FROM OPENROWSET('SQLOLEDB', '127.0.0.1,2434';'sa';'932kj', 'SELECT * FROM jfdf1.dbo.xtsz')
GO
--
-- OPENROWSET
-- Windows身份
--
SELECT *
FROM OPENDATASOURCE('SQLOLEDB', 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=jfdf1;Data Source=127.0.0.1,2434').jfdf2.dbo.xtsz
--
-- 账号密码登录
--
SELECT *
FROM OPENDATASOURCE('SQLOLEDB', 'Provider=SQLOLEDB.1;Password=932kj;Persist Security Info=True;User ID=sa;Initial Catalog=jfdf1;Data Source=127.0.0.1,2434').jfdf2.dbo.xtsz
GO
--关闭高级设置
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE
--关闭外围设置
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO