CODEWITHSUNDEEP
excelodbcoledb
MSBI-Geek
MSBI-Geek

Reputation: 648

Any downsides using ODBC to connect to SQL Server from Excel

I'm creating many Excel reports (Excel 2010 upwards) with data from SQL Server. I first used OLEDB Connection fine, then was forced to switch to ODBC as OLEDB does not support passing parameters to queries or stored procedures from Excel. I was barred from using Macros. The only option is to use ODBC which works great with parameters. However it was very long since I used ODBC, in those days we need to create a DSN and the file needs to exist on each PC to be able to use the connection. My Excel reports will be accessed by many users from Sharepoint. So not possible to copy the DSN file everywhere. So I'm wondering if ODBC connections are much more easier to use now than before. If I use it in my Excel files with windows authentication and deploy it to Sharepoint, will users be able to access it just the way they do with OLEDB?

I don't find much information on this or I might not know where to look for this. I hope someone can help.

Thanks

Upvotes: 1

Views: 1837

Answers (2)

Mark Iannucci
Mark Iannucci

Reputation: 135

I was looking at this question because we're migrating from one SQL Server instance to another and need to update connection strings. In my research, I found Microsoft's documentation of the differences between the various connection providers in a table.

Our new SQL Servers use an AlwaysOn Availability Group which spans subnets, so if we point our clients to the AAG listener, we need to specify MultiSubnetFailover=true in the connection string. Unfortunately, OLEDB doesn't support the MultiSubnetFailover=True keyword in the connection string.

On the other hand, OLEDB connections offer support for OLAP pivot tables while ODBC do not. As a result, we're planning on using a manually updated DNS entry to route OLEDB connections to the primary node and continue using an OLEDB connection.

Upvotes: 1

erg
erg

Reputation: 1652

There is no need to use a DSN (IMHO there never was). You can simply use a connection-string instead of DSN entry. But the corresponding ODBC-driver to connect to the SQL Server needs to be installed on every machine running the queries - that might be an issue.

See here for a list of connection-strings: https://www.connectionstrings.com/sql-server-native-client-11-0-odbc-driver/

Edit: There is one driver that comes as part of Windows 7 (at least on a professional): The driver is named SQL Server. But there are newer drivers to access recent versions of Sql Server, see here: https://msdn.microsoft.com/en-us/library/mt703139%28v=sql.1%29.aspx

I dont know exactly what features are supported / have been added in those newer driver versions.

Upvotes: 1

Related Questions