Executing Stored Procedures with Text Parameters from ExcelLast reviewed: November 3, 1994Article ID: Q73288 |
The information in this article applies to:
SUMMARYYou 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 INFORMATIONIt 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.XLAA1 =INITIATE("qe","system") A2 =ACTIVATE("sheet1") A3 =DB.LOGON("SQLServer") A4 =DB.SQL.QUERY(2,"sp_helpdb ''pubs''",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 FunctionsA1 chan=INITIATE("qe","system") A2 =EXECUTE(chan,"[logon('SQLServer')]") A3 =ACTIVATE("Sheet1") A4 =EXECUTE(chan,"[open('execute sp_helpdb ''pubs''')]") A5 =EXECUTE(chan,"[copy.special(true,false,2,1)]") A6 =PASTE() A7 =TERMINATE(chan) A8 =RETURN() NOTE: There are two single quotation marks before the parameter pubs and three single quotation marks after.
REFERENCES"Q+E for Microsoft Excel User's Guide," version 3.0, pages 142-143
|
KBCategory: kbother
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |