[gdal-dev] connect mssql from linux

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] connect mssql from linux

Martin Landa
Hi,

I have problem connecting SQL Server from my Linux machine. Command

$ ogrinfo "MSSQL:database=kn;server=host\kartografiesql;uid=landa;pwd=xxx;driver=ODBC
Driver 11 for SQL Server" --config CPL_DEBUG ON

fails with

ODBC: SQLDriverConnect(database=kn;server=host\kartografiesql;uid=landa;pwd=xxx;driver=ODBC
Driver 11 for SQL Server)
ODBC: ... failed: [unixODBC][Microsoft][ODBC Driver 11 for SQL
Server]Login timeout expired
ODBC: SQLDisconnect()

But same command in Windows VM works

ODBC: SQLDriverConnect(DRIVER={SQL Server Native Client 11.0};database=kn;server
=host\kartografiesql;uid=landa;pwd=xxx)

Note that I had to install on Windows machine SQL Server Native Client
11.0, otherwise connection failed. I tried to install such driver on
my Debian machine [1], but with no success.

Thanks for any pointers in advance! Martin

[1] https://emacstragic.net/2017/11/06/mssql-odbc-client-on-debian-9-stretch/

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Martin Landa
2018-05-06 12:53 GMT+02:00 Martin Landa <[hidden email]>:

> $ ogrinfo "MSSQL:database=kn;server=host\kartografiesql;uid=landa;pwd=xxx;driver=ODBC
> Driver 11 for SQL Server" --config CPL_DEBUG ON
>
> fails with
>
> ODBC: SQLDriverConnect(database=kn;server=host\kartografiesql;uid=landa;pwd=xxx;driver=ODBC
> Driver 11 for SQL Server)
> ODBC: ... failed: [unixODBC][Microsoft][ODBC Driver 11 for SQL
> Server]Login timeout expired
> ODBC: SQLDisconnect()

same results with "Microsoft ODBC Driver 17 for SQL Server" (original
Debian 9 package used)

ERROR 1: Unable to initialize connection to the server for
MSSQL:database=kn;server=hostkartografiesql;uid=landa;pwd=xxx;driver=ODBC
Driver 17 for SQL Server,
[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
Try specifying the driver in the connection string from the list of
available drivers:
ODBC Driver 17 for SQL Server

Ma

[1] https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Martin Landa
Hi,

2018-05-06 13:06 GMT+02:00 Martin Landa <[hidden email]>:

For record, I managed to connect to MSSQL DB using tdsodbc

sudo apt install unixodbc freetds-bin tdsodbc

[SQL Server]
Description = FreeTDS driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
UsageCount = 1

It works perfectly!

Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Even Rouault-2

On dimanche 6 mai 2018 23:06:54 CEST Martin Landa wrote:

> Hi,

>

> 2018-05-06 13:06 GMT+02:00 Martin Landa <[hidden email]>:

>

> For record, I managed to connect to MSSQL DB using tdsodbc

>

> sudo apt install unixodbc freetds-bin tdsodbc

>

> [SQL Server]

> Description = FreeTDS driver

> Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

> Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

> FileUsage = 1

> UsageCount = 1

>

> It works perfectly!

 

Would be a good hint for the driver documentation page.

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Martin Landa
Hi,

2018-05-06 23:22 GMT+02:00 Even Rouault <[hidden email]>:
> Would be a good hint for the driver documentation page.

Will do later (probably tomorrow). Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Jeremy Palmer-3
Note I alway found the FreeTDS driver would truncate values on rows that contained large geometries. Can't remember the size limit though. Would be interesting to know why the LINUX native driver doesn't work.

On Mon, May 7, 2018 at 9:33 AM, Martin Landa <[hidden email]> wrote:
Hi,

2018-05-06 23:22 GMT+02:00 Even Rouault <[hidden email]>:
> Would be a good hint for the driver documentation page.

Will do later (probably tomorrow). Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Martin Landa
Hi,

2018-05-07 1:37 GMT+02:00 Jeremy Palmer <[hidden email]>:
> Note I alway found the FreeTDS driver would truncate values on rows that
> contained large geometries. Can't remember the size limit though. Would be

thanks for warning, in my case I do not care about geometries, so it's fine.

> interesting to know why the LINUX native driver doesn't work.

Right, would be useful. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Martin Landa
In reply to this post by Jeremy Palmer-3
Hi,

2018-05-07 1:37 GMT+02:00 Jeremy Palmer <[hidden email]>:
> Note I alway found the FreeTDS driver would truncate values on rows that
> contained large geometries. Can't remember the size limit though. Would be
> interesting to know why the LINUX native driver doesn't work.

my experience is even worse. It truncate in my case all strings (even
short strings with two characters). Just first character is written do
DB! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Tamas Szekeres
What if you disable bulk insert with "-config MSSQLSPATIAL_USE_BCP NO" ?

Best regards,

Tamas


2018-05-08 18:44 GMT+02:00 Martin Landa <[hidden email]>:
Hi,

2018-05-07 1:37 GMT+02:00 Jeremy Palmer <[hidden email]>:
> Note I alway found the FreeTDS driver would truncate values on rows that
> contained large geometries. Can't remember the size limit though. Would be
> interesting to know why the LINUX native driver doesn't work.

my experience is even worse. It truncate in my case all strings (even
short strings with two characters). Just first character is written do
DB! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Mateusz Loskot
In reply to this post by Martin Landa
FYI, the SQL Server Native Client is not the best choice to connect to the latest MSSQL.
You may want to to switch to the new driver available for both, Linux and Windows


On Tue, 8 May 2018, 18:44 Martin Landa, <[hidden email]> wrote:
Hi,

2018-05-07 1:37 GMT+02:00 Jeremy Palmer <[hidden email]>:
> Note I alway found the FreeTDS driver would truncate values on rows that
> contained large geometries. Can't remember the size limit though. Would be
> interesting to know why the LINUX native driver doesn't work.

my experience is even worse. It truncate in my case all strings (even
short strings with two characters). Just first character is written do
DB! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Jeremy Palmer-3
But does the ODBC driver support the BCP (bulk copy) API? That was the original reason to use the native client. 

On Wed, May 9, 2018 at 8:06 AM, Mateusz Loskot <[hidden email]> wrote:
FYI, the SQL Server Native Client is not the best choice to connect to the latest MSSQL.
You may want to to switch to the new driver available for both, Linux and Windows



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: connect mssql from linux

Mateusz Loskot
AFAIK, yes the new driver does support the extension API (I think the driver installer let's you to install SDK for it). 
The bcp utility that uses the driver is available on Linux. 

Mateusz Loskot, [hidden email]
(Sent from mobile)

On Tue, 8 May 2018, 22:10 Jeremy Palmer, <[hidden email]> wrote:
But does the ODBC driver support the BCP (bulk copy) API? That was the original reason to use the native client. 

On Wed, May 9, 2018 at 8:06 AM, Mateusz Loskot <[hidden email]> wrote:
FYI, the SQL Server Native Client is not the best choice to connect to the latest MSSQL.
You may want to to switch to the new driver available for both, Linux and Windows



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev