On Mon, May 18, 2015 at 10:28 AM, Filip Rembiałkowski <filip.rembialkowski@xxxxxxxxx> wrote: > Thank you Geoff. > > Actually I have a problem - maybe you can point me in the right direction? > > CREATE EXTENSION tds_fdw; > CREATE EXTENSION > > CREATE SERVER ms FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'ms'); > CREATE SERVER > > CREATE USER MAPPING FOR postgres SERVER ms > OPTIONS (username 'bzzt', password 'blurp'); > CREATE USER MAPPING > > CREATE FOREIGN TABLE test ( id integer ) SERVER ms OPTIONS ( database > 'MyApp', query 'select 1' ); > CREATE FOREIGN TABLE > > SELECT * FROM test; > NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed > database context to 'master'., Server: ms, Process: , Line: 1, Level: > 0 > NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed > language setting to us_english., Server: ms, Process: , Line: 1, > Level: 0 > NOTICE: DB-Library notice: Msg #: 40508, Msg state: 1, Msg: USE > statement is not supported to switch between databases. Use a new > connection to connect to a different database., Server: ms, Process: , > Line: 1, Level: 16 > ERROR: DB-Library error: DB #: 40508, DB Msg: General SQL Server > error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), > Level: 16 > The NOTICE right before the error might provide useful information: NOTICE: DB-Library notice: Msg #: 40508, Msg state: 1, Msg: USE statement is not supported to switch between databases. Use a new connection to connect to a different database., Server: ms, Process: ,Line: 1, Level: 16 tds_fdw doesn't explicitly call the "USE" statement. It calls the dbuse() DB-Library function: https://github.com/GeoffMontee/tds_fdw/blob/master/src/tds_fdw.c#L703 http://www.freetds.org/reference/a00341.html#ga66ea891910f0a357cc78107d6ab7d962 This function does seem to call USE behind the scenes: https://github.com/FreeTDS/freetds/blob/master/src/dblib/dblib.c#L1414 This seems to be a limitation specific to Azure. Are you using that?: http://stackoverflow.com/a/14167073/1732980 It looks like your main option at the moment is to set the default database for the login account on the MS SQL side. Then when the account connects, the database will be automatically selected when the user logs in: https://technet.microsoft.com/en-us/library/ms189828(v=sql.110).aspx For this to work, don't supply the "database" name for the foreign table on the PostgreSQL side I'll try to come up with a permanent fix for this at some point. I added this bug to GitHub. Feel free to subscribe to it: https://github.com/GeoffMontee/tds_fdw/issues/23 Thanks, Geoff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
