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

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!
LVL 2
ThorinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dasariCommented:
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
ThorinAuthor Commented:
Huh?  What does DTS have to do with this?
0
jonleehackerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arbertCommented:
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
jonleehackerCommented:
How will you create the Server Agent Job to run it at regular intervals with OSQL?
0
Anthony PerkinsCommented:
You could use Edlin :)
0
dasariCommented:
my bad, overlooked the post.....
0
arbertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThorinAuthor Commented:
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
ThorinAuthor Commented:
arbert,

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

-Thorin
0
arbertCommented:
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
arbertCommented:
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
arbertCommented:
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
ThorinAuthor Commented:
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
arbertCommented:
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
ThorinAuthor Commented:
Great.  Thanks again for all your help.

-Thorin
0
arbertCommented:
sure :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.