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