intermittent connection string failure

[Emin]

Dear Experts,

I would like to ask for your help in diagnosing intermittent
occurrences of the "invalid connection string". I have a variety of
client machines all of which use a connection string like the
following via ADO to connect to a MS-SQL SERVER 2005 instance:

Driver={SQL
Server};Server=BCSRV83;Database=MY_DB_NAME;UID=MY_USER;PWD=MY_PWD;Provider=SQLOLEDB.
1;DataTypeCompatibility=80;

Most of the time this works. Once in a while, however, I get the
following exception

Exception was (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE
DB Provider for SQL Server', 'Invalid connection string attribute',
None, 0, -2147467259), None)

This error message comes from the adodbapi.py module which I use to
connect to sql server from Python.

What I find incredibly frustrating is that sometimes this connection
string works and sometimes it does not.

Does anybody know how to fix this, help diagnose this, or another way
to connect to sql server from python that would avoid this?

Thanks,
-Emin

[Erland Sommarskog]

Emin (emin.s...@gmail.com) writes:
> I would like to ask for your help in diagnosing intermittent
> occurrences of the "invalid connection string". I have a variety of
> client machines all of which use a connection string like the
> following via ADO to connect to a MS-SQL SERVER 2005 instance:
>
> Driver={SQL Server};Server=BCSRV83;Database=MY_DB_NAME;

> UID=MY_USER;PWD=MY_PWD;Provider=SQLOLEDB.1;DataTypeCompatibility=80;

>
> Most of the time this works. Once in a while, however, I get the
> following exception
>
> Exception was (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE
> DB Provider for SQL Server', 'Invalid connection string attribute',
> None, 0, -2147467259), None)

I can't say why you only get it intermittently, but both the first and
the last of the attribute are not applicable to SQLOLEDB.

Driver applies to MSDASQL, the OLE DB-over-ODBC driver, and specifies
the ODBC driver.

DataTypeCompatibility applies to SQL Native Client, the successor to
SQLOLEDB that ships with SQL 2005 and SQL 2008. It specifies that
fallbacks should be user for new data types. This is good when you
use SQLNCLI with ADO, becuase ADO don't know about types such as xml.
But if you use SQLOLEDB, which only knows about SQL 2000, you get
that compatibility out of the box.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Emin63]

On May 26, 5:33 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
> I can't say why you only get it intermittently, but both the first and
> the last of the attribute are not applicable to SQLOLEDB.

Thanks. I recall having to add DataTypeCompatibility=80 at some point
due to type issues in ADO as you mention. I don't recall why I put in
Driver={SQL Server}. I just did a quick test with a simple query and
things still seem to work fine without it so I will try removing it
and see if that helps. Could the Driver={SQL Server} cause problems?

More testing suggests that the intermittent failures may be more
likely to happen when many clients are connecting to the server (which
is sql server 2005) running on a separate machine. I guess it is
possible that this is a network issue. Any thoughts on how to verify
that?

Thanks,
-Emin

[Erland Sommarskog]

Emin63 (emin.s...@gmail.com) writes:
> Thanks. I recall having to add DataTypeCompatibility=80 at some point
> due to type issues in ADO as you mention. I don't recall why I put in
> Driver={SQL Server}. I just did a quick test with a simple query and
> things still seem to work fine without it so I will try removing it
> and see if that helps. Could the Driver={SQL Server} cause problems?

It's a little strange that it would only cause problems sometimes.

> More testing suggests that the intermittent failures may be more
> likely to happen when many clients are connecting to the server (which
> is sql server 2005) running on a separate machine. I guess it is
> possible that this is a network issue. Any thoughts on how to verify
> that?

Intermittent errors are often network problems. But the error message
does not point in that direction. It looks like something the OLE DB
provider generates before it hits the network. But maybe the API
produces an incorrect error code.