Question regarding the execution of jobs

Hi,
Assuming that all jobs are configured with SQL Agent, you
could implement automatic notifications using a MAPI
client. You will have to configure a MAPI client and then
configure the Agent to use that profile and then configure
each job for notification.
hth
DeeJay
>--Original Message--
>We have a ton of jobs that we run from time to time and I
am trying to
>figure out a way to let me know who executes a job and
when. Would anyone
>have any ideas they would be willing to share?
>Thanks,
>Jeff
>
>.
>If I understand correctly, this would basically be an email sent to whoever
saying this job has started. How about an approach where everytime this job
is executed, a row gets written to a log table?
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:392901c48f88$950e40c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Assuming that all jobs are configured with SQL Agent, you
> could implement automatic notifications using a MAPI
> client. You will have to configure a MAPI client and then
> configure the Agent to use that profile and then configure
> each job for notification.
> hth
> DeeJay
> am trying to
> when. Would anyone|||Job execution does get sent the job history tables. You could query those.
Another method that may be more conducive to what you are after is to create
a DTS package that uses VBScript, that pushes information to an outside
logfile. Then add that DTS package as steps to your job and send it
whatever data you want to push out.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Jeff" <jeff.southworth@.verizon.net> wrote in message
news:u8s0iu4jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> If I understand correctly, this would basically be an email sent to
whoever
> saying this job has started. How about an approach where everytime this
job
> is executed, a row gets written to a log table?
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:392901c48f88$950e40c0$a501280a@.phx.gbl...
>|||You understood properly.
Here is the approach similar to yours:
You can do this ways (one is done by default):
1. Go into the SQLServerAgent Properties and towards the
bottom under 'Error log', check 'Include execution trace
messages'. This will write all trace messages in the
SQLServerAgent log. This is not recommended since the log
can get quite large and should be only done for
troubleshooting purposes.
2. This is done by default: All job execution history is
retained in the 'sysjobhistory' table in the msdb
database. You can get the job_id and query this table.
However, the options to log here must be specified
according to your needs. For example, how long the history
is kept by the job itself and if your SQLServerAgent is
configured to retain the job history and how long. The
agent job history retention is configured in the
SQLServerAgent properties under 'Job System' tab.
This should do the job.
I would go with option 2 and perhaps create a reporting
table to export data (query whatever you need) out the
sysjobhistory table and then run your reports.
hth
DeeJay
>--Original Message--
>If I understand correctly, this would basically be an
email sent to whoever
>saying this job has started. How about an approach where
everytime this job
>is executed, a row gets written to a log table?
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>news:392901c48f88$950e40c0$a501280a@.phx.gbl...
you[vbcol=seagreen]
then[vbcol=seagreen]
configure[vbcol=seagreen]
and I[vbcol=seagreen]
>
>.
>

Question on automatic copying table from one datbase to another on same SQL Serv

Currently have two databases. A live database and a history database. Also have the ability to purge data from the live to the history database. What I'm looking for is the ability, if the table doesn't exist in history, to automatically create the table in the history database using the format of the table within the live database. Any help would be greatly appreciated. ThanksLog Shipping sounds like a plan

[BOL] Log shipping|||Originally posted by Ruprect
Log Shipping sounds like a plan

[BOL] Log shipping Within the same server? Kinda overkill, isn't it?|||live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option?|||Originally posted by Ruprect
live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option?

I figure if I have to an option would be to use the sys tables for the information and build the create table string on the fly but was hoping there was a quicker and easier way ^_^|||Originally posted by Ruprect
live to history kinda set me off i'm working on something like this right now so i have log shipping on the brain.

i do believe that you do know what i will suggest as his second option? Hmmm, get a real job? OK, that was a joke ;)

Trx Replication? I don't know, still sounds too heavy. I'd drop the idea of having "live history" database residing on the same server. I'd worry about that one once a day, at midnight, after all backups are done.|||How about INSERT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp) if the table exists, or SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) if the target table does not exist?

-PatP|||i was gonna go with dts...
a scheduled import export but this could be handled with a vbscript looking for the existence of the table and running one of two possible sub packages depending on the table's design
the only problem is the fact that this doesnot rely on the actual table's schema. you would have to know that already.

in the case of the actual question, i think that pat has hit on the easiest solution.|||After you do the insert into, won't you still need to create the matching indexes and relationships, or are you not worried about that.|||I would second Ruprect suggestion about using Log shipping and say using your own log shipping you can achieve the task.