Postgresql ODBC驅動，用sqlserver添加dblink跨庫訪問postgresql資料庫

在同樣是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')

結果：調整後就可以查出數據，就可以拿對方資料庫得數據做自己的業務邏輯操作了。