An OLE DB Provider was not specified in the ConnectionString

Question

I am using the following connection string successfully on one machine, but the connection fails on another with the message An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

Data Source=.;Initial Catalog=MyDB;Integrated Security=True

There is one difference between the connection strings -- on the successful machine the database is local, and on the unsuccessful machine the database is on another machine. Otherwise, I'm running exactly the same function on both machines (this is used in a BizTalk Database Lookup functoid). I wrote a quickie app to check that I can successfully open a connection to the remote database using this connection string on the machine that is otherwise failing, and indeed I can successfully connect to the remote database.

The connection works when I change the connection string to

Provider=SQLNCLI10;Server=remoteMachine;Database=MyDB;Integrated Security=SSPI

So even though I have a functioning process now I don't understand the difference in the two machines. I'd like to have a consistent deployment model and tweaking connection strings like this is not something we want to do in our environment. Do you have any thoughts on what the difference between the two machines is that causes the first connection string to not work on our second machine?

Thanks,
Mike

Answer 1

Hello, Mike.

I don't know well about that, but as far as I know,
You have to specify Provider when you use OLEDB or ADO,
You don't need to specify Provider when you use SQL Native Provider or ADO.NET

That's OK, you just keep it.

I hope that helps
Thanks

---

Best Regards,
Jungsun Kim

Answer 2

"Server" and "Database" are ODBC style keywords. OLE DB spec doesn't define them but it allows OLE DB providers to interpret them. Many providers treat "Server" and "Database" as OLE DB keywords "Data Source" and "Initial Catalog", respectively.

It's recommended to use "Data Source" and "Initial Catalog" unless the provider used is MSDASQL.
Provider=SQLNCLI10;Data Source=remoteMachine;Initial Catalog=MyDB;Integrated Security=SSPI

But the error message seems to be generated by application, not OLE DB. Not specifying provider might not be the root cause.

I don't understand what you mean "There is one difference between the connection strings -- on the successful machine the database is local, and on the unsuccessful machine the database is on another machine.".  Do you mean that, on a machine that need to access a remote SQL Server, using "Data Source=.;Initial Catalog=MyDB;Integrated Security=True" will fail? If so, the cause will be using "Data Source=.". As "(local)", "." means the local machine.

Unless you need some unique feature of SQLNCLI10, you can use SQLOLEDB, which is shipped in Windows(no installation is needed).
Provider=SQLOLEDB;Data Source=remoteMachine;Initial Catalog=MyDB;Integrated Security=SSPI

Please let me know if you still see any problem.

Answer 3

Thank you -- my original post was unclear on the connection string I'm using to connect to the remote database. I am specifying the machine name for the Data Source in that connection string, and only using '.' on the machine where the database is local.

I think your answer is helpful, though it still leaves unanswered why the Data Source style works on one machine and not the other.

Mike

Answer 4

Hi Mike,
  If provider is not specified in a connection string, 32 bit OLE DB(including wow64 version on 64 bit OS) will use MSDASQL as the default provider while 64 bit OLE DB will use SQLOLEDB as the default provider. If the machine that can work is a 64 bit system(and, the application you're using is a 64 bit application) and the machine that cannot work is a 32 bit system, most likely the defference on default provider is the cause. 
  Please try ODBC style connection string "Driver={SQL Server};Server=remoteMachine;Database=MyDB;Trusted_Connection=yes" on the machine that doesn't work now. If this works, it can be confirmed that the problem is caused by the fact that MSDASQL is used. MSDASQL is a technique to allow OLE DB application access data sources that have no OLE DB provider but have corresponding ODBC drivers. In your case, it's recommended to use SQLOLEDB.

Regards,
Zheng
  (Note: Although ADO is built on OLE DB, ADO will always use MSDASQL as the default provider if provider is not specificed in a connection string regardless it's 32 bit or 64 bit).

Answer 5

Both machines in question are 64-bit machines. I can live with specifying the Provider and leaving unanswered the question as to the difference.

Thanks,
Mike