MS SQL Server access from Linux/ODBC?

[dan]

I need to access MS SQL Server from our Linux host. Is there currently
a way to do this? If not, any idea how many hours it would take an
experienced programmer to add it? And what would be the best way? ODBC?
I looked into pymssql, but it won't work on our current host since it's
not at version 1.0 yet. And I'd rather not have to move the site.

Thanks.

[Jeremy Dunck]

If you don't want to use pymssql, you'll have a lot of work ahead of
you. And it doesn't seem to fit your rationale, since it's a fair bet
your (new) code would be no more stable than (pretty widely used)
pymssql.

Perhaps you should help push pymssql to 1.0 instead? If NIH, you'll
want to look at FreeTDS, which I think is the underlying C library
pymssql is using to speak SQL Server's language.

In any case, a sticking point to adding support for mssql is that
Django uses non-standard LIMIT/OFFSET clauses. Those are used when
indexing and slicing querysets (which, in turn, is used for pagination
and similar).

[dan]

Jeremy Dunck wrote:
> On 7/15/06, dan <dkor...@sterretech.com> wrote:
> >
> > I need to access MS SQL Server from our Linux host. Is there currently
> > a way to do this? If not, any idea how many hours it would take an
> > experienced programmer to add it? And what would be the best way? ODBC?
> > I looked into pymssql, but it won't work on our current host since it's
> > not at version 1.0 yet. And I'd rather not have to move the site.
>
> If you don't want to use pymssql, you'll have a lot of work ahead of
> you. And it doesn't seem to fit your rationale, since it's a fair bet
> your (new) code would be no more stable than (pretty widely used)
> pymssql.
>
> Perhaps you should help push pymssql to 1.0 instead? If NIH, you'll
> want to look at FreeTDS, which I think is the underlying C library
> pymssql is using to speak SQL Server's language.

Pymssql also depends on "MS SQL 2000 DB-Library for C". And that
library's been deprecated.

There are 3 reasons I'm considering ODBC instead.
1) It should also allow connecting to other db's. Firebird is one I'm
interested in.
2) It hasn't been deprecated AFAIK.
3) It shouldn't be a problem installing it on our existing host. I
could always find another host. So this isn't too important.

[Filipe]

FreeTDS is indeed the the library used by Pymssql, but only in Linux.
For me there's one big disadvantage with pymssql, it has far too many
limitations under windows (it uses DB-Library under windows), which
makes it not a real cross-platform solution. Yet, I'm using it for
connection to MSSql from Linux, it's the only open source library I've
found that isn't windows dependent. If you find any open ODBC driver
for linux please let me know, I haven't found one yet.

Concerning LIMIT/OFFSET clauses, I've just read the thread in the
bellow URL. It's in the context of connecting to MSSql via ADO, but I
think that's irrelevant in this case. any solution found for pymssql
should also work with other providers, and vice-versa.
http://groups.google.com/group/django-developers/browse_frm/thread/6fa803c0fdcb82f3/3afb2c92f67d0511

hope this helps,
Filipe

[Dan Hristodorescu]

Hi Filipe,

Have you tried this?
http://www.egenix.com/files/python/mxODBC.html

For commercial use you might have to pay for it but should run on Linux.

There is also this one (SWIG based):

https://sourceforge.net/projects/pyodb

I cannot say anything about any of them, at work I'm forced  to use Windows with MS-SQL and for my personal projects I would probably choose Postgres on Linux.


Regards,
Dan.

[Filipe]

> Have you tried this?
> http://www.egenix.com/files/python/mxODBC.html
>
> For commercial use you might have to pay for it but should run on Linux.

true, and that's why I don't like it very much :) haven't tried it,
though it seems to have a large user base, so I suppose it's built
right.

> There is also this one (SWIG based):
> https://sourceforge.net/projects/pyodb

didn't know that one, it seems promising, I think I'll try it.

The following message (and it's thread) has a good (non-exhaustive)
review of the several ways to connect to MSSqlserver out there, which
might be useful:
http://groups.google.com/group/comp.lang.python/msg/0efa82b55929803e

> I cannot say anything about any of them, at work I'm forced to use Windows
> with MS-SQL and for my personal projects I would probably choose Postgres on
> Linux.

I'd also prefer to use postgres in this project of mine, but the data I
need is already "packaged" in a MSSqlServer database, so my choice is
bit limited for now.

regards,
Filipe

[Joe]

Have you considered migrating the data from MSSQL to Postgres? We ran
into the same problem you are having, and purchased the "DTM Migration
Kit". http://www.sqledit.com/mk/index.html

This tool lets you do arbitrary sql on migration, so if the MSSQL
database is being used by another app, you can leave unessesary stuff
behind (we left credit card numbers behind...a big advantage).

[Filipe]

Do you know if something equivalent to the "DTM Migration Kit" you
mentioned exists as open software? Using a commercial product is not
very practical in my case, because the clients would have to agree on
buying it, which I belive is not likely.