I have a business process that requires someone to manually copy/paste calls to stored procedures into SQL Management studio… Most of these stored procedures dont even have parameters or result sets. So my question is, can we call these procedures from a page in SP without having to build something in Visual Studo? I picture something like a button (or even hyperlink) that a non-tech user can just click on. This would clean up and simplify this process massively.
Of course, also getting rows from procedure results back into SP would be ideal. Then I could set up some simple reporting (using existing stored procedures) for user feedback. So they know when to click the next button or whatnot 😉
Any ideas dear SP community?
- Make a custom Webpart you can add all your logic inside it click button save button etc run sql query your templates etc
- https://blogs.msdn.microsoft.com/sharepointdev/2011/02/10/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure-jyoti-saikia/#_Toc279612938 bcs
Cheers for that Mike! I will be trying this next week and will let you know if I have success. Thanks again.
Hi Aarron – I finally got a chance to try it on a SharePoint 2013 Foundation and it worked…on my machine.
The basic process was:
- Create a Data Source using the stored procedure. When given the option choose “or specify customer Select, Update….” and on the Select tab, choose the stored procedure from the list.
- Create a new .aspx page and then on the Insert Tab> Data Source > Database Connections, insert the data source with the stored procedure.
- Add an empty repeater. You need something to reference the Data Source from above.<asp:Repeater runat=”server” id=”Repeater1″ DataSourceID=”SqlDataSource”/>
Every time the page is loaded, the stored procedure is run.
For giggles, using jQuery, I wired up button on another page to make an ajax call to the aspx page above.
Hi Aaron
Never used it, but it looks like it could fit the bill! Take a look at this project : http://sqlwebquery.codeplex.com/
Ta
Mark
Darren Hi,
Thanks for your words of caution. In this case I wouldnt be updating the any databases Sharepoint or Office are running on, its a completely separate database with company data.
