Why doesn't Microsoft support OLE DB connections to SQL Azure?
Asked Answered
F

3

10

At the MSDN website it says, "Connecting to SQL Azure by using OLE DB is not supported."

There are other places on the web where folks report that it works fine for them after tweaking the server name in the connection string, such as here and here. Even SQL Server's Analysis Services uses OLE DB to connect to SQL Azure!

I develop a native/unmanaged application in Delphi that connects to SQL Server using ADO through the OLE DB provider for SQL Server. I'm considering adding SQL Azure support. It would be really helpful if I could reuse the majority of my code without not too much change. I probably wouldn't consider going this direction otherwise.

It would be helpful if Microsoft were more clear on why "OLE DB is not supported". If there are certain limitations within the use of OLE DB, what are they? Maybe I can work around them, or maybe it wouldn't affect me.

Microsoft also mentions that ODBC is supported. So could I use the "OLE DB provider to ODBC" and connect this way? Or is any combination that includes OLE DB "not supported"?

Fakir answered 21/6, 2010 at 15:5 Comment(1)
I just noticed in the book "Windows Azure Platform" that an OLE DB connection string used to be provided on the SQL Azure portal. This book was published about six months ago.Fakir
S
7

You can use it, however it has not been thoroughly tested for all cases. Essentially, it should work for most things, but there might be a few edge cases where it won't work. Until we document those cases, it remains unsupported. That being said, if you were to use and run into errors, we would love to know about it and prioritize that to be fixed.

Shelled answered 25/6, 2010 at 4:57 Comment(2)
Thanks, dunnry. That helps. I'll give it a try. My usage of OLE DB will be pretty limited (to firehose cursors and update commands). If I do run into discrepencies, what is the most effective way to let the right product team at Microsoft know?Fakir
I would contact the SQL Azure team directly through the blog - blogs.msdn.com/sqlazure. Alternatively, you can always fire an email my way (through dunnry.com) and I will redirect.Shelled
P
4

You can use ADO using the SQL Native Client although this information is hard to find you can read about it here http://msdn.microsoft.com/en-us/library/ms130978(SQL.110).aspx and here http://msdn.microsoft.com/en-us/library/ms131035(SQL.110).aspx.

In the connection string instead of using Provider=SQLOLEDB; we can use Provider=SQLNCLI10;. Also it is recommended to use DataTypeCompatibility=80;. So a SQL Native Client supported connection string would look like this:

"Provider=SQLNCLI10;Server=tcp:MyServerName.database.windows.net;Database=AdventureWorks2008R2;Uid=MyUserName@MyServerName;Pwd=MyPa$$w0rd;Encrypt=Yes;DataTypeCompatibility=80;"

You can also add "MARS Connection=True;" to the connection string for multiple recordsets.

Politburo answered 16/3, 2012 at 23:53 Comment(1)
Thanks; this was helpful. Note you can throw single quotes around the password field if it contains reserved chars like semicolons. see #3178361Shebeen

© 2022 - 2024 — McMap. All rights reserved.