Solved

How to easily distribute a stored procedure and set up a job

Posted on 2003-12-11
17
657 Views
Last Modified: 2012-08-13
My company writes a software application that uses MS SQL Server for a database backend.  We have recently written a stored procedure that optimizes that database, and would like to distribute this to our users.

The question is - how can we best distribute this stored procedure, and get a recurring job set up to run it, with the least impact on the customer?  Ideally, it would work like this:
1.  User downloads a file from our site to temporary folder.
2.  They execute the downloaded file, which prompts them for name/ip of SQL Server, and database name.
3.  The stored procedure gets installed to that database, and a job gets created to run it on a regular interval - all with no user intervention.

Can this be done?  If so, what do I need, where do I go to get more info?

Thanks!
0
Comment
Question by:Thorin
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 4

Expert Comment

by:dasari
ID: 9925020
U need to use a DTS package for the same......create a DTS package and shcedule it to run on regular interval of times....
0
 
LVL 2

Author Comment

by:Thorin
ID: 9925063
Huh?  What does DTS have to do with this?
0
 
LVL 1

Expert Comment

by:jonleehacker
ID: 9925114
You'll have to create a VB (my choice) application that will prompt for the name/ip of the SQL Server. It can then test the connection, create the stored procedure using the connection object (just send the whole "create procedure..." as text). The only thing I'm unsure of is how to start the SQL Server Agent process and create a new job from VB (I'm pretty sure it's possible but beyond me without a little research). The only thing else you'll need to do is make sure the user can log in as the dbo - part of your prompt.

Good Luck,

Jon
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:arbert
ID: 9925460
DTS?  VB?  Dang, why don't we throw some fortran at it too.....


You can create a batch file (or .cmd file)  to prompt for the sql server name and use OSQL to run your script.  The tricky part is the user password--do they have a userid/password to use that will create objects in the database and jobs?
0
 
LVL 1

Expert Comment

by:jonleehacker
ID: 9925496
How will you create the Server Agent Job to run it at regular intervals with OSQL?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9925659
You could use Edlin :)
0
 
LVL 4

Expert Comment

by:dasari
ID: 9926023
my bad, overlooked the post.....
0
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 9926034
You would execute the stored proc called sp_add_job that's located in the msdb database--it would look something like this (again, easily callable by a script):

USE msdb
EXEC sp_add_job @job_name = 'myTestBackupJob',
    @enabled = 1,
    @description = 'myTestBackupJob',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend =2,
    @notify_level_page = 2
--  @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Data',
    @subsystem = 'TSQL',
    @command = 'BACKUP DATABASE msdb TO DISK = ''c:\msdb.dat_bak''',
    @on_success_action = 3,
    @retry_attempts = 5,
    @retry_interval = 5
go

-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Log',
    @subsystem = 'TSQL',
    @command = 'BACKUP LOG msdb TO DISK = ''c:\msdb.log_bak''',
    @on_success_action = 1,
    @retry_attempts = 5,
    @retry_interval = 5
go


Like I said above, I think the big issue you have is the user/password.  Does your client have system admin account that will allow them to create jobs or will you have to code it when you send the app?
0
 
LVL 2

Author Comment

by:Thorin
ID: 9929301
I am thinking that we could certainly require them to run this application as a user with dbo rights.  It would be cool to prompt for userid/password - but not required.
0
 
LVL 2

Author Comment

by:Thorin
ID: 9929439
arbert,

You said "create a batch file (or .cmd file)  to prompt for the sql server name" - how?!?  

-Thorin
0
 
LVL 34

Expert Comment

by:arbert
ID: 9930071
this user would have to be more than DBO--you would have to have a user with sysadmin to create the scheduled job....

I'll throw together a script and post it for ya in a bit...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9930256
Ok, this is a quick and dirty script to show you the skeleton of what it would look like.  This would just have to be run by somebody that has OSQL on their machine or on the server.  For the most part, I would hope that something like this would be performed by a DBA/ADMIN type person anyway.  You'll notice that there is a -i parm on the OSQL command line--this would be the name of your stored proc script file you're distributing (replace input.sql with your actual name).....So, if you take the below text and save it with a .BAT extension, you can run it from a command prompt.



@echo off
if "%1"=="" goto noServer
if "%1"=="?" goto getHelp
if "%2"=="" goto noUser
if "%3"=="" goto noPassword
echo %1 %2 %3
osql -S%1 -U%2 -P%3 -iinput.sql

exit /b

:noServer
echo Please Enter Valid Server Name
exit /b

:noUser
echo Please Enter a Valid UserID
exit /b

:noPassword
echo Please Enter a Valid Password
exit /b

:getHelp
echo This script will install the new options
echo for your sql database.
echo .
echo The Syntax is:   script.bat {servername} {username} {password}
exit /b




0
 
LVL 34

Expert Comment

by:arbert
ID: 9930264
there is all kind of error checking you could add to the above--after the attempted OSQL connection, you could check for a return code to see if there was an invalid login or the proc had a problem for some reason....could be much easier than building an application and having to build and installation program for it (ie, the VB runtime etc)....
0
 
LVL 2

Author Comment

by:Thorin
ID: 9945443
Arbert,

Thanks so much...that is working fine.  One question - I am getting it to work just fine with SQL authentication...what do I need to do to allow the users to use their NT credentials when prompted?

-Thorin
0
 
LVL 34

Expert Comment

by:arbert
ID: 9945863
Instead of passing a -U and -P for the parms, use -E  for a trusted connection.   NOTE: a lot of the OSQL command parms are case sensitive--the user and password parms are (E isn't the same as e).   :)

Brett
0
 
LVL 2

Author Comment

by:Thorin
ID: 9950353
Great.  Thanks again for all your help.

-Thorin
0
 
LVL 34

Expert Comment

by:arbert
ID: 9950453
sure :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

839 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