Readers' Tips and Tricks - 13 Jul 2000

Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.

SQL.REQUEST: Excel's Sleeping Giant Function

Microsoft Excel has a powerful yet poorly documented and little-known function that lets users run ad hoc queries against a SQL Server database through a Data Source Name (DSN) connection and have the results returned to a specific cell in the Excel spreadsheet or an array. To work correctly, this codeless solution requires Excel 97 or later.

The SQL.REQUEST function differs from Excel's Query Wizard in that the SQL.REQUEST queries can be more complex. The Query Wizard performs only basic SELECTs with a simple WHERE clause. But SQL.REQUEST lets you devise queries that are limited in complexity only by your SQL skill level, then edit those queries directly in a cell. You can also use a cell reference within the query to point to a value or condition elsewhere on the spreadsheet. This functionality lets you change the query dynamically as the referenced cell value changes.

SQL.REQUEST doesn't currently support statements that modify data directly (i.e., UPDATE, CREATE, DELETE, and INSERT), but you can launch some stored procedures. Even with that limitation, you can set up a template formula that uses SQL.REQUEST from within Excel; for example, you can use it to eliminate some Data Transformation Services (DTS) packages and technical support overhead involved in performing ad hoc queries. Many other applications and possibilities exist for combining Excel's SQL.REQUEST function with the power of SQL Server.

To use SQL.REQUEST, you must have appropriate rights on the SQL Server machine, configure a DSN connection to the server through ODBC, install the Excel ODBC add-in (XLODBC.XLA), and enable Macros within the spreadsheet. For whatever reason, the only example in the Microsoft documentation connects to a dBase4 source. The required syntax for connecting to SQL Server is different from that for connecting to dBase4.

Following are examples that demonstrate the minimum required syntax to make the function work with a SQL Server data source. Note that the password in the examples is blank and that Excel 97 is case sensitive. The following formula defines the entire query within the SQL.REQUEST function:

=SQL.REQUEST("DSN=mytest;UID=sa;   PWD=;Database=PUBS",,,"select * fromauthors where lname = 'Jackson'")

Figure 1 shows the results of this query. The next example references a spreadsheet cell to complete the query:

=SQL.REQUEST("DSN=mytest;UID=sa;  PWD=;Database=PUBS",,,"select * from  authors where lname =  " & B2)

Figure 2 shows the results of using the dynamic cell reference formula. You can also reference a spreadsheet cell for the entire query:

=SQL.REQUEST("DSN=mytest;UID=sa;   PWD=;Database=PUBS",,,"" & B2)

You can find additional documentation for this powerful query function in the Excel Help file under the topic SQL.REQUEST.