Solved

Using exec in databound stored procedures

Posted on 2009-04-09
3
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

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 27

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 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