pymssql-users — General discussion about pymssql, meant for its users and developers

Re: [Pymssql-users] can't commit / can't rollback errors

[Greg G. <gr...@sn...>]

 > while using pymssql, sometimes it happens a connection starts behaving
 > strangely: every time I try to commit or rollback, a "can't commit" 
or "can't rollback"
 > error is raised and I'm not able to have the correct behaviour again.

I also ran into this; I filed a bug 
https://sourceforge.net/support/tracker.php?aid=2784534
on it.

[Pymssql-users] transaction active after open the connection

[Roberto F. <rob...@gm...>]

Hi,

I just start using pymssql (ver. 0.8.0), and noticed that just after open
the connection, there is a transaction active:

import pymssql
conn =3D pymssql.connect(host =3D "localhost",user =3D"joe", password=3D"xx=
x",
database=3D"testdb")
cur =3D conn.cursor()
cur.execute("select @@trancount")
cur.fetchone()
>>>(1,)

cur.close()
cur =3D conn.cursor()
cur.execute("commit tran")
cur.execute("select @@trancount")
cur.fetchone()
>>>(0,)

I've tried this code against 2 servers with the same result, one SQL Expres=
s
Edition 2005, and one SQL Server 2000 running as production server
I made a test with freetds against the same production server and there is
no transaction open at all.

My questions:
=BFis this a known behavior of pymssql?
=BFis there a parameter to avoid this behavior?

thanks in advance
--=20
Saludos!
Roberto

[Pymssql-users] can't commit / can't rollback errors

[Enrico S. <enr...@gm...>]

Hello,
while using pymssql, sometimes it happens a connection starts behaving
strangely:
every time I try to commit or rollback, a "can't commit" or "can't rollback"
error
is raised and I'm not able to have the correct behaviour again. The
connections operating
from other processes are not affected.

Unfortunately in my case it happens randomly while there is a lot of
cuncurrency and activity
on the DB and it's quite difficult for me to reproduce it.

Do you have any idea about what can I do in order to reset the connection
state and restart
the transaction?
Thanks for your time,
Enrico

Re: [Pymssql-users] OS Authentication

[Brad A. <bra...@gm...>]

On 1/27/07, Rick Morrison <ric...@gm...> wrote:
> Hi Brad,
>
> As far as I know pymssql will theoretically work using Windows
> authentication (never tried it tho...). Pymssql is based on DB-lib,
> which will support it (barely), and that in turn talks over FreeTDS,
> which supports it.
>
> - I think that you'll need to use a DSN vs. uid/pwd
> - I have read somewhere that SQL server needs to be istening on named
> pipes for connections, and that named pipes must be the first
> connection listed.
>
> If you get it to work, post back to the list with your config.

I'm not sure I'm going to be much help with that.  I'm still stuck on
getting pymssql to connect period. I'm running the SQL Server
Express Edition, and have configured the surface area to allow
both TCP/IP and named pipes on both local and remote connections.
I'm trying to connect using a  Python script on the same machine
using the following:

con = pymssql.connect(host='10.0.1.3',user='sa',password='mypwd',database='test_db')

Below is my traceback. My local firewall is not enabled, I'm using
ActiveState Python 2.4.3, and the IP address above is assigned on my
wireless port (no ethernet).  I tried using localhost instead of the
IP address, but that didn't work either. Do I need to specify a port?

Any suggestions?

Traceback (most recent call last):
  File "pymssqTest.py", line 3, in ?
    con = pymssql.connect(host='10.0.1.3',user='sa',password='mypwd',database=
'test_db')
  File "C:\Python24\Lib\site-packages\pymssql.py", line 328, in connect
    con = _mssql.connect(dbhost, dbuser, dbpasswd)
_mssql.error: DB-Lib error message 10004, severity 9:
Unable to connect: SQL Server is unavailable or does not exist.  Unable to conne
ct: SQL Server does not exist or network access denied.
Net-Lib error during ConnectionOpen (Connect()).
Error 2 - The system cannot find the file specified.

Re: [Pymssql-users] OS Authentication

[Rick M. <ric...@gm...>]

Hi Brad,

As far as I know pymssql will theoretically work using Windows
authentication (never tried it tho...). Pymssql is based on DB-lib,
which will support it (barely), and that in turn talks over FreeTDS,
which supports it.

- I think that you'll need to use a DSN vs. uid/pwd
- I have read somewhere that SQL server needs to be istening on named
pipes for connections, and that named pipes must be the first
connection listed.

If you get it to work, post back to the list with your config.

Rick


On 1/27/07, Brad Allen <bra...@gm...> wrote:
> On Jun 21, 2006, at 7:11 PM, Keith Jackson wrote:
>
> > Hi,
> > First off, thanks for developing this code. It has made the fact that
> > I've been forced to use M$ products much less painful. :)
> >
> > Our SQL Server 2005 instance is setup to not support SQL
> > authentication, only OS authentication. On OSX I have no problem
> > making this work by using a username like: HOSTNAME\username in my
> > connect call. Everything then works fine.
> >
> > On windows however, I consistently get login failures. After looking
> > at the network traffic with ethereal it appears that pymssql is
> > trying to do SQL auth instead of OS auth.
> >
> > How can I force pymssql on windows to us OS auth? Any suggestions
> > would be greatly appreciated.
> > thx,
> > --keith
>
> This was posted over six months ago. I am also curious about the
> answer, though I would normally want to use SQL Auth.  Did anyone
> answer it off-list?
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Pymssql-users mailing list
> Pym...@li...
> https://lists.sourceforge.net/lists/listinfo/pymssql-users
>

Re: [Pymssql-users] OS Authentication

[Brad A. <bra...@gm...>]

On Jun 21, 2006, at 7:11 PM, Keith Jackson wrote:

> Hi,
> First off, thanks for developing this code. It has made the fact that
> I've been forced to use M$ products much less painful. :)
>
> Our SQL Server 2005 instance is setup to not support SQL
> authentication, only OS authentication. On OSX I have no problem
> making this work by using a username like: HOSTNAME\username in my
> connect call. Everything then works fine.
>
> On windows however, I consistently get login failures. After looking
> at the network traffic with ethereal it appears that pymssql is
> trying to do SQL auth instead of OS auth.
>
> How can I force pymssql on windows to us OS auth? Any suggestions
> would be greatly appreciated.
> thx,
> --keith

This was posted over six months ago. I am also curious about the
answer, though I would normally want to use SQL Auth.  Did anyone
answer it off-list?

Re: [Pymssql-users] OS Authentication

[Keith J. <KRJ...@lb...>]

Just to avoid any confusion. What I was calling OS auth, is  
officially "Windows Authentication" according to the docs.
--keith

On Jun 21, 2006, at 7:11 PM, Keith Jackson wrote:

> Hi,
> First off, thanks for developing this code. It has made the fact that
> I've been forced to use M$ products much less painful. :)
>
> Our SQL Server 2005 instance is setup to not support SQL
> authentication, only OS authentication. On OSX I have no problem
> making this work by using a username like: HOSTNAME\username in my
> connect call. Everything then works fine.
>
> On windows however, I consistently get login failures. After looking
> at the network traffic with ethereal it appears that pymssql is
> trying to do SQL auth instead of OS auth.
>
> How can I force pymssql on windows to us OS auth? Any suggestions
> would be greatly appreciated.
> thx,
> --keith
>
> All the advantages of Linux Managed Hosting--Without the Cost and  
> Risk!
> Fully trained technicians. The highest number of Red Hat  
> certifications in
> the hosting industry. Fanatical Support. Click to learn more
> http://sel.as-us.falkag.net/sel? 
> cmd=lnk&kid=107521&bid=248729&dat=121642
> _______________________________________________
> Pymssql-users mailing list
> Pym...@li...
> https://lists.sourceforge.net/lists/listinfo/pymssql-users

[Pymssql-users] OS Authentication

[Keith J. <KRJ...@lb...>]

Hi,
First off, thanks for developing this code. It has made the fact that  
I've been forced to use M$ products much less painful. :)

Our SQL Server 2005 instance is setup to not support SQL  
authentication, only OS authentication. On OSX I have no problem  
making this work by using a username like: HOSTNAME\username in my  
connect call. Everything then works fine.

On windows however, I consistently get login failures. After looking  
at the network traffic with ethereal it appears that pymssql is  
trying to do SQL auth instead of OS auth.

How can I force pymssql on windows to us OS auth? Any suggestions  
would be greatly appreciated.
thx,
--keith 

[Pymssql-users] timeout on windows

[Santiago B. <sba...@gm...>]

Hello!

Is there a way to set the timeout value on Windows? Under Linux and
FreeTDS I think it is possible to fine-tune such things through the
freetds.conf file, is there something like that for the Microsoft
driver?

thanks,
Santiago

[Pymssql-users] connect: non-default port on Windows

[Santiago B. <sba...@gm...>]

Ok, I know what was going on now: on Windows, the driver expects the
port number to follow a comma, not a colon!

cheers & thanks,
Santiago

[Pymssql-users] kudos & connection problem

[Santiago B. <sba...@gm...>]

Hello pymssql-users,

I am facing a problem that has perplexed me for a couple of hours by
now. I am accesing an SQL Server 2000 host from two machines using
pymssql on a non default tcp port. The first one runs Linux and
everything works fine. However, I can't connect using the windows
version of pymssql (0.7.3), even though I am passing the exact same
parameters than in the linux one! I know it is not a connectivity
problem (I can connect using Query Analizer in the machine in
question). I know that the windows version of pymssql uses a different
transport layer, does it have any bugs or known limitations that the
linux version does not?

TIA
Santiago

Re: [Pymssql-users] patch pymssql.py - Permits database queries that return no recordsets

[Jakub L. <jl...@gm...>]

I see,

I have back ported the freetds package from unstable and it seems to
work fine for me.

So in order to make everybody happy we would have to make it backwards
compatible
with previous versions of python and previous versions of freetds(the
version in sarge is quite old).
It seems like to much of hassle to make everybody happy, i guess we
just keep rocking with our homemade patches.
Maybe for the next release we just make sure we don't suddenly require
python 2.5/6 and newer freeteds. So that things can stabilize
somewhat.


On 9/12/05, Laura Vetter <Lau...@es...> wrote:
> Jakub,
>
> We are also using debian but our problem isn't with python 2.4, it becaus=
e debian stable uses an old version of freetds and we were unable to get th=
e newer version, which is required for python 2.4, to compile with other re=
quired dependencies on debian.  So it looks like some code in freetds needs=
 to be ported to debian so it can compile.  Or perhaps we could find out wh=
y the new version of pymssql requires the new version of freetds and see if=
 it would make sense to take out that requirement.
>
> Laura.
>
> -----Original Message-----
> From: Jakub Labath [mailto:jl...@gm...]
> Sent: Monday, September 12, 2005 10:57 AM
> To: Laura Vetter
> Subject: Re: [Pymssql-users] patch pymssql.py - Permits database queries
> that return no recordsets
>
>
> Hi Laura,
>
> I believe this is the same problem I had and I also provided a fix for
> it, and Andrzej already added it to current version.
>
> I do have a question, and I am wondering what do you guys think?
> I myself am using python 2.3 and I know that Andrzej is developing for
> python 2.4 and the same as Laura the current version simply won't
> compile for me.
>
> Is there any value in putting some time and effort to make the pymssql
> backwards compatible with previous versions of python.
>
> And if so how far back should we go? Python 2.3. Python 2.2?
>
> I know we should just all be using Python 2.4, but 2.3 is standard on
> debian (which I use). And also on some other systems (OS X Jaguar).
>
> Andrzej, I wouldn't mind helping with this effort since it is in my
> best interest :-).
>
> Your thoughts?
>
>
>
> Best Regards
>
> On 9/12/05, Laura Vetter <Lau...@es...> wrote:
> > Problem:  I want to execute queries that change the schema of a databas=
e and I would prefer that those queries not return any recordsets.
> >
> > Fix: Made it so code looks to see if there are recordsets before going =
into the recordset array and reading from it.
> >
> > Note: Please note that I am using version 0.7.1 because I couldn't inst=
all the latest version because my system was have problems compiling and in=
stalling the dependancies for the latest version.
> >
> > My apologies in advance if you don't want the diff attached.  I tried t=
o check the archives to see how to send it but the archives are either non-=
existant or having problems.  Also, I'm relatively new to python, so please=
 be kind.
> >
> >  <<pymssql.py.diff>>
> >
> > Laura Vetter
> >
> >
> >
> >
>
>

[Pymssql-users] patch pymssql.py - Permits database queries that return no recordsets

[Laura V. <Lau...@es...>]

Problem:  I want to execute queries that change the schema of a database =
and I would prefer that those queries not return any recordsets.

Fix: Made it so code looks to see if there are recordsets before going =
into the recordset array and reading from it.

Note: Please note that I am using version 0.7.1 because I couldn't =
install the latest version because my system was have problems compiling =
and installing the dependancies for the latest version.

My apologies in advance if you don't want the diff attached.  I tried to =
check the archives to see how to send it but the archives are either =
non-existant or having problems.  Also, I'm relatively new to python, so =
please be kind.

 <<pymssql.py.diff>>=20

Laura Vetter