Which provider?

[Buzby]

I'm quite new to the wonderful world of ADO and I'm trying to get my head around providers. I'm connecting to SQL Server 7 and I've tried using MSDASQL and SQLOLEDB - which one is the best one to use?
It seems that whichever one I use I get a trade off between speed and compatibility. When I use SQLOLEDB I get errors about transactions, when I use MSDASQL I get crap performance.
Does anyone have the definitive answer as to which provider is best? I've tried Microsoft and, not suprisingly, they don't have the answer.

[Clunietp]

Definitely SQLOLEDB. This is the native OLEDB provider for SQL Server. MSDASQL provides a layer on top of the ODBC driver, which would explain the speed loss, and you'll probably also have less functionality than the native OLEDB provider would give you.

What "transaction" errors are you getting?

[Buzby]

The error I got when using SQLOLEDB;

"Cannot create new transactions because capacity is exceeded"

When we told Microsoft about this error they suggested that we should use MSDASQL..!!!

[Clunietp]

Well that sucks....

I've searched the web and MS KB and I found nothing....I would suggest applying SQL 7 SP2 and/or reinstall SQL Server (if possible)...just check and make sure this happens from different client machines as well...

[JHausmann]

Also complain to Microsoft about the performance degradation (make grumbling noises about converting to Oracle for effect).