Connecting to Microsoft SQL Server (ADO)
This example illustrates how to connect to a SQL Server database through ADO. These instructions are applicable when you use the recommended Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL), which is available for download at https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15.
Before following these instructions, make sure that you have downloaded and installed the provider above on your workstation. The ADO provider must match the platform of XMLSpy (32-bit or 64-bit). |
If you would like to use other ADO providers such as SQL Server Native Client (SQLNCLI) or Microsoft OLE DB Provider for SQL Server (SQLOLEDB), the instructions are similar, but these providers are deprecated and thus not recommended. Also, for the connection to be successful with a deprecated provider, you may need to set additional connection properties as described in Setting up the SQL Server Data Link Properties.
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is known to have issues with parameter binding of complex queries like Common Table Expressions (CTE) and nested SELECT statements. |
To connect to SQL Server:
1.Start the database connection wizard.
2.Select Microsoft SQL Server (ADO), and then click Next. The list of available ADO providers is displayed. In this example, the Microsoft OLE DB Driver for SQL Server is used. If it's not in the list, make sure that it is installed on your computer, as mentioned above.
3.Click Next. The Data Link Properties dialog box appears.
4.Select or enter the name of the database server, for example, SQLSERV01. If you are connecting to a named SQL Server instance, the server name looks like SQLSERV01\SOMEINSTANCE.
5.If the database server was configured to allow connections from users authenticated on the Windows domain, select Windows Authentication. Otherwise, select SQL Server Authentication, clear the Blank password check box, and enter the database credentials in the relevant boxes.
6.Select the Allow saving password check box and the database to which you are connecting (in this example, "Nanonull").
7.To test the connection at this time, click Test Connection. This is an optional, recommended step.
8.Click OK.