VB6 Error when calling SQL Server SP: "Operation is not allowed when the object is closed"

Question

I have an old VB6 application that used to be connected to production sybase database. The database was recently migrated to SQL Server 2008 R2 and now I'm the process to update the app to keep it up-and-running. The error I'm getting is "Operation is not allowed when the object is closed".

This is the structure of the code on VB6:

Dim sql as String
Dim connString as String
Dim conn as New ADODB.Connection 

connString = "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CatalogName;Data Source=Production_Server_Name;"
sql = "EXEC sp_name @param1, @param2, @param3, @param4"

conn.Open connString

Dim Cmd as ADODB.Command
Set Cmd = New ADODB.Command 
Set Cmd.ActiveConnection = conn
Cmd.CommandText = sql
Cmd.CommandType = adCmdText
Cmd.CommandTimeOut = 300

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open sql, conn --> The Code fails on this line !!!

Set rs = Cmd.Execute() 

If rs Is Nothing Or IsNull(rs) Then
  Set rs = Nothing
  bla bla bla 
  bla bla
End

The stored procedure code is as follows:

CREATE PROCEDURE sp_name 
(
    @param1 as integer, 
    @param2 as integer,
    @param3 as integer, 
    @param4 as nvarchar(150)
 )
 AS
  SET NOCOUNT ON

 -- All the code goes here

 RETURN

Code Structure Notes:

@param1, @param2 and @param1 are integer defined values. @param4 is comma separated string value
I already know ADODB objects can be declared and instanciated at the same line.

Validations I already did/Suggestions already tried without success:

The SP works perfectly when executed from SSMS with a read-only database user.
The SP DOES use variable tables (@VariableTables) for intermediate processing stuffs.
The VB6 code WORKS when calling the "sp_who" bult-in SQL Server SP
The VB6 code WORKS when sql variable contains "SELECT TOP 1 Column FROM DB.dbo.Table"
The SP FAILS even when using full-qualification for the call (EXEC DB.dbo.sp_name)
The SP FAILS even when removing the "EXEC" part of the sql variable

Any word to put light on this topic error be really appreciated and the correct answer accepted inmediately.

Thanks in advance

Based on what you have already tried, it seems there might be an issue in the SP code itself. — Jeremy
– Jeremy, Commented Mar 16, 2015 at 16:56
Try to comment out different sections of the SP, then when the SP runs, you know which section of the SP it has a problem with. — Jeremy
– Jeremy, Commented Mar 16, 2015 at 16:56
Also, you can run the SQL Profiler and turn on error catching so you can see what if any error is being created in the SP. — Jeremy
– Jeremy, Commented Mar 16, 2015 at 16:57
A couple of things: 1. Ensure you are using client side cursors, 2. Have you tried using a different provider (like sqloledb) — Rob
– Rob, Commented Mar 16, 2015 at 17:01
You're missing opening the connection before the execution of the command. — MarceloRB
– MarceloRB, Commented Mar 16, 2015 at 22:12

Dbloch · Accepted Answer · 2015-03-16 17:29:11Z

1

You need to open the connect first.

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

conn.Open()  <-- missing this

rs.Open sql, conn 

Set rs = Cmd.Execute()

answered Mar 16, 2015 at 17:29

Dbloch

2,4061 gold badge16 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB6 Error when calling SQL Server SP: "Operation is not allowed when the object is closed"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related