Re: multiple-step ole db operation generated errors? (Member of pr

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.

--
Hope this helps.

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

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9AAF476A108Yazorman@xxxxxxxxxxxx
Dan Guzman (guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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

.

Relevant Pages

  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... It looks like it is not going to be an easy job getting it to work on the SQL Server 2005 and Express:(. ... 'Then you can execute the command and then retrieve the ID ... I am 100% sure this code works against any version of SQL Server, as long as the connection is OK and the SP has two parameters @varCompany as Input and @Return_Value as Output. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: exec sp_help_job user account rights
    ... I don’t know whether it work or not, but u can create a stored procedure ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Connection.Execute and SQL stored procedures
    ... ..asp, the connection object and MS SQL Server as the DB can help me out. ... Five of the six calls execute as expected. ... This particular stored procedure does quite a bit of work within the DB, ...
    (microsoft.public.sqlserver.odbc)