ID Number: Q67903
1.10 1.11 4.20
OS/2
Summary:
The QE.XLM macro that comes with Q+E allows you to execute simple
SELECT statements and extract data from SQL Server into Excel
worksheets. However, it does not include options for executing SQL
Server stored procedures. You can implement this type of functionality
by creating a custom Excel macro. A macro of this type initiates a
dynamic data exchange (DDE) channel with Q+E, sends a command, and
processes any results that might be returned.
The method for initiating a channel with Q+E and sending commands is
described in the "Using Dynamic Data Exchange (DDE)" section of the
"Microsoft Excel Q+E User's Guide." In addition, an in-depth
discussion of Excel macros can be found in the "Microsoft Excel
Functions and Macros" guide.
More Information:
An example of an Excel macro that executes the sp_helpdb stored
procedure is listed below:
chan=INITIATE("QE","SYSTEM")
=EXECUTE(chan,"[OPEN('master..sp_helpdb')]")
NR=REQUEST(chan,"NUMROWS")
NC=REQUEST(chan,"NUMCOLS")
=EXECUTE(chan,"[FETCH('EXCEL','SHEET1','R1C1:R"&NR&"C"&NC&"','ALL')]")
=EXECUTE(chan,"[CLOSE()]")
=TERMINATE(chan)
=RETURN()
This macro will start Q+E (if it is not already running), allow you to
log on to SQL Server, and execute the sp_helpdb stored procedure. The
results are then placed on a worksheet called SHEET1.
When executing stored procedures from Excel, there are two limitations
worth noting:
- Excel has a formula limit of 255 characters, thus limiting the size
of your execution string. This means that your execute statement,
stored procedure name, and any parameters you may want to supply
must not exceed this limit.
- If the stored procedure is designed to return results from more
than one SELECT statement, only the first set of results will
actually be returned. However, with careful planning of your stored
procedures, this situation can be avoided. An example of this would
be to insert data from several SELECT statements into a temporary
table, and in turn, select the temporary table in order to retrieve
the combined results.
Additional reference words: 1.10 1.11 4.20 Q+E