multiple-step ole db operation generated errors? (Member of proble

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hidarisei - 29 May 2008 20:43 GMT

I get this error when I run my ado query object which in turn runs a stored
proc.

CREATE PROCEDURE sp_InsertUserLogonDateTime

@UserNRI INT = NULL,
@Type INT = NULL

AS

INSERT INTO UserLogs
(UserLog_DateTime,
LogType_NRI,
User_NRI)
VALUES
(GETDATE(),
@Type,
@UserNRI)
GO

multiple-step ole db operation generated errors. check each ole db status
value, if available. no work was done.

My application runs on every client machine of the windows 2003 server domain.
SQLServer is installed on the system where the domain controller/active
directory is. I added the users to my database using windows authentication
to log in.

Everything is fine if I set each user to be a member of the builtin
administrator group of the server. But if I set the user to be only a member
of the Domain Users group, or any other groups, I get this error msg.

I cannot put every user a member of the server's builtin admin group... that
would be very naughty. What are my choices? What is the problem?

thanks

Reply to this Message

Erland Sommarskog - 29 May 2008 22:33 GMT

> I get this error when I run my ado query object which in turn runs a
> stored proc.

Unfortunately, that's a very generic error, and it can be quite difficult
to say what is going on. Could you post the ADO code as well, in case
that could give a clue?

> CREATE PROCEDURE sp_InsertUserLogonDateTime

Generally, you should not call your stored procedures sp_something, as
this prefix is reserved for system procedures. Whether this has anything
to do with your issue, I don't know, but try renaming to rule out the
possibility.

> SQLServer is installed on the system where the domain controller/active
> directory is.

There is a strong recommendation that you should not run SQL Server on
domain controllers. Again, I don't know if this has anything to do with
your issue. I would not really think so, but I cannot rule it out.

> Everything is fine if I set each user to be a member of the builtin
> administrator group of the server. But if I set the user to be only a
> member of the Domain Users group, or any other groups, I get this error
> msg.

And you granted Domain Users access to this procedures?

Which version of SQL Server are you using?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Hidarisei - 29 May 2008 23:53 GMT

Thank you for responding to my inquiry.

> Unfortunately, that's a very generic error, and it can be quite difficult
> to say what is going on. Could you post the ADO code as well, in case
> that could give a clue?

I know ADO is a generic connection object as I did 3 hours of research
before posting my question.

As for the code, I will try to shorten the example because I use dynamic
creation of connection objects through classes. BTW my code is in C++ Builder
2006.

Example Code:
---------------
TADOConnection *padoCon;
TADOStoredProc *padoSP;
AnsiString strProcName;

// ...
// I set the connection string here
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MYCATALOG;Data Source=MYDATASOURCE"

padoCon = new TADOConnection( NULL );
padoCon->ConnectionString = ( "thestring" );
padoCon->LoginPrompt = false;
// ...

padoSP->Connection = padoCon;
padoSP->CursorLocation = clUseServer;
padoSP->ProcedureName = strProcName;

try
{
padoSP->ExecProc();
}
catch(EADOError &Error)
{
// Show 'Error.Message';
}

> Generally, you should not call your stored procedures sp_something, as
> this prefix is reserved for system procedures. Whether this has anything
> to do with your issue, I don't know, but try renaming to rule out the
> possibility.

I tried that and to no avail.

> There is a strong recommendation that you should not run SQL Server on
> domain controllers. Again, I don't know if this has anything to do with
> your issue. I would not really think so, but I cannot rule it out.

I know this is recommended. I will need to have a standalone server for
domain controller and a separate one who will join the domain for SQLServer.
It is also said that it is recommended to have the Windows 2003 Server OS on
one hard drive and SQLServer on another one.

> And you granted Domain Users access to this procedures?

I will need to remote to work to check that out.

> Which version of SQL Server are you using?

SQLServer 2000.

Thanks

Reply to this Message

Hidarisei - 30 May 2008 00:46 GMT

I have found the problem. My user rights within SQLServer were not granting
access to execute stored procs.

thank you for the suggestion as it lead me to find the problem.

Reply to this Message

Dan Guzman - 30 May 2008 13:11 GMT

>I have found the problem. My user rights within SQLServer were not granting
> access to execute stored procs.

The fact that the proper exception didn't get thrown is an issue. You might
try adding SET NOCOUNT ON to your stored procedures to see if the proper
exception bubbles up.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>I have found the problem. My user rights within SQLServer were not granting
> access to execute stored procs.
>
> thank you for the suggestion as it lead me to find the problem.

Reply to this Message

Erland Sommarskog - 30 May 2008 23:25 GMT

>> I have found the problem. My user rights within SQLServer were not
>> granting access to execute stored procs.
>
> The fact that the proper exception didn't get thrown is an issue. You
> might try adding SET NOCOUNT ON to your stored procedures to see if the
> proper exception bubbles up.

If I understood Hidarisei's post correctly, he had simply failed to
assign permissions to execute the stored procedure. In that case, it
probably does not matter what's in those procedures. :-)

My guess is that he is not checking for errors in the right place,
but jogs alone, and tries to get a result set which is not there,
and that's why he get the multi-step error.

Signature

Reply to this Message

Dan Guzman - 31 May 2008 15:05 GMT

> If I understood Hidarisei's post correctly, he had simply failed to
> assign permissions to execute the stored procedure. In that case, it
> probably does not matter what's in those procedures. :-)

I agree that the SET NOCOUNT ON wasn't the problem in this particular case.
However, I've seen the DONE_IN_PROC messages prevent ADO apps from getting
the SQL Server errors and is why I recommend it as a general practice for
ADO applications.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>>> I have found the problem. My user rights within SQLServer were not
>>> granting access to execute stored procs.
[quoted text clipped - 10 lines]
> but jogs alone, and tries to get a result set which is not there,
> and that's why he get the multi-step error.

Reply to this Message