Run time error 3704 "Operation is not allowed when object is closed"

Question

Run time error 3704 "Operation is not allowed when object is closed"

ng_rai

SSC Enthusiast

Points: 189
More actions
September 27, 2019 at 2:01 pm

#3684036

Hi,
I have a VB code linked to SQL server SP which runs the SP first and pull the data in Excel.
When I am running the SP in SQL, its running absolutely fine and giving me message "1 row affected " in message window.
However, when I am running VB code, it gives me error mentioned above.
Note that I am already using "SET NOCOUNT ON" in my SP.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Without knowing what the proc or code do, how do you expect us to help you with this?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ng_rai SSC Enthusiast Points: 189 More actions · Answer 2

***Part of the code(Its a really big code and wont be able to paste completely here****

USE [AccessTest]

GO

/****** Object: StoredProcedure [dbo].[SP_FlagTests_CY] Script Date: 2019-09-27 9:36:14 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_FlagTests_CY]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @CYMinus1Table varchar(50), @CYMinus2Table varchar(50), @CYMinus3Table varchar(50)

set @CYMinus1Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 2 as varchar(4))) + ']'

set @CYMinus2Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 3 as varchar(4))) + ']'

set @CYMinus3Table = 'Accesstest.dbo.[ACAR Historical Data_' + (select cast(year(getdate()) - 4 as varchar(4))) + ']'

declare @query varchar(max)

CODE in VB is :

Public cn As ADODB.Connection

Public rs As ADODB.Recordset

Public rs1 As ADODB.Recordset

Const connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=SVF-ASSURDB01\MEMBERDATA;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ASR-DC2-TS1;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AccessTest"

Sub FromSQL()

Mypassword = ""

If InputBox("Please enter password to proceed.", "Password") <> Mypassword Then

MsgBox ("Wrong, please try again.")

Exit Sub

Else: End If

' Then msgbox("wrong try again",vbOKOnly)

'Worksheets("From SQL").Range("C1").Value = "Retrieving data. Please wait..."

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

cn.CursorLocation = adUseClient

cn.Open connstr

'set the procedure to a variable to execute

sqlQuery = "exec AccessTest.dbo.SP_ACAR_CY"

'Execute the stored procedure

Set rs1 = cn.Execute(sqlQuery)

sqlQuery2 = "Select * from [AccessTest].[dbo].[ACAR Historical Data_CY] order by [Company Name]"

Set rs = cn.Execute(sqlQuery2)

RecordCount = rs.RecordCount

FieldCount = rs.Fields.Count

On Error Resume Next:

Worksheets("From SQL").Range("A2:ZZ500").Clear

For i = 1 To FieldCount

With Worksheets("From SQL").Cells(2, i + 1)

.Value = rs.Fields(i - 1).Name

.Font.Bold = True

End With

Worksheets("From SQL").Range("B3").CopyFromRecordset rs

rs.Close

End If

StartCell = "B3"

LastCell = Range("B3").SpecialCells(xlCellTypeLastCell).Address

Worksheets("From SQL").Range(StartCell, LastCell).Value = Worksheets("From SQL").Range(StartCell, LastCell).Value

'lets set font type and size to the sheet

With Worksheets("From SQL").Range("A1:ZZ500")

.Font.Name = "Times New Roman"

.Font.Size = 12

End With

'lets autofit the columns

'Worksheets("Sheet1").Range(StartCell, LastCell).Columns.AutoFit

'Worksheets("From SQL").Range("C1").Value = ""

Sheets("From SQL").Rows("2:2").WrapText = True

Sheets("From SQL").Rows(2).RowHeight = 120

Sheets("From SQL").Columns("A:ZZ").ColumnWidth = 12

Sheets("From SQL").Columns("C:C").ColumnWidth = 48

End Sub

aaron.reese SSChampion Points: 13498 More actions · Answer 3

What happens inside @query? This might need SET NOCOUNT ON as well

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

ng_rai wrote:

When I am running the SP in SQL, its running absolutely fine and giving me message "1 row affected " in message window.
Note that I am already using "SET NOCOUNT ON" in my SP.

This message should not appear if SET NOCOUNT ON is in your proc. This is what you should focus on ... the proc must be doing something else, which we cannot see, to cause this behaviour.

Also, how is the subject of this thread (Run time error 3704 "Operation is not allowed when object is closed") related to this issue?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

aaron.reese SSChampion Points: 13498 More actions · Answer 5

because you can't do rs.EOF if the stored procedure returned a message before it returned the dataset