0

Execute a stored procedure

Jet Reports Historic Posts

August 26, 2011 17:42

None

Follow

Is it possible to execute a stored procedure within Jet?
I can run a simple SQL statement with this code,
=NL("Rows","SQL=SELECT Field1 from Table",Field1")

,but would like to be able to execute a stored procedure. I've tried this,
=NL("Rows","SQL=EXEC sp_storecproc","Field1")
, but the error I get says, "The SQL query must begin with the 'SELECT' keyword.

Thanks,
Tim

• Facebook
• Twitter
• LinkedIn

6 comments

• 
  Jet Reports Historic Posts August 26, 2011 22:32 Official comment

  Thanks Hughes,
  I can see the point of not allowing that kind of behavior. Thanks for the suggestions, it got me thinking along another line and I may have solved my problem. Instead of trying to use dynamic SQL to change the table selection on the fly, I've created a view that will union the different tables I may be querying and then it's just a matter of filtering.
  
  Thanks again,
  Tim

• 0
  
  Jet Reports Historic Posts August 26, 2011 21:50

  Nope, it is not possible to execute stored procedures. We want to guarantee in Jet that you cannot execute any code which can actually change your database. Therefore we do not allow stored procedures. I realize that this can be a little limiting, but unfortunately it's the nature of Jet Reports. Executing any Jet function which could potentially make data changes is dangerous even if you WANTED the data changed. This is because there is no guarantee of how many times a function may be executed during a report run. We have to make Excel calculate the workbook several times during a report run (particularly while replicating) so the function could get executed multiple times and make multiple unexpected changes. I realize you probably just want to return values from the procedure, but it's a trade-off we had to make in functionality vs. safety.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 26, 2011 21:56

  Thanks Hughes,
  
  I am looking for a way to run dynamic SQL inside a stored proc for a few reasons. I like stored procedures as I can tweek the code on the server instead of the templates which might be who knows where. Also in this case I want to dynamically set the tables, which you can't do in a normal select statement.
  
  Thanks,
  Tim

• 0
  
  Jet Reports Historic Posts August 26, 2011 22:11

  Hi Tim,
  
  You might be able to do something like this by creating a stored procedure which creates a temporary table and populates it with the information you want. You could set up a script on your server to run this stored procedure as often as necessary to update the temporary table. Then you could use regular Jet functions to access the data from the temporary table. Because of the limitations of Jet and our guarantee that your data won't be changed, I don't think it is likely you will ever be able to execute a stored procedure directly from Jet.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 29, 2011 15:23

  Hi Tim,
  
  Hey the view is a great idea! I should have thought of that. ;-) Glad you figured out a way around the issue.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 12, 2013 17:28

  Hi all,
  
  
  YES, it is possible to execute a stored procedure!
  
  Please have a look at "How to parameterize a call to a stored procedure in an NL()"
  
  Regards
  Sascha