CODEWITHSUNDEEP
t-sqlexcelvba
tonester640
tonester640

Reputation: 94

SELECT and UPDATE in stored procedure called from Excel with VBA

I have created a stored procedure that involves a simple UPDATE and then SELECT statement, which works fine in SSMS - the update works and the select produces a recordset.

The problem is when I am trying:

Set rs = New ADODB.Recordset             'works fine
conn.Open sConnString                    'works fine
Set rs = conn.Execute("EXEC uspUpdateManual")

I get a 'operation is not allowed when the object is closed 3704' error. If I comment out the update part of the stored procedure, the select works fine on its own and the recordset is dropped into Excel.

SP:

ALTER PROCEDURE [dbo].[uspUpdateManual] 
AS
BEGIN TRANSACTION
    UPDATE Table1
    SET ACC = '9'
COMMIT TRANSACTION
BEGIN TRANSACTION
    SELECT * FROM Table1
COMMIT TRANSACTION
END
GO

Is there any way to do both update and select in one, or do I have to separate these into two stored procedures?

Upvotes: 1

Views: 270

Answers (1)

Dane
Dane

Reputation: 287

I think the problem possibly here is with transactions, could you show the procedure code please?. You could try and wrap the update in a transaction and the select in another as show below:

BEGIN TRANSACTION

UPDATE...

COMMIT TRANSACTION

BEGIN TRANSACTION

SELECT...

COMMIT TRANSACTION

Upvotes: 1

Related Questions