A simple INSERT Problem

Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is 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=MyTable;Data Source=MyServe;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.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is 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=MyTable;Data Source=MyServe;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.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is 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=MyTable;Data Source=MyServe;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.
> --
> Rick