* FROM OPENQUERY (PGSERVER, ' SELECT table_name FROM information_schema. Our query looked something like below, but any query we used gave similar error with the PostgreSQL site 64-bit drivers. sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL ', provstr =N ' Driver=PostgreSQL 64-bit ODBC Drivers uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' Test query Our connection strings - using the visionmap one look like: - The visionmap on just installs one so registration looks like (this one works fine) EXEC master.dbo. sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL ', provstr =N ' Driver=PostgreSQL 64-bit ODBC Drivers uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' The visionmap on just installs one so registration looks like (this one works fine) EXEC master.dbo. sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL.1 ', provstr =N ' Driver=PostgreSQL Unicode(圆4) uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' Vision Map 64-bit drivers sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL ', provstr =N ' Driver=PostgreSQL Unicode(圆4) uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' EXEC master.dbo. sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL.1 ', provstr =N ' Driver=PostgreSQL ANSI(圆4) uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' EXEC master.dbo. sp_addlinkedserver server = N ' PGSERVER ', srvproduct =N ' PostgreSQL provider =N ' MSDASQL ', provstr =N ' Driver=PostgreSQL ANSI(圆4) uid=pguser port=5432 Server=localhost database=mydb pwd=pgpassword ' EXEC master.dbo. The trick is to use MSDASQL.1 for the provider instead of MSDASQL. GORM officially supports the databases MySQL, PostgreSQL, SQLite, SQL Server, and TiDB MySQLimport ( gorm.io/driver/mysql gorm.io/gorm)func main(). Slapo provided a solution which seemed to solve the problem. The odbc 64 bit installs both a ANSI and Unicode. With the published 64-bit ODBC drivers, we'll be using these 64-bit Official PostgreSQL drivers So for the time being until we figure out if its an issue on our end or something wrong The odbc log looks fine, so seems to be an issue when its coming back to SQL Server. The query runs on PostgreSQL fine and we can see it in the logs. You can however see all the tables via the linked server tab. They seemed to work fine in our MS Access 2010 64-bit install, but when used via SQL Server, SQL Server would choke with a message: Msg 7350, Level 16, State 2, Line 1Ĭannot get the column information from OLE DB provider "MSDASQL" What was different this time is that we decided to use the latest version of the new PostgreSQL 64-bit drivers now available main PostgreSQL site. Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit. ![]() This is something we've done before so not new and something we documented in We were setting up another SQL Server 2005 64-bit where we needed a linked server connection to our PostgreSQL 9.0 server.
0 Comments
Leave a Reply. |