[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Using exec in databound stored procedures

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
bnrtech
Asked:
bnrtech
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
bnrtechAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now