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:
- Build and execute the query in Q+E.
- From the File menu, choose Save As.
- Select QueryFile as the destination.
- Enter the name of the query file.
- 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
