Postgresql ODBC驱动，用sqlserver添加dblink跨…

2019-01-21 02:42:43来源：博客园阅读 ()

在同样是SQLserver数据库跨库访问时，只需要以下方法

declare @rowcount int
set @rowcount = 0
set @rowcount =(select COUNT(*) from sys.servers where name = 'ITSV2')
if @rowcount <= 0 
begin
exec sp_addlinkedserver  'ITSV2', ' ', 'SQLOLEDB', '192.168.0.222,8989'   --IP，端口号
end 
exec sp_addlinkedsrvlogin 'ITSV2','false',null, 'sa', 'sa1234' --数据库链接账号、密码
--select * from [ITSV2].数据库.dbo.表

做项目的时候遇到数据对接问题，需要从其他地方同步数据到本项目，本项目是使用sqlserver数据库，而对方使用的是postgresql数据库。

一、下载安装postgresql ODBC驱动

在PostgreSql官网下载ODBC驱动，网址：https://www.postgresql.org/ftp/odbc/versions/msi/

本数据库所在的服务器是64位，我找最新版本的64位的

下载下来为 psqlodbc_x64.msi

在网上有人下载使用的的另一个，这个是收费的，但是有免费使用期。

下载好后放在本项目数据库所在服务器上，安装，直接点下一步就好了，

二、ODBC添加数据源

找到控制面板--管理工具--数据源(ODBC)--系统DSN

找到postgresql-完成，然后输入对方的数据库信息，点击测试，显示连接成功。说明和对方的数据库可以连接了。

三、数据库添加dblink，连接对方postgresql，查询数据

1、在数据库中添加linkedserver

execute sp_addlinkedserver 
    @server='sourceDB',    --被访问的服务器别名，可以自己定义
        @srvproduct='Any',
        @provider='MSDASQL',
        @datasrc='PostgreSQL35W'    --被访问的服务器地址(IP地址,端口号\服务器名称)  --PostgreSQL35W 上面第二步设置的名称
--创建本地用户与远程服务器中用户之间的映射

execute sp_addlinkedsrvlogin 
    @rmtsrvname='sourceDB',    --被访问的服务器别名 ，
        @useself='false',    --是否通过模拟本地登录名或显式提交登录名和密码来连接到远程服务器
        @locallogin=null,    --本地登录
        @rmtuser='user01',    --对方数据库用户名
        @rmtpassword='123456'    --对方数据库密码

2、select * from sys.servers 查到刚才添加的，说明添加成功。

--显示的linkedserver
--select * from sys.servers

--同步数据后 可以关闭连接，
--删除运行本地与远程之间的用户映射
--execute sys.sp_droplinkedsrvlogin @rmtsrvname='sourceDB',@locallogin=null
--删除链接服务器
--execute sys.sp_dropserver @server='sourceDB'

3、查询数据

此处可能会遇到的问题：

(1)对方postgresql版本可能较低，需要查询语句中字段、表名都需要加双引号，如果不加会出错，提示不存在表

错误信息：

链接服务器"sourceDB"的 OLE DB 访问接口 "MSDASQL" 返回了消息 "ERROR: relation "lgs_purchaseorder" does not exist;
No query has been executed with that handle"。
消息 7350，级别 16，状态 2，第 114 行
无法从链接服务器 "sourceDB" 的 OLE DB 访问接口"MSDASQL"获取列信息。

(2)报以下错误，一般在查找数字列的时候出现，这个是所查出的数字精度比较大，而sqlserver 查出所表示的精度没有那么大

解决方法可以是不查数字列，或者是将该数字列转换成字符串表达

SELECT * from openquery(sourceDB,'select "OrderNo","ItemNo","PartNo","Qty" from "LGS_PurchaseOrder" order by "OrderNo" desc limit 100') --查询报以下错误

消息 7356，级别 16，状态 1，第 112 行
链接服务器 "sourceDB" 的 OLE DB 访问接口 "MSDASQL" 为列提供的元数据不一致。
对象 "select "OrderNo","ItemNo","PartNo","Qty" from "LGS_PurchaseOrder" order by "OrderNo" desc limit 100"
的列 "Qty" (编译时序号为 4)在编译时有 6 的 "SCALE"，但在运行时有 8。

调整：将数字列调整为字符串

SELECT * from openquery(sourceDB,'select "OrderNo","ItemNo","PartNo",cast("Qty" as char(30)) from "LGS_PurchaseOrder" order by "OrderNo" desc limit 100')

结果：调整后就可以查出数据，就可以拿对方数据库得数据做自己的业务逻辑操作了。

(3) 如何在存储过程中用openquery加参数化查询

一般的加写死的参数方法为

SELECT * from openquery(sourceDB,
'select "OrderNo","ItemNo","PartNo","WindowTime","CloseTime" from "LGS_PurchaseOrder" where "WindowTime" > ''2019-12-18 16:00:00.0000000'' ');

如果需要把条件换成参数，下面这样是不行的，会提示语法错误，

--DECLARE @nowdate NVARCHAR(50)
--SET @nowdate = '2018-08-18 16:00:09.0000000'
SELECT * from openquery(sourceDB,
'select "OrderNo","ItemNo","PartNo","WindowTime","CloseTime" from "LGS_PurchaseOrder" 
where "WindowTime" > '''+@nowdate+'''   limit 100')

正确的解决方法是用exec执行方式，如下

DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
DECLARE @nowdate NVARCHAR(50)
SET @nowdate = '2019-12-18 16:00:09.0000000'
SET @sql ='select * from openquery (sourceDB,''select "OrderNo","ItemNo","PartNo","WindowTime","CloseTime"
 from "LGS_PurchaseOrder" where "WindowTime"> '''''+@nowdate+''''''')';
EXEC(@sql)