Solved

Using exec in databound stored procedures

Posted on 2009-04-09
3
303 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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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