1

Is it somehow possible to create a view on a Postgres database which gets its original data from a table of a SQL Server database on another server?

I want to have the data from a table of the SQL Server database on server A inside a table on my Postgres database on server B, but I can't use export import, also no scripted solution because I need them nearly realtime.

1

You can use a FDW1 to connection on your MSSQL database and get your data. A FDW expose a remote service (a database, webservice, api, etc) like a table for read and write.

For MSSQL, the tds_fdw2can do the trick.

Let me know if you need a example or more details how the FDW work.

References:

  1. https://wiki.postgresql.org/wiki/Foreign_data_wrappers
  2. http://pgxn.org/dist/tds_fdw/
  • Thanks i tried it but it seem to not work - on the last part - import schema - i get the following Error. - DB-Library error: DB #: 4060, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 11 – konqui Dec 23 '16 at 13:39
  • and what says the mssql log? – Sebastian Webber Dec 23 '16 at 15:15
  • 1
    You might take a look as well to connecting to MS SQL with a FDW that uses ODBC. If on windows, check postgresonline.com/journal/archives/…. If on Linux/(and probably OS X), check github.com/CartoDB/odbc_fdw. The "native" FDW is marked with "Status: Unstable". Haven't found any mention for odbc_fdw. Check also: ODBC Foreign Data wrapper to query SQL Server on Windows – joanolo Dec 23 '16 at 16:26
  • @SebastianWebber it worked with the lastest github branch and by importing the whole Schema (i first tried to make my own foreign table since i only need two of those 45 tables) - only point which isn't working as expected now is that if a original cell value contains a ä/ö/ü i get an empty cell in the foreign table and also the performance is really slow - that way it isn't working for what i wanna use it - i guess i just use connections to multiple Servers from the Program Side by now thanks anyway – konqui Jan 20 '17 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.