Microsoft KB Archive/73288

{| = Executing Stored Procedures with Text Parameters from Excel =
 * width="100%"|

Last reviewed: November 3, 1994

Article ID: Q73288 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.1 and 3.0
 * Microsoft Excel for OS/2, version 3.0

SUMMARY
You can execute SQL Server-stored procedures from Microsoft Excel in a custom macro by using the macro functions provided in the add-in QE.XLA or by sending commands to Q+E via DDE (Dynamic Data Exchange).

Parameters passed to the stored procedure must be placed after the name of the procedure and separated with a single space.

MORE INFORMATION
It is important that when you pass a text parameter that you enclose it in double quotation marks. Because the whole query passed from Microsoft Excel is in double quotation marks and you may not place a set of double quotation marks inside another set, you must use two sets of single quotations marks around the text parameters when executing a stored procedure from Microsoft Excel.

The two examples below execute the stored procedure SP_HELPDB to obtain information on the example database PUBS. The information returned from the server is pasted to Sheet1.

The first example uses the macro functions supported by the QE.XLA. For this to work correctly, you must first open QE.XLA from the \XLSTART\QEMACRO subdirectory of your Excel directory.

Example Using Functions Supplied by the QE.XLA
A1 =INITIATE(&quot;qe&quot;,&quot;system&quot;) A2 =ACTIVATE(&quot;sheet1&quot;) A3 =DB.LOGON(&quot;SQLServer&quot;) A4 =DB.SQL.QUERY(2,&quot;sp_helpdb pubs&quot;,1,FALSE) A5 =RETURN

NOTE: In A4, there are two single quotation marks before the parameter pubs and two single quotation marks, followed by a double quotation mark, after pubs.

Example Using DDE Functions
A1 chan=INITIATE(&quot;qe&quot;,&quot;system&quot;) A2 =EXECUTE(chan,&quot;[logon('SQLServer')]&quot;) A3 =ACTIVATE(&quot;Sheet1&quot;) A4 =EXECUTE(chan,&quot;[open('execute sp_helpdb pubs')]&quot;) A5 =EXECUTE(chan,&quot;[copy.special(true,false,2,1)]&quot;) A6 =PASTE A7 =TERMINATE(chan) A8 =RETURN

NOTE: There are two single quotation marks before the parameter pubs and three single quotation marks after.