![]() ![]() Please let me know the solution for following error!! Linked Server fails from SQL Server 2008 R2 and PostgreSQL OLE DB provider "MSDASQL" for linked server "PostGreDB" returned message "MSDTC XARMCreate error".Ĭorrect the errors and retry or press ESC to cancel the change(s). Net SqlClient Data Provider.Įrror Message: The operation could not be performed because OLE DB provider "MSDASQL" for linked server "PostGreDB" was unable to begin a distributed transaction. Microsoft SQL Server Management Studio ExpressĮrror Source. Msg 7350, Level 16, State 2, Procedure containerload2, Line 3Ĭannot get the column information from OLE DB provider "MSDASQL" for linked server "PostGreDB". No query has been executed with that handle". OLE DB provider "MSDASQL" for linked server "PostGreDB" returned message "ERROR: column "PID" does not exist Worked somehow (i can see data in a MSSQL Server Management Express View).įROM OPENQUERY(PGCounter, 'SELECT PID, value Can you please help?įROM OPENQUERY(PGCounter, 'SELECT * from "PostGreDB".originalTable ') Keep in mind that the PostgreSQL 64-bit ODBC is marked as experimental, but we have had good success with it on an Intel processor basedĦ4-bit Windows 2003 running SQL Server 2005 64-bit. ' SELECT * From information_schema.tables ' ) SELECT * FROM OpenQuery (NAMEOFLINKEDSERVERHERE, To test out the linked server - you can run the sample query below in SQL Server:.You should also be able to expand the PostgreSQL linked server and see the tables and views. You can fiddle further with the settings. sp_addlinkedsrvlogin rmtsrvname =N ' NAMEOFLINKEDSERVERHERE useself =N ' True ', locallogin = NULL, rmtuser = NULL, rmtpassword = NULLĪfter that you should see the linked server in SQL Server 2005 Management ->Server Objects ->Linked Server and from there sp_addlinkedserver server = N ' NAMEOFLINKEDSERVERHERE ', srvproduct =N ' PostgreSQL AMD64A provider =N ' MSDASQL ', provstr =N ' Driver=PostgreSQL AMD64A uid=pguser Server=pghost database=pgdatabase pwd=somepassword ' /* For security reasons the linked server remote logins password is changed with # */ EXEC master.dbo. Create a Linked Server in SQL Server - below is a sample script that creates a PostgreSQL Linked Server in Microsoft SQL ServerĮXEC master.dbo.SQL Server 2005 that we will outline in the next step. Create a System DSN in the 64-bit Data Source (ODBC) - alternatively you can skip this and use and embedded file DSN in.The newere driver doesn't seem to handle data type conversion quite as well as the older. There is a newer compiled 64-bit ODBC driver at If you are using this newer driver the use PostgreSQL 64-bit ODBC Drivers for the driver name instead of what we have below. The dlls from psqlodbc_AMD64 available from Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (seems to also work fine against 8.3/8.4 if you are running that) and place. ![]() (Available as of from: ) ( If you are running Vista 64-bit or Windows 2008 64-bit these are included already (or possibly in SP1)) Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.īelow are the steps to get a PostgreSQL linked server working in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB providerįor ODBC to use it in SQL Server 2005 64-bit.When Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors. Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL.However you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles. We would like to thank Jeff Crumbley of IILogistics for providing many of these stepsĪnd informing us that Microsoft has finally released aįor those who have not experienced the torture of this situation - let me start with a little background.įirst if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. ![]()
0 Comments
Leave a Reply. |