[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure Execution Options

Posted on 2010-01-07
9
Medium Priority
?
256 Views
Last Modified: 2013-11-27
I am looking for a quick solution that is very easy and fast to implement.  

I have a couple of procedures written that I woudl like some specific users to be able to change parameters on and execute.  I don't want to have to install the SQL tools for these users, and I don't want to grant them rights on the database that would give them more access than just these particular procedures.  

I am looking for a small visual studio web or client side EXE or Batch type of method that can prompt for the parameters and run a procedure.  I am asking because I literally don't have much time to make this happen, and am a novice at best with regard to the newer VS amd .NET development tools.

Thanks!

0
Comment
Question by:Supergrov
  • 5
  • 3
9 Comments
 
LVL 10

Expert Comment

by:lof
ID: 26205942
If you want to make it in a batch file then you could use SQLCMD http://msdn.microsoft.com/en-us/library/ms162773.aspx

but in .Net with ADO.Net to write simple application calling few stored procedures should take few minutes, under an hour for sure.

As to permissions give the user explicit permission to execute procedure and nothing else.
0
 
LVL 29

Expert Comment

by:QPR
ID: 26205943
SQL Server Reporting Services
Microsoft Excel (via MS Query)
Quick and dirty exe that has a report viewer control in it.

You don't mention what the SPs do.. is it data retrieval or something else?
0
 

Author Comment

by:Supergrov
ID: 26205963
The procedures are updating information in a database, and returning success or not to the end user.  I am looking to find out if anyone has a quick way to do this and can send me the code or an example if possible on a procedure that returns one row or something.  
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 29

Expert Comment

by:QPR
ID: 26206025
Would a web page work?
Predefined parameter choices or something they'd type?
0
 

Author Comment

by:Supergrov
ID: 26206060
A web page would work fine, and is probably the preferred solution.  I have plenty of IIS servers I could throw it on.  The parameters on two of the procedures could be predefined, but on a third procedure it is going to be typed in by the user.  If need be they could ALL be manualy entered.
0
 
LVL 29

Expert Comment

by:QPR
ID: 26206068
you could use a drop down for the first 2 to prevent typos and a text box on the 3rd.
Give me a moment to drag some stuff on a page and I'll get back to you with some code.
0
 
LVL 29

Expert Comment

by:QPR
ID: 26206136
ok for this to work you need to create a connectionstring in the web.config pointing to the correct server/database. You might want to consider using impersonation that way you can specify that the SP be run as a user who has execute permissions on it.

Change the name of the SP, the parameters and the drop down values to suit.
The 3 pages of (vb.net) code are the web page, the web.config and the code behind page.
Rename them to get rid of the .txt from the end. Create a new web site in vs and import the 3 pages in.
Default.aspx.txt
0
 
LVL 29

Accepted Solution

by:
QPR earned 2000 total points
ID: 26206141
0
 

Author Closing Comment

by:Supergrov
ID: 31674331
THis worked beautifully!  Thanks for the help!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 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