PRB: Accessing SQL Server from Excel with Q+E Query Files

ID Number: Q70955

1.10 1.11 4.20

OS/2

Summary:

SYMPTOMS

To execute a query on SQL Server and transfer the results to

Microsoft Excel, the DB.SQL.QUERY function (which is accessible

when the QE.XLA add-in macro is loaded) is used because the query

is more than 255 characters long. According to the documentation,

this function allows you to execute a query that resides in an

external file, by using the following parameters:

=DB.SQL.QUERY(3, "C:\SQL\QUERY.QRY", 1)

This function should execute the query in the file and place the

results in the active cell. However, when you attempt to execute

the macro that calls the function, it fails to return any rows.

Using the same query file with ISQL works correctly.

CAUSE

The DB.SQL.QUERY function included with the QE.XLA add-in macro can

be used to execute a Q+E query file (.QEF), as generated by Q+E. It

does not function properly with standard ISQL batch files.

RESOLUTION

To create a Q+E query file, perform the following steps:

1. Build and execute the query in Q+E.

2. From the File menu, choose Save As.

3. Select QueryFile as the destination.

4. Enter the name of the query file.

5. Choose OK to save the file.

Once the file has been generated, you can access it with the

DB.SQL.QUERY function in Excel by specifying the file's full path

and filename in the query parameter.

Additional reference words: 1.10 1.11 4.20