Re: SQL Server access from PostgreSQL - Mailing list pgsql-general

On Mon, May 18, 2015 at 10:28 AM, Filip Rembiałkowski
<filip.rembialkowski@gmail.com> 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