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?
Hi Vlad, I can call Powershell scripts directly from Sharepoint? This could be a workaround if we cant figure out a way to call stored procedures directly from Sharepoint…
Hi Mike,
Thanks for your reply. I cant get this to work under 2013 unfortunately… I could be doing it wrong or it isnt supported like this anymore – not sure!
Don’t want to rain on anyones parade but you should exercise caution. Anything outside of the permitted actions (see KB article below)Â are regarded as a deal breaker in terms of support. You should always use the Object Model or Web Service API. Check the following: http://support.microsoft.com/kb/841057
If you didn’t need to do it from a SP Page, I would have suggested PowerShell. I will let devs suggest better solutions!Â
I’m still on 2007 but below is an example of what I have been doing. Basically calling the stored procedure from the SelectCommand attribute on an aspx page. Â Then using ajax to call the page from javaScript.Â
The stored procedure returns an “SUCCESS”  ( “<%# Eval(‘result’)%>”/>) if everything went as planned. I look for the “SUCCESS” in the  ajax response before moving on.
—— BOF:  moveStaff.aspx  ———-
<%@ Page Language=”C#” %>
<asp:Repeater runat=”server” id=”Repeater1″ DataSourceID=”SqlDataSource1″><ItemTemplate><asp:Literal runat=”server” ID=”result” Text=”<%# Eval(‘result’)%>”/></ItemTemplate></asp:Repeater>
<asp:SqlDataSource runat=”server” id=”SqlDataSource1″ ProviderName=”System.Data.SqlClient” ConnectionString=”Data Source=*****\****;Initial Catalog=****;User ID=****;Password=****” SelectCommand=”StaffMove” SelectCommandType=”StoredProcedure”>
<SelectParameters>
<asp:querystringparameter QueryStringField=”idClassroom” DefaultValue=”-1″ Name=”idClassroom” Type=”Int32″ />
<asp:querystringparameter QueryStringField=”idStaff” DefaultValue=”-1″ Name=”idStaff” Type=”Int32″ />
<asp:querystringparameter QueryStringField=”spUserId” DefaultValue=”-1″ Name=”spUserId” Type=”Int32″ />
</SelectParameters>
</asp:SqlDataSource>
—- EOF———-
