how to run sql scripts from an operators pc
-
HI.I currently have all my database/tlog backups running automatically with database maintenance plans. well we have a software package that runs and of course update the database but there is no backup up that is executed before the update is ran. so i'm having to write an sql script that our operators can run at any given time(the time can vary). UNLESS SOMEONE can tell me how i could use my existing database maintenance plan for this???? Our scenario is sql server 2000 standard edition and it runs on its own box. I would like our operators to be able to access the sql script from their desktop via a mapped network drive to the sql server box. My dilemna comes in where the only method i can think of for them to execute this script would be with query analyzer. (which I will have to install on their machine) If this is the case, can i limit them to JUST BE ABLE TO open and execute the query and nothing else in query analyzer.
very basic question, i know...
juanita
-
I would setup a scheduled job, remove the schedule. Then write a stored procedure to check the execution status of the job and call sp_start_job to start it. This procedure can be called from a button on a simple ASP page.
This way you don't need anything extra on the operators workstation. You don't need to worry about them altering the SQL script, or writing some other script. You don't need to give them elevated privelages to run backups, the connection details you specify in the ASP page will be used to run the job.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
You could look at OSQL batch line script. I use them for quite a bit of routines (Both scheduled and manual) at my company. If you really must do this routine manual then you can at least reduce it to double click execution. And the best part is you can easily pipe the results down to a log file to review later.
-
In addition to my last post I would also create the script you need them to run in a stored procedure. I also have set up a special user for these types of scripts that limits the access down to fit the intended purpose(s). (Since the username and password is plain text in the batch file itself).
-
Have you considered setting up a stored procedure and calling it from script? You could return the recordset and they would never access your environment directly.
'******************************************************************************
'* Make connection to the DB
'******************************************************************************
Public Function MakeConnection()
dim strconnect
strconnect = "Driver={Sql Server}; SERVER=" & DBServer & _
"; DATABASE=" & DatabaseName & "; UID=####; PWD=####"
set objconn = createobject("adodb.connection")
objconn.Open strconnect
End Function
'******************************************************************************
'*
'******************************************************************************
Public Function SubmitQuery()
set objrec = objconn.Execute( "execute storedprocedure")
if not objrec.eof then
SubmitQuery = True
else
SubmitQuery = False
end if
End Function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply