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 SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.
Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>
|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>
|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is s
o
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups
?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>>I have a question on how SQL Server updates records. Say assuming if I
>>have a table with 50000 records.. and i need to update a particular field
>>C in this table, based on field A and field B on the same table. I can
>>either write a set based SP or a cursor based SP to do that.
>> If I update it using cursors, I can see the records being updated 1 by 1.
>> If I update the whole thing using a set based SP, am I right to say that
>> I will not see any records updated till the process is over? Does SQL
>> Server does the transaction commitment itself if I write it using the
>> set-based approach?
>> Thanks.
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:
> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Update with set will do a batch updat based on the condition you are gving
> > in the where clause. So until the transaction is commited
> > you will not be able to see change. Where as Cursor does a row by row
> > operation. So after each record update you could see the
> > change.
> >
> > Have a look into ISOLATION LEVEL topic in books online.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Nestor" <n3570r@.yahoo.com> wrote in message
> > news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
> >>I have a question on how SQL Server updates records. Say assuming if I
> >>have a table with 50000 records.. and i need to update a particular field
> >>C in this table, based on field A and field B on the same table. I can
> >>either write a set based SP or a cursor based SP to do that.
> >>
> >> If I update it using cursors, I can see the records being updated 1 by 1.
> >> If I update the whole thing using a set based SP, am I right to say that
> >> I will not see any records updated till the process is over? Does SQL
> >> Server does the transaction commitment itself if I write it using the
> >> set-based approach?
> >>
> >> Thanks.
> >>
> >
> >
>
>