I have changed a user connection form WINDOWS NT Trusted Connection to SQL
Authentication. I've kept the roles and permission of the user.


When a  stored proc, called PROC_TEST, is excuted, I get the message:

"Operation is not Allowed when the Object is closed."

This is the store proc:

CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
  ('Value', GETDATE())
SELECT* from TEST
RETURN

This is the call in Visual Basic 6.0:

strSQL = "EXECUTE PROC_TEST "
strCnn  = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyDB;Data Source=MyServer;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh


Notice that ctlADO is an ADODC control.
When I check if the INSERT statement is executed, I see the the new register
was inserted. The problem is that VB doesn't return the SELECT statement.

On the other hand, this stored procedure is executed correctly on the Query
Analyzer Console. It INSERTS data and returns the result form the SELECT
statement.

Additionally, The stored procedure is run correctly where I use a DSN and a
Trusted Connection with the code above.


Any ideas???

The reason for changing the connection is because of Microsoft Office, since
it permits any user connect to the database by using the DSN(ODBC) and
manipulate information.

Thanks for any help.-

You should REALLY be asking in an SQL Server group. For reference, I just
ran into this problem a month ago and I searched the SQL Server groups on
Google. The first several hits solved the issue. I won't give the answer
here, but rather the link and the terms to search with:

http://groups.google.com/advanced_search
Groups: microsoft.public.sqlserver.*
Keywords: "operation allowed object closed recordset"

Personal function library
Multiple exe instances by version
Normalizing data for quick extraction - ideas?
Question about thread safety...
Long process might fail...how to implement a timeout?
DSN-Less Connections
Manifest VB6 IDE
Minimised Application Not Restoring or Maximising
outlook-like application
Should I learn this?