tsql - SELECT and UPDATE in stored procedure called from Excel with VBA -

i have created stored procedure involves simple update , select statement, works fine in ssms - update works , select produces recordset.

the problem when trying:

set rs = new adodb.recordset             'works fine conn.open sconnstring                    'works fine set rs = conn.execute("exec uspupdatemanual") 

i 'operation not allowed when object closed 3704' error. if comment out update part of stored procedure, select works fine on own , recordset dropped excel.

sp:

alter procedure [dbo].[uspupdatemanual]  begin transaction     update table1     set acc = '9' commit transaction begin transaction     select * table1 commit transaction end go 

is there way both update , select in one, or have separate these 2 stored procedures?

i think problem possibly here transactions, show procedure code please?. try , wrap update in transaction , select in show below:

begin transaction  update...  commit transaction  begin transaction  select...  commit transaction