include tds-fdw in windows installer

[HighKeys]

Hi there,

tired for a week to compile tds-fdw for windows, as i have seen in the Repo, some ppl had success but there is no prebuild binary which would work with PG 15+ and so it won't work with WiltonDB, as you are very exp. with the whole ecosystem. Is it possible to build and include in the installer?

I think its a great addon as you can create linked servers in WiltonDB with SSMS.

Also let me know if there is any other way to do that.

thanks and br

[staticlibs]

Hi!

I was thinking about tds_fdw for the initial release, just it is hard to test and also Babelfish docs notes like this one (perhaps outdated) and datatypes limitations were discouraging.

I will look into building tds_fdw for Windows and including it with the installer, on a cursory glance it looks doable (with some additional deps).

Cannot think about any other way, never used dblink or FDWs in practice myself, for linked Postgres it seems to be relatively straightforward to connect but for linked MSSQL tds_fdw seems to be the only option.

[staticlibs]

As an update on this, I've just got the first successful build of tds_fdw for Windows. If all go well, going to do installer update with it included in a few days.

[HighKeys]

Oh wow, you are super fast! Thanks for that. I'm looking forward to test.

[staticlibs]

Published the update, release notes, docs and Linux packages update will follow.

AWS Aurora people did a solid job with sp_linkedservers and OPENQUERY implementation, I've got minimal problems with porting it (after I've got FreeTDS working). I've done very basic testing so far (following this doc), querying MSSQL worked fine, querying in the opposite direction also should work. Feedback is highly appreciated.

PS: didn't have a chance to try user roles management, just I wonder if sp_execute_postgresql can help with it?

[HighKeys]

Thank you, I will update and setup everything tomorrow, I will gather some feedback for you from the Database devs next week.

[HighKeys]

Hi,

sorry to bother you, I tried to follow the docs you linked from your basic tests (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-postgres-linkedservers.html), I'm able to create a LinkedServer from SSMS but i can't fetch any data.

here is what I tried:

EXEC sp_execute_postgresql N'CREATE EXTENSION tds_fdw';

EXEC master.dbo.sp_addlinkedserver @server=N'TestLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'FQDM to the sql server';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TestLinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password';        

Which seems to work fine, as i can see the "TestLinkedServer" under the Linked Servers in SSMS and also in Postgre pgAdmin, but when I try to fetch data like this:

SELECT * FROM OPENQUERY(TestLinkedServer, 'SELECT * FROM DBName.dbo.TestTable');

SELECT * FROM TestLinkedServer.DBName.dbo.TestTable;

I'm getting a error Message which says

TDS client library error: DB #: 20012, DB Msg: server name not found in configuration files...

as I found thats may coming from the freetds.conf file, so I tried to create one and set the Enviroment Var FREETDSCONF to the created File. I added a [Global] section and one for [TestLinkedServer] like it was described here: https://cubist.cs.washington.edu/doc/FreeTDS/userguide/x631.htm

Not sure what I'm missing here, any tip would be appreciated.

Thanks!

[staticlibs]

M, I am running your example locally and cannot immediately see anything wrong with it. I have WiltonDB running in a VM and MSSQL running on a host machine accessible with 192.168.122.1 from the VM. It uses default port 1433.

Created the following table in MSSQL:

use master
create table test1(id int, val nvarchar(1000))
insert into test1 values(41, 'foo')
insert into test1 values(42, 'bar')
insert into test1 values(43, 'baz')

Then in WiltonDB:

EXEC sp_execute_postgresql 'CREATE EXTENSION tds_fdw';
EXEC master.dbo.sp_addlinkedserver @server=N'TestLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'192.168.122.1';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TestLinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'pemacuser',@rmtpassword='pemacuser';   

Then the following works fine for me (note the name of the linked server in lowercase):

SELECT * FROM OPENQUERY(testlinkedserver, 'SELECT * FROM test1');
select * from testlinkedserver.master.dbo.test1

To troubleshoot this from FreeTDS side you need to set TDSDUMP env variable to some path where log file will be written and then restart WiltonDB service.

freetds.conf file needs to be added to c:\freetds.conf or c:\users\<username>\appdata\roaming\.freetds.conf, the path found or not found will be reflected in TDSDUMP log.

I also suggest to try WireShark to troubleshoot the remote connection.

PS: if that is convenient and you have some kind of live chat (Discord?) I can help with troubleshooting it live next few hours.

[staticlibs]

I believe your problem comes from FQDM to the sql server name cannot be resolved, here is the same error example in test suite. Can you change it to IP address to check?

[HighKeys]

Thanks for your answer, I added TDSDUMP to the enviroment vars but it does not dump any logs there, verified the enviroment var with cmd echo %TDSDUMP%. (Also restarted the whole server)

I think I'm maybe missing freeTDS as install (not sure if its included in WiltonDB, but didn't notice that in the install docs).

If you want we could text chat in Discord, sadly I cannot share my screen or something. If you wanna do that my Discord user is: highkeys

Thanks for all your help!

[HighKeys]

I believe your problem comes from FQDM to the sql server name cannot be resolved, here is the same error example in test suite. Can you change it to IP address to check?

Oh yes that solved it! Thank you!

I will continue with some tests now

[staticlibs]

A note just for the record:

I think I'm maybe missing freeTDS as install

All required libs from FreeTDS are included with WiltonDB installer and are installed by default, so no additional installation is necessary for them.

I also added some docs about Linked Servers to the wiki.

[staticlibs]

tds-fdw seems to work fine on Windows now, closing the issue, please reopen if needed.