?
Solved

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

Posted on 2003-12-11
17
Medium Priority
?
673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 500 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 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