Creating a job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a job

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 04:40:47

Hello,
I created a job called 'test'.

Now when i run this script
use [msdb]
select * from sysjobs

I see my job. Now when i execute the job using exec sp_start_job 'test'

After execution, does it still remain in the sysjobs table ? to be executed again and again or its deleted ?

Thanks

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 08:21:09

After you executed the job did you re-query the sysjobs table to see for yourself?

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 08:22:31

No. Am just reading it up and asking questions.

I am testing with a demo server which is an express version, which hasnt got jobs. Before deploying to the live server

hence reason for the questions

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 08:31:30

I see.
A job will stay there until it is explicitly deleted. A job can be automatically executed on a fairly flexible recurring schedule. The sql agent runs all jobs on the server according to each job's schedule and reports/logs status and history. So naturally, the job needs to stay around after execution. It is up to the dba to maintain whether it is "enabled", "scheduled", etc...

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 08:54:02

Ok thanks TG,

one more question. How do you pass a variable to the job ?

USE msdb ;
GO

EXEC dbo.sp_start_job 'test' @transactionID ;
GO

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 09:07:00

Jobs don't take parameters. Since a job is simply a collection of job steps and a job step can be one of many different command types. So it doesn't work that way. If you are using tsql to "launch" the job why not call a procedure from your code instead of a job? As you know a (stored) procedure can take input parameters.

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 09:25:51

Well am going round in circle

I started a thread, asking what to do. As...

1. I have a TSQL script that is called from a .net page and takes a long time to execute so
2. I called it from a trigger, upon completion so that the .net page can be released from the calling app

in view of the above, i was adviced to use a job. Tahts how i came about this

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 09:33:20

LOL - maybe you should have continued the other topic with a followup :)

In that case you can use a job you just need to come up with a way for the jobstep to know what parameters to use. One idea is have your app (or trigger) populate a JobProcessing table which includes all needed info to process the row. Create a stored procedure to process the rows in your table. Call the procedure from seperate code or schedule a job to call your procedure on a schedule. One possibility is to schedule your job to run very often (every minute) if there is work to do it will do it other wise it will wait for the next run to check again.

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 09:37:10

I was told never use a trigger. That its bad performance

_____________________

Yes O !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-11 : 09:43:50

There is always the right tool for the job. If you work in a china shop the owner may tell you "never use a hammer". But every once in a while you need to hang a picture :)

A trigger is a terrible choice for calling executables, performing large processing tasks, and anything that will make the user transaction take significantly longer to run and possibly fail which causes the user transaction fail as well.

However, if you intention is to log all data changes made to a table, then a trigger (on that table) is an excelent choice.

EDIT:
just to be clear, the suggestion (above) was to use the trigger to simply add a row to a processingTable - not to perform the processing.

Be One with the Optimizer
TG

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-11 : 13:25:54

Thanks TG, you are a genius.

Quite clear. Well, am just using the Trigger to call an SP, and its only 1 line of code.

Thanks

_____________________

Yes O !

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-12 : 20:52:37

Here is the earlier post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111958

I only have one trigger in my db and am using .net and MS SQL.

from the advice above, i guess its ok to use a trigger, the trigger is only called when the transactions level is above 250 loops. Which is called by a split function to log a users records.

Its a very heavily hit site, so we are looking at best practices.

_____________________

Yes O !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources