1

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

Community
  • 1
  • 1
MSBI-Geek
  • 628
  • 10
  • 25

2 Answers2

1

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.

erg
  • 1,632
  • 1
  • 11
  • 23
  • thanks for the reply. I'm assuming ODBC drivers for SQL Server will come as part of Windows 7, please correct me if I'm wrong. Also If I deploy Excel to Sharepoint there is no need of these drivers on every PC as the Excel will run from ExcelServices for Sharepoint in the browser. – MSBI-Geek May 18 '16 at 11:18
  • If ExcelServices is managing the refresh then you will not need the driver on each machine. If ExcelServices is just rendering the file then users will need to refresh the file themselves (and will need the relevant driver) – Brandon McClure Apr 27 '18 at 19:47
1

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.

Mark Iannucci
  • 135
  • 3
  • 11