Altova XMLSpy 2024 Enterprise Edition

Connecting to Microsoft SQL Server (ODBC)

Home Prev Top Next

This example illustrates how to connect to a SQL Server database through ODBC.

Prerequisites:

Download and install the Microsoft ODBC Driver for SQL Server from the Microsoft website, see https://docs.microsoft.com/en-us/SQL/connect/odbc/download-odbc-driver-for-sql-server. This example uses Microsoft ODBC Driver 17 for SQL Server to connect to a SQL Server 2016 database. You might want to download a different ODBC driver version, depending on the version of SQL Server where you want to connect. For information about ODBC driver versions supported by your SQL Server database, refer to the driver's system requirements.

To connect to SQL Server using ODBC:

1.Start the database connection wizard.

2.Click ODBC Connections.

3.Select User DSN (or System DSN, if you have administrative privileges), and then click Add DSN_add .

4.Select the driver from the list. Note that the driver appears in the list only after it has been installed.

dbc_sqlserver_odbc_01

5.Click User DSN (or System DSN if you are creating a System DSN).

 

Creating a System DSN requires that XMLSpy be run as an administrator. Therefore, in order to create a System DSN, cancel the wizard, make sure that you run XMLSpy as an administrator, and perform the steps above again.

 

6.Enter a name and, optionally, a description to identify this connection, and then select from the list the SQL Server to which you are connecting (SQLSERV01 in this example).

dbc_sqlserver_odbc_02

7.If the database server was configured to allow connections from users authenticated on the Windows domain, select With Integrated Windows authentication. Otherwise, select one of the other options, as applicable. This example uses With SQL Server authentication... , which requires that the user name and password be entered in the relevant boxes.

dbc_sqlserver_odbc_03

8.Optionally, select the Change the default database to check box and enter the name of the database to which you are connecting (in this example, Sandbox).

dbc_sqlserver_odbc_04

9.Click Next and, optionally, configure additional parameters for this connection.

dbc_sqlserver_odbc_05

10.Click Finish. A confirmation dialog box listing the connection details opens.

dbc_sqlserver_odbc_06

11.Click OK. The data source now appears in the list of User or System data sources, as configured, for example:

dbc_sqlserver_odbc_07

© 2017-2023 Altova GmbH