MSSQL链接常用数据库(二)

原文地址  

1、建立链接服务器。

简单说明下sp_addlinkedserver 这个存储过程的几个重要参数
@server = N'链接服务器名称', @srvproduct=N'产品名称', @provider=N'访问接口', @datasrc=N'数据源' 于是：
ACCESS的建立
（1）没有密码的情况：
EXECUTE sp_addlinkedserver 'OLE_TEST', 'Access', 'Microsoft.Jet.OLEDB.4.0', 'E:\MyDatabase.mdb（详细路径）'
GO
（2）有密码的情况
EXECUTE sp_addlinkedserver 'OLE_TEST', 'Access', 'Microsoft.Jet.OLEDB.4.0', 'E:\MyDatabase.mdb（详细路径）',null,';pwd=密码'
GO
如果启用了账户权限，那就没这么简单了，注册表SystemDB项也需要修改，不过这种情况应该很少。
MSSQL的建立
EXECUTE sp_addlinkedserver 'SQL_TEST','SQL','SQLOLEDB','机器名称/IP地址/域名等'
EXECUTE sp_addlinkedsrvlogin 'SQL_TEST',FALSE,null,'用户名','密码'
GO
ORACLE的建立
EXECUTE sp_addlinkedserver 'ORA_TEST','Oracle','MSDAORA','Oracle数据源名称'
EXECUTE sp_addlinkedsrvlogin 'ORA_TEST',FALSE,null,'用户名','密码'
GO

2、数据库的访问以及操作

链接数据库建立后，就可以对链接数据库进行访问，普通方法有2种：
（1）T-SQL方式   select * from [链接表名称].[数据库名].[架构].[表名]
ACCESS一般使用： select * from [链接表名称]...[表名]       （数据库名和架构都没）
MSSQL 一般使用：   select * from [链接表名称].[数据库名].[dbo].[表名]
Oracle   一般使用：   据说是 select * from [链接表名称]..[Oracle用户名].[表名] （我没试成功过。。）
（2）PL/SQL方式   使用OpenQuery交互（虽然不是最好的，但速度比第一种方法至少快一半）
查询实例：
Select * from OpenQuery(连接服务器名称,'Select * from [表名]') 
新增实例：
INSERT OPENQUERY (链接服务名称, 'SELECT 字段1,字段2 FROM 表') VALUES ('值1', '值2');
顺便提下：OPENQUERY 会返回一个数据集，换言之上面的语句，必须要等表所有数据都select后才会插入数据，如果数据量大这个过程非常漫长，所以上面的语句需要改进下：
INSERT OPENQUERY (链接服务名称, 'SELECT 字段1,字段2 FROM 表 where 1=2') VALUES ('值1', '值2');
更新实例：
UPDATE OPENQUERY (链接服务名称, 'SELECT 字段1,字段2 FROM 表 WHERE id=1') SET 字段1= '值1', 字段2= '值2';
删除实例：
DELETE OPENQUERY (链接服务名称, 'SELECT 字段1 FROM 表 WHERE 字段1=1');
（以上所有操作实例如果链接的服务器是MSSQL，则需要在表名前添加数据库和架构，如：数据库名.dbo.表名）

另外关于引号的小问题也提下，虽然简单但有时候也容易忽略，比如：
Select * from OpenQuery(连接服务器名称,'Select * from [表名] where id=1') 这样没问题，但如果id是字符那么应该是
Select * from OpenQuery(连接服务器名称,'Select * from [表名] where id=''值''') 需要在一前一后加2个 ' ，如果中间的语句再复杂些，或者引入函数等，需要拆开写，那前后就需要加4个' ，反正原则就是引一次加2个'，其实也就是转义。

3、同义词的的使用（仅SQL2005及以上版本支持！）

这个东东很好！很强大！ （OPENQUERY其实也就是它的简化版）

创建语句：

CREATE SYNONYM [同义词名称] FOR [链接服务器名称].[数据库].[架构].[表名]
GO

我的个人理解是，首先通过链接服务器创建远程链接，再通过链接服务器创建同义词到本地数据库，这样一来在本地数据库上就相当于创建了虚拟表（支持4种对象的创建：表、视图、函数、存储过程），远程的表就像在本地一样，所以同义词翻译成链接表或者链接对象似乎更为恰当。。

这样一来，操作远程数据库就非常方便了，不管是ACCESS、SQL、还是Oracle（估计其他数据库链接过来也是一样的道理，前提是需要有相应的访问接口）都可以使用最普通的T-SQL语句操作，如：
select * from 同义词名称 
insert into 同义词名称(...) values(...)
delete 同义词名称 where...
（当然需要执行增加、删除、修改等操作，需要你相对应的链接服务器有相应的权限）


这样一来前面提到的2种访问链接数据库的方式通通都可以放弃，不过可惜的是如果是SQL2000的话，仍然只能使用OpenQuery来交互数据库。。。