stored proc being allowed to select but not allowed to execute.

stored proc being allowed to select but not allowed to execute.

Post by Jignesh Pate » Sat, 12 Jan 2002 22:12:34



Hi! All

We are in the process of development of a three-tier
financial accounting software. Our front end is delphi,
middle-tier is asta with delphi and third tier is ms sql
7. The application and database servers are residing on
the same machine in the USA through a web hosting service.
We are based in India. We have been provided with an asp
site to make any changes to the database - like
dropping/recreating etc. Two days back we created a stored
procedure for our application need.
We use a client data set component to access the stored
procedures. In the object browser of this component there
is "stored procedure" property which lists all the stored
procedure in the database. We are able to select the newly
created stored procedure from this list, but whenever we
are trying to execute this stored procedure it gives the
following error: "Invalid object name +  procedure name".
Now to solve this problem we asked the web hosting people
to run a database script which also has this procedure
scripted. Everything works fine. Why is that whenever we
create an object from the asp site, we cannot access the
object? All the time we cannot ask those guys to run the
script for us.  Kindly provide your insight in this
matter. This is really urgent.

Thanks
Jignesh

 
 
 

stored proc being allowed to select but not allowed to execute.

Post by Bob Pfeiff [MS » Sat, 12 Jan 2002 22:53:32


It could be the owner of the objects you create from the asp site is
something other than dbo.  If you are logged in to the asp site under a
specific SQL Server login or database role, then the object is created with
that login as the owner (e.g., databasename.login.objectname).  Then when
the application tries to execute it without specifying the correct owner in
the object name, it can't find the stored proc.

You should check this with the administrators at the hosting service.
Depending on how your asp access to the database works, you could specify
dbo in the create statements (e.g., create proc dbo.p_storedproc as ...).

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

1. stored proc error doesn't allow rollback to execute

When I do an INSERT inside of a stored procedure, and that INSERT
fails due to a trigger, program execution seems to just abruptly end.


tmpError, but it never gets there.

One of the main reasons I want to test for an error is to 'rollback
tran' the transaction that I started at the beginning of my stored
procedure.

The trigger does a rollback, but I am unclear about whether the
transaction that I began gets rolled back also as I did not
specifically get to my rollback code.  I am guessing that the Insert
Trigger was rolling back a transaction that is automatically run for
any insert.

Does the transaction that I began ever get rolled back even though it
never gets to a rollback inside my stored procedure?
--

2. Russian Hackers Offer Databases

3. Microsoft SQL isapi extension - 405 Method Not Allowed - POST queries are not allowed - OpenXML

4. From QuickReport 1.0 to QuickReport 3.0

5. Stored Proc problems - Operation not allowed when the object is closed

6. DTS Error handling from SPROC

7. Allow/Not Allow Duplicates?

8. Populate ListBox

9. nulls not allowed to nulls allowed

10. transactions: what is allowed / not allowed

11. am I not allowed to use

12. rdConcurReadOnly not allowed if not a single select statement

13. Allowing Users to Execute Jobs, Not Your Database