Error with pymssql on Windows: DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server (localhost:1433)\nDB-Lib error message 20002, severity

[dtiz...@gmail.com]

Hi everyone,

I'm starting with pymssql in my project and I'm getting this error after a few iterations, and I've did some research but found no solutions for my case. Could someone help me?

How to reproduce:

I read a list of values and for each one I create a connection and call the procedure, but it randomly crashes with the given error:

Code for the method that get called n times:

def save_arquivamento(node):

    with pymssql.connect(host=cfg['db-cvm']['host'],user=cfg['db-cvm']['username'],password=cfg['db-cvm']['password'],database=cfg['db-cvm']['database'], port=1433) as conn:

        with conn.cursor(as_dict=True) as cursor:

            cursor.callproc('PROC_NAME',(parameters...))

The stack for the error:

 File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)

pymssql.OperationalError: (20017, b'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server (localhost:1433)\nDB-Lib error message 20002,

severity 9:\nAdaptive Server connection failed (localhost:1433)\n')

The strange thing is that if runs successfully a number of times then it starts crashing with this error.

The versions are:

- Python: Python 3.5.1

- Pip: pip 9.0.1 from c:\programdata\chocolatey\lib\python3\tools\lib\site-packages (python 3.5)

- Pymssql: pymssql-2.1.3-cp35-cp35m-win_amd64.whl (got from MS site, as I couldn't build it myself)

- Windows 10 Pro

- SQL Server Express 2014

Thanks in advance,

Daniel

[pi raspberry]

Same problem and didnt found any solution. My pymssql connection, insert and conn close is in a loop, after some moinutes its crashed with the same error.

[Daniel Tiziani]

I didn't find a solution for it, but I did manage to use pyodbc instead and it worked. Maybe that can help you for now!

If you need tips, tag me.

--
You received this message because you are subscribed to a topic in the Google Groups "pymssql" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pymssql/t1Tg8_DoPpE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pymssql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charlie Bini]

Also running into this issue in a very similar manner, even though out setup is fairly exotic.  I'm noticing that it happens on queries that take more than a minute to return any data, whether it's due to resource locks or calculations.  I thought it might be a timeout issue since it consistently dies around the 60 second mark, but overriding the default connection parameters didn't help.

- Python = 3.6 (on PythonAnywhere)

- MSSQL = 2016

I'm going to see if pyodbc handles this as well

To unsubscribe from this group and all its topics, send an email to pymssql+u...@googlegroups.com.

[Paulo Afonso]

Hi all

In my case we had SQL 2014 Standard on Windows Server 2012 R2, It works well during a couple of days, and on 3rd ou 4th day, give the some message.

"...Erro=(20017, b'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server..."

Of course my windows service don't stop because we detected my exceiption and solve the situation, restarting the service, but loose some minutes to take care of all transactions on queue!

We also try uing pyodbc with the some results, and what we verify is we toke 50~75ms with pymssql and 217~237ms with pyodbc, for each trasaction, so that is a problem for us!

If anyone have a solutions please shared with us.

[David Rueter]

How many SQL connections are open when this fails?

 

To determine the answer, execute  the following query (from another connection, such as in SQL Server Management Studio):

 

SELECT * FROM sys.sysprocesses

 

I am wondering if SQL connections are not being closed.  You might want to explicitly close connections with conn.close()

 

You might also want to take a look at pymssql.set_max_connections(number):  ‘Sets maximum number of simultaneous database connections allowed to be open at any given time. Default is 25.”

--
You received this message because you are subscribed to the Google Groups "pymssql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.

[Paulo Afonso]

We have all connection closed after every transaction, and also destroy the class. 

For every call received by a TCP "call" first create a class object, that try to connect to SQL Server. If not sucess try again, and record that error on Logging.

But after the error happen a few ms after every again is ok and do the job!

[Steve Wagner]

@Paulo Did you ever resolve this issue?  We've run into the same issue in the last couple of weeks and I've been unable to find an answer.

[pjca...@gmail.com]

Hi Steve

 

Yes, we solve based on changed on server site! Thanks for your question!

 

Best Regards

 

Paulo

--
You received this message because you are subscribed to a topic in the Google Groups "pymssql" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pymssql/t1Tg8_DoPpE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pymssql+u...@googlegroups.com.

[Steve Wagner]

What did you change?

[Ariel]

I guess that error message is somewhat generic, since some people here reported that the problem was fixed by changes on the server side.

Anyways, i also started having this error message (for _all_ connection attempts) after updating to 2.1.3.

The problem was the pre-compiled python package for Linux: up to version 2.1.2 pip install was building the code from scratch, 2.1.3 brought a "wheel" file and installed in an instant.

Looking at the changelog:

 

Version 2.1.3 - 2016-06-22 - Ramiro Morales

• We now publish Linux PEP 513 manylinux wheels on PyPI.
• Windows official binaries: Rollback changes to Windows binaries we had implemented in pymssql 2.1.2; go back to using:
  • A statically linked version of FreeTDS (v0.95.95)
  • No SSL support

it seems clear to me that the pre-compiled package now available for Linux does not support SSL/secured connections out of the box, which is what i was using.

The solution for me was avoiding the "wheel" stuff when installing with pip:

pip uninstall pymssql
pip install --no-binary :all: pymssql

[renaud larzilliere]

We are also facing this issue on Windows. Any resolution out there?

[Mark Evans]

We also have the same issue on Windows. Everything has been running fine for a few days, but now crashes after a few iterations. We are also creating and closing the connection in a loop.

-Mark

[Aviad Bitton]

Same here, has anyone managed to solve this ?