Thread: SQL Server access from PostgreSQL

Hi.

I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw?

In terms of performance / stability / convenience.

(Pg on OpenSuse, MS SQL on Win2008 )

Thanks!

Hi Filip,

On Mon, May 18, 2015 at 7:52 AM, Filip Rembiałkowski
<filip.rembialkowski@gmail.com> wrote:
> Hi.
>
> I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw?
>
> In terms of performance / stability / convenience.
>
> (Pg on OpenSuse, MS SQL on Win2008 )
>
> Thanks!
>

I'm the developer of tds_fdw. I originally developed tds_fdw because I
wasn't able to get odbc_fdw working with FreeTDS. If you try out
odbc_fdw, hopefully you'll have better luck than I did!

If you decide to try out tds_fdw and you have any problems or run into
any bugs, feel free to ask for help on the GitHub page:

https://github.com/GeoffMontee/tds_fdw

Good luck!

Thanks,

Geoff

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


Thanks,

Filip



On Mon, May 18, 2015 at 6:23 PM, Geoff Montee <geoff.montee@gmail.com> wrote:
> Hi Filip,
>
> On Mon, May 18, 2015 at 7:52 AM, Filip Rembiałkowski
> <filip.rembialkowski@gmail.com> wrote:
>> Hi.
>>
>> I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw?
>>
>> In terms of performance / stability / convenience.
>>
>> (Pg on OpenSuse, MS SQL on Win2008 )
>>
>> Thanks!
>>
>
> I'm the developer of tds_fdw. I originally developed tds_fdw because I
> wasn't able to get odbc_fdw working with FreeTDS. If you try out
> odbc_fdw, hopefully you'll have better luck than I did!
>
> If you decide to try out tds_fdw and you have any problems or run into
> any bugs, feel free to ask for help on the GitHub page:
>
> https://github.com/GeoffMontee/tds_fdw
>
> Good luck!
>
> Thanks,
>
> Geoff

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

On Mon, May 18, 2015 at 11:24 AM, Geoff Montee <geoff.montee@gmail.com> wrote:
>
> 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
>
> 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
>

If anyone else is having issues using tds_fdw with Microsoft Azure,
the latest commit adds a foreign server-level "database" option which
fixes the dbuse() issue with Azure. See the GitHub issue for details:

https://github.com/GeoffMontee/tds_fdw/issues/23#issuecomment-109655082

I'll probably package up a tds_fdw 1.0.2 release relatively soon.

Thanks,

Geoff Montee