September 27, 2019 at 2:01 pm
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.
September 27, 2019 at 2:07 pm
Without knowing what the proc or code do, how do you expect us to help you with this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 27, 2019 at 4:15 pm
***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
Next
If Not rs.EOF Then
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
October 3, 2019 at 1:02 pm
What happens inside @query? This might need SET NOCOUNT ON as well
October 3, 2019 at 1:40 pm
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 8, 2019 at 5:38 pm
because you can't do rs.EOF if the stored procedure returned a message before it returned the dataset
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply