Solved

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

Posted on 2003-12-11
17
629 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now