Solved

Using exec in databound stored procedures

Posted on 2009-04-09
3
302 Views
Last Modified: 2013-11-26
Hello,

In VS 2005 I am trying to bind a drop down box to an existing stored procedure.
The existing stored procedure runs a simple query and then executes another stored procedure using exec.

Problem is when attempting to configure the table adapter to point to this procedure, near the end of the wizard an error saying "unable to find stored procedure" appears with the finish dialogue. If I remove the exec line the error goes away. However the execution of the 2nd procedure is needed and furthermore needs to be dynamic. Is there a way allow exec within databound procedures? Or is there another way to accomplish my goal?
0
Comment
Question by:bnrtech
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24110858
It sounds like a permission chaining issue in the database.  Is the second stored procedure in a different database or schema?  Can you test it by connecting to the db with the same credentials that your app is using and see if you can exec the stored procedure.  By default a stored procedure is created with EXEC AS CALLER context.  See BOL Execute As Clause for more info (excert):
"CALLER
Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.
CALLER is the default for all modules except queues, and is the same as SQL Server 2000 behavior."
I bet the user does not have permissions on the second stored procedure or db that it is in.
You can either grant exec on the inner SP to the user or add With Exec As Owner to the outer SP.
0
 

Author Comment

by:bnrtech
ID: 24111391
Hi CGLuttrell,

I have modified the stored procedure a little. I am now using Exec sp_executesql to execute my dynamic sql.  This results in the error going away and every things fine on the table adapter configuration. However when i try to connect my drop down box to the data source there are no available output columns. Yet when i used the preview and passed in an argument it returned exactly what I was expecting. I also tried a table variable with the same results. Does this help any?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24111884
bnrtech,
Glad you got past the db issue.  Unfortunately the DB is where I spend all my time and I am not sure why the data is not showing up when you connect your drop down.  If someone else does not chime in and answer it for you I will try to bounce the question off one of our .Net developers but most have left for a long weekend.  Sorry to not have the answer right away.  Good luck.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question