Retrive old JobHistory

I have a job which only keeps 100 entries in the job history. When one new
entry comes, the oldest entry in the list is deleted.
I need retrieve the old job histories that have been removed from the
current job history list. How can I do it? Restore DB is not an option.
Thanks a lot,
LixinYou will need to restore msdb(as different db) and look at the table
sysjobhistory, I recommend you iether increase the number of history rows
for that job or write a job which copies the rows to an archive table.
Yovan
"Lixin Fan" <nospam@.hotmail.com> wrote in message
news:uYx1XbouDHA.2712@.tk2msftngp13.phx.gbl...
> I have a job which only keeps 100 entries in the job history. When one new
> entry comes, the oldest entry in the list is deleted.
> I need retrieve the old job histories that have been removed from the
> current job history list. How can I do it? Restore DB is not an option.
> Thanks a lot,
> Lixin
>|||Hi Lixin
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I think a trigger will be a workaround to you question.
Suppose you want to record the job history of Job_test, and the job_id is
={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information from
two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
Suppose you have a table 'job_history' in database 'Database_test', and the
table 'job_history' has the same schema with the table 'sysjobhistory' in
msdb and you want to use this table to save the job history. You can add a
trigger to the sysjobhistory.
The thinking is: when the system add a new entry into the 'sysjobhistory',
check if this new entry is for job_test. If no, then take no action. If
yes, then the trigger will copy this entry in the job_history table in
database_test. By this way, you can record all the job history when the job
occurred.
The code for the trigger would be like this:
CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
FOR insert
AS
Declare @.job_date int
Declare @.job_time int
Declare @.job_idx varchar
Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
job_id=@.job_idx order by rundate,runtime asc)
--if the latest entry in the sysjobhistory is not caused by the job you
want to record
--no action
Begin
Return
End
--If the job you want to record add a new entry
Else
Begin
Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
asc
End
Note:
1) This code is just a thinking, not a tested one
2) This method will add the workload on your system, especially on a system
which has many concurrent jobs.
I hope this will help you to solve your problem. If you still have
questions, please feel free to post message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Great help. Thank you very much.
Lixin
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:NMNq6HxuDHA.2900@.cpmsftngxa07.phx.gbl...
> Hi Lixin
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> I think a trigger will be a workaround to you question.
> Suppose you want to record the job history of Job_test, and the job_id is
> ={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information
from
> two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
> Suppose you have a table 'job_history' in database 'Database_test', and
the
> table 'job_history' has the same schema with the table 'sysjobhistory' in
> msdb and you want to use this table to save the job history. You can add a
> trigger to the sysjobhistory.
> The thinking is: when the system add a new entry into the 'sysjobhistory',
> check if this new entry is for job_test. If no, then take no action. If
> yes, then the trigger will copy this entry in the job_history table in
> database_test. By this way, you can record all the job history when the
job
> occurred.
> The code for the trigger would be like this:
> CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
> FOR insert
> AS
> Declare @.job_date int
> Declare @.job_time int
> Declare @.job_idx varchar
> Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
> If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
> job_id=@.job_idx order by rundate,runtime asc)
> --if the latest entry in the sysjobhistory is not caused by the job you
> want to record
> --no action
> Begin
> Return
> End
>
> --If the job you want to record add a new entry
> Else
> Begin
> Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
> from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
> asc
> End
> Note:
> 1) This code is just a thinking, not a tested one
> 2) This method will add the workload on your system, especially on a
system
> which has many concurrent jobs.
> I hope this will help you to solve your problem. If you still have
> questions, please feel free to post message here and I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

Retrieving data from SqlDataSource (old school)

Hey,

I need to retrieve info from a database and display it using a repeater control. No problems there! But, I need to add data before displaying it, and I don't mean add data to the database but rather to the repeater control. For eaxample:

I have a simple database containing two fields: [date] and [event]. The repeater will display these events in a monthly view. That is: the repeater will have 31 rows and the events will be displayed next to the day it happens. Now if there's nothing happening a certain day then I need to add that day manually because it will not be bound, right! See my problem?

In other words, how do i loop through a records when using the SqlDataSource?

Thanks,
Bj?rn Andersson

You can use the split function (Described in this forum somewhere on one implementation of it), or create a table with 31 rows in it, and left join it to your query. Then you'll get back 31 rows (or more) every time.

Retrieve data from a SQLDatasource object.

I'm an "old" programmer but new to ASP.NET.

I want to get a value from the SQL Dataset.

What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.

Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.

thanks in advance,

Thommie

if you already have created the dataset then its easy to retrieve and iterate through rows.

for each row as datarow in dataset.tables(table index or name, 0 if its only table).rows

dim str as string=row(column name or index).tostring

Next

or

for i as integer=0 to dataset.tables(0).rows.count

var=dataset.tables(0).rows(i)(columnname or index)

next

hope this helps.

Eric

|||

Do you have a compelling reason to use a DataSet? If you are needing to return a single value, I'd look into using ExecuteScalar() off of the SqlCommand object or using an output parameter and getting your value like that. If you need some more explicit examples, let me know.

|||

Hi,

You can go through the DataSet using foreach, like

foreach(DataRow dr in DataSet.Table[0].Rows)
{
//use dr[0] to get the first column data.
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!