In this article we shall demonstrate using Microsoft SQL Server 2005/2008 OPENQUERY AND OPENROWSET to add, delete and update data in PostgreSQL.
First we must start by saying there are a number of ways of copying data between databases. While OPENROWSET is not necessarily the fasted,
in certain cases such as when you are wrapping this in a stored procedure, it is one of the most convenient ways of doing this.
Why on earth would you want to copy data back and forth between 2 servers and 2 disparate DBMS systems for that matter?
We all would like to think we are an island and live in a world with one DBMS system, but we don't. There are many reasons for having multiple DBMS providers in
an organization. Some are better for some things than others, some are more integrated in an environment -- (for example in a windows shop the SQL Server drivers are already loaded on all
windows machines, but PostgreSQL provides the advantage of being able to run on more platforms such a FreeBSD/Unix/Linux box and with cheaper cost and more options for PL programming so is often better for a front-facing DMZ accessible database),
and there are numerous other reasons that are too hard to itemize. The other question of why triggering from SQL Server instead of PostgreSQL is because
its just a little easier from Microsoft SQL Server. The OPENROWSET and OPENQUERY logic that SQL Server provides is just simply better and easier to use than the dblink provided for PostgreSQL. Anyrate with that said lets move on with the show.
Although this example is focused on using PostgreSQL with Microsoft SQL Server, the same technique applies when
copying retrieving updating data from other databases such as MySQL or Oracle or DB II.
OPEN what - what's the difference?
SQL Server provides two functions for doing cross server calls or pulling in files. One is called OPENQUERY and the
other is OPENROWSET. The distinction is fairly simple.
OPENQUERY - needs a reference to an already estabilished Linked server. Linked server as we discussed in our prior article.
OPENROWSET - contains the connection string to the server/file so need not piggy back on a linked server setup. Since OPENROWSET is truly ad-hoc -- you need to
have Ad-hoc Remote Queries enabled in your SQL Server surface area configuration. Or alternatively running the below commands as admin
The SQL Server 2005 examples below use the 64-bit ODBC driver for PostgreSQL and OLEDB Driver for ODBC.
If you are using SQL Server 2008 and you have the PostgreSQL OLEDB driver installed, it appears that that is an option under
installed providers though we haven't tested that out.
Doing a simple select of a PostgreSQL data
For a select from a PostgreSQL table or view or function, you can do this with a linked server, OPENQUERY or OPENROWSET. We shall demonstrate using
Cross Updates doable but potentially very slow depending number of records you are pulling and updating. Things
to keep in mind, include the primary key, only select the columns and rows you wish to update.
'SELECTmykeyAspgkey,myvalueAspgvalueFROMsometableWHEREfielda=''test%''')SET pgvalue = tabref.myvalue
WHERE tabref.mykey = pgkey
We have tested the openquery in SQL Server 2005 using 64-Bit ODBC driver for PostgreSQL and OLEDB driver for ODBC. It works fine except when the columns on the Postgres DB is a text datatype. With the 32-Bit ODBC driver, it allows us to cast or use substr on the text column when bringing it across but the 64-Bit gives an error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "CORE_DEV" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "CORE_DEV".
Have you had this problem before? If so, is there a workaround this issue?
Just tested what you are saying and I get a similar problem, but I was able to work around the issue by CASTING in postgres. So here is a bulk table insert I tried from postgres to sql server. (though haven't yet tried a regular insert, but I assume it should work fine).
'SELECT address, contact_name,
CAST(comments As varchar(8000)) As comments
') As a