PRB: Returning Results from SQL Updates/Inserts to Q+E & Excel ID Number: Q70954
1.10 1.11 4.20 OS/2
Summary:
SYMPTOMS When a query is sent to SQL Server through a DDE channel with Q+E, only results from a SELECT statement are returned through the channel once the query has completed. Because of this, if an update or insert sent directly through the channel fails, nothing will be returned.
RESOLUTION One way to work around this problem is to create a stored procedure on SQL Server that performs the insert/update and sends confirmation back to Excel in the form of a result row generated by a SELECT statement.
More Information:
The following is an example of a stored procedure that performs an update and returns a result row specifying that the transaction completed, noting the number of rows affected by the transaction:
create proc proc_1
@parm_old int,
@parm_new int
as
declare @err int
declare @rows voucher(10)
begin
update table_1
set col1 = @parm_new
where col1 = @parm_old
select @err = @@error,
@rows = convert(voucher(10), @@rowcount)
if (@err = 0)
select Message = "Update successful: "
+ @rows + " affected."
end
One method of executing the above stored procedure from Excel involves using the database functions provided by the QE.XLA add-in macro. An example is as follows:
=DB.SQL.QUERY(2,“EXEC PROC1 1, 2”,1)
If the update is successful, the macro will place a message in the active cell stating that the update was successful and noting how many rows were affected.
Additional reference words: 1.10 1.11 4.20
