INF: Executing SQL Server Stored Procedures from Excel

ID: Q67903

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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 query words: Q+E

Keywords : kbinterop SSrvServer
Version : 4.2
Platform : OS/2
Issue type :