Bobspadger / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

pyodbc raises exceptions for SQL Server warnings that could be ignored (SQLDriverConnectW IM006 / SQLSetConnectAttr 01000) #112

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?
1. create an empty database in SQL Server
2. create a system DSN in ODBC data sources for that DB
3. connection = pyodbc.connect(dsn="xyz")

What is the expected output? What do you see instead?
Most of the time it works fine. Sometimes on one server pyodbc raises the 
following exception:

Error: ('IM006', "[IM006] [Microsoft][ODBC Driver Manager] Driver's 
SQLSetConnectAttr failed (0) (SQLDriverConnectW); [01000] [Microsoft][SQL 
Native Client][SQL Server]Changed database context to 'xyz'. (5701); [01000] 
[Microsoft][SQL Native Client][SQL Server]Changed language setting to 
us_english. (5703)") 

According to MS documentation:
http://msdn.microsoft.com/en-us/library/ms715433%28v=VS.85%29.aspx
http://msdn.microsoft.com/en-us/library/ms713605%28v=VS.85%29.aspx

IM006 in SQLDriverConnectW means "Driver's SQLSetConnectAttr failed: (DM) 
During SQLDriverConnect, the Driver Manager called the driver's 
SQLSetConnectAttr function and the driver returned an error."

01000 in SQLSetConnectAttr means "General warning: Driver-specific 
informational message. (Function returns SQL_SUCCESS_WITH_INFO.)"

=> looks like it's a warning that could be ignored. However, pyodbc raises an 
exception pyodbc.Error, and there is no easy way for the python app to figure 
out if it's a real error or just a warning.
(especially when using SQLAlchemy or other frameworks on top of pyodbc)

I haven't seen so far what is the real cause of the warning and why it happens 
only on that specific server, but it would be much better if pyodbc could 
provide more info about exceptions to avoid this issue, or if it could ignore 
such warnings when the result of a call is SQL_SUCCESS_WITH_INFO instead of 
SQL_SUCCESS.

What version of the product are you using? On what operating system?
pyodbc 2.1.7 installed on Python 2.6.5, Windows 2003 SP2, MS SQL Server 2005 
full license (not Express edition)

Please provide any additional information below.

Original issue reported on code.google.com by decal...@laposte.net on 29 Jul 2010 at 11:58

GoogleCodeExporter commented 9 years ago
First, is it possible to use a DSN-less connection?  I think that is a valid 
work around.  See here: http://support.microsoft.com/kb/822841

Second, I looked for a way to work around it in the library, but it isn't that 
straightforward.  It is being reported as an error, not as 
SQL_SUCCESS_WITH_INFO.  The SQL_SUCCEEDED macro I use everywhere treats both 
SUCCESS and SUCCESS_WITH_INFO as success, so I always ignore warnings.

That means the connection probably did fail anyway, so ignoring it won't do any 
good.

Notice that the article I linked to mentioned a hotfix is available.  Since it 
is so old, I suspect one of your servers is not up to date with patches?  Is 
that something you can look into also?

I'm going to put this on hold until I hear back, but I'm pretty sure that (1) 
patches or (2) a DSN-less connection would be the best fixes. 

Original comment by mkleehammer on 21 Nov 2010 at 6:01