Microsoft KB Archive/70954

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