Connecting to Microsoft SQL Server

The SaveToDB add-in enables connections to SQL Server tables, views, stored procedures, and table functions.

It supports:

• Microsoft SQL Server 2005 or higher
• Microsoft SQL Server Express LocalDB
• Azure SQL Database
• Azure SQL Data Warehouse

The SaveToDB add-in works with the following data providers:

• OLE DB providers
• ODBC drivers, including DSN files
• .NET Framework Data Provider

Microsoft Office installs the Microsoft OLE DB Provider for SQL Server, which simplifies sharing workbooks with other users.

However, this OLE DB provider may not support the latest SQL Server data types and authentication methods. You can download and install the latest SQL Server OLE DB providers or ODBC drivers for improved compatibility.

You can also use the .NET Framework Data Provider, but note that Microsoft Excel does not support .NET Framework Data Providers. Unlike OLE DB providers and ODBC drivers, data connected via .NET providers can only be reloaded using the SaveToDB add-in.

Below is a sample of the Database Connection Wizard page where you can select the data provider:

Connecting to SQL Server Databases

Use the following server name format for Microsoft SQL Server:

<Server name or IP-address>[,<Port>][\<Instance name>]

Here’s a sample of the database connection page:

When a user specifies the server and login credentials, the add-in attempts to load a list of available databases.

Here’s an example of connecting to a local server on port 1433:

Below is a sample connection to a named instance, SQLEXPRESS:

Here’s an example of connecting to a file database using Microsoft SQL Server Express LocalDB. Use the Browse button to select a file.

The add-in allows you to use attached files as regular databases, as shown in the database list below:

Connecting to a file can be advantageous because the database can be detached when needed.