modification in database one by one record

My problem is related to database and data.

I building a project in ASP.NET 2.0 with the help of VWD in which i have certain records and each record has

a unique id with it.Now my problem is that when a user wants to edit a certain record i want the user to enter

a record id for that record. Now if the record id matches any record in the database then that record should be

dislayed in another page with and edit option with it.

Check out this article:

http://asp.net/learn/dataaccess/default.aspx?tabid=63

Hope this helps,

Vivek

MOD Equivalent in SQl Server?

I'm trying to build a query that I'd like only run on records with an odd
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:

> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.

Missing replicated records

Hi gurus there,
I have a puzzling scenario here.
Environment: SQL Server 2K5 (sp2) EE + windows 2K3
I have a publisher server PubSrv_1and two subscriber severs SubSrv_1 and
SubSrv_2, there are two publications on the Publication_1, and
Publication_2, but they all contain the same table as article tbl_A
Publication_1 is a transational replication with Subscription on SubSrv_1
and Publication_2 is a transactional replication with updateable
subscription on SubSrv_2.
Issue: sometimes I find there are missing records which appear in tbl_A on
PubSrv_1 but not in tbl_A on SubSrv_2 while still in tbl_A on SubSrv_1. In
another word, some records in the publication table are NOT replicated to
one subscriber but still replicated to another subscriber.
I am confused how this happens. Could someone please shed some lights here?
TIA
Jeff
Can you check the conflicts log on the subscriber to see if the
missing rows are logged there?
On Jan 9, 3:03 am, "jeff_y" <jeffrey...@.abc.com> wrote:
> Hi gurus there,
> I have a puzzling scenario here.
> Environment: SQL Server 2K5 (sp2) EE + windows 2K3
> I have a publisher server PubSrv_1and two subscriber severs SubSrv_1 and
> SubSrv_2, there are two publications on the Publication_1, and
> Publication_2, but they all contain the same table as article tbl_A
> Publication_1 is a transational replication with Subscription on SubSrv_1
> and Publication_2 is a transactional replication with updateable
> subscription on SubSrv_2.
> Issue: sometimes I find there are missing records which appear in tbl_A on
> PubSrv_1 but not in tbl_A on SubSrv_2 while still in tbl_A on SubSrv_1. In
> another word, some records in the publication table are NOT replicated to
> one subscriber but still replicated to another subscriber.
> I am confused how this happens. Could someone please shed some lights here?
> TIA
> Jeff
|||Hi Hillary,
Thanks for your quick response.
I am not sure where the conflicts log are for the transactional replication.
More information:
I have even put a delete trigger on the problematic table at the subsriber
side to try to log any deletion on the table, but find the trigger has never
been fired off. So in another word, the missing records have never been
deleted as they were in fact never replicated over from the publisher side.
I find the two subscribers have different editions, the SubSrv_2, which has
seen the issues, has edition 3042, while SubSrv_1 has 3161. Do you think
this can cause the issue (I am not convinced though)?
Thanks,
Jeff
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:86aa0601-0caa-49ad-b9a1-d5d3cc732456@.f10g2000hsf.googlegroups.com...
> Can you check the conflicts log on the subscriber to see if the
> missing rows are logged there?
> On Jan 9, 3:03 am, "jeff_y" <jeffrey...@.abc.com> wrote:
>

missing records on subscribing database (transactional replication)

Hi all,
I have a publishing database and a subscriber database on two
different machines. Transactional replication is configured between
them. Everything is straigforward at the start, then after a few days
I got reports that the same tables on both databases are not in synch,
and some tables in the subscriber database has missing rows.
Has anyone experienced this? I realize that it is possible to delete
rows on the subscriber database, but other than that, is it possible
for the transactional replication to actually fail to copy some rows
from the publishing database?
There are no filters set, btw.
Thanks!
Aramid
If you don't have any errors in the replication engine, then, no it will not
fail to send changes. You would have to interfere with it in some way such
as within the distribution database. I would suggest doing some auditing at
your subscriber and see if there is a process which is making changes there.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Aramid" <aramid@.hotmail.com> wrote in message
news:8fueu1to6vadlh5dd4q1be9509cteqnbdk@.4ax.com...
> Hi all,
> I have a publishing database and a subscriber database on two
> different machines. Transactional replication is configured between
> them. Everything is straigforward at the start, then after a few days
> I got reports that the same tables on both databases are not in synch,
> and some tables in the subscriber database has missing rows.
> Has anyone experienced this? I realize that it is possible to delete
> rows on the subscriber database, but other than that, is it possible
> for the transactional replication to actually fail to copy some rows
> from the publishing database?
> There are no filters set, btw.
> Thanks!
> Aramid
|||Aramid,
these are 2 separate possibilities that you're describing. In one, the rows
are deleted from the subscriber, while in the other, some inserts are not
sent from the publisher to the subscriber. For the first, this is unlikely
to occur through the replication engine, so I'd go along with Mike's
suggestion and use Lumigent's LogExplorer to investigate. This is also
possible to occur naturally through the replication, if you are replicating
stored procedure execution. In the other case, I'd use sp_browsereplcmds to
see if the commands are still waiting to be sent down.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Missing records in report

Hey, I don't know if anyone here will be able to help with this, but I don't want to have to spend money on a "support call" if I don't have to.

Anyhow, I am using reporting services to create letters for new customers. I am basing who gets a letter off of a status field associated with each record, and which location that customer is at. Here's where the problem lies: Every other day, each of the managers at each of the location will have a new customer "go missing". What I mean by "go missing" is that it will appear in the "data" section of the report designer, and using query analyzer it will come up in the results, also. But, it will not print out in the report! I cannot find anything wrong with the record, and, as I said, it shows up in the dataset, but just not when I go to view the report. Does anyone know if this is a version issue (ie, is there a patch or SP out to fix this), or if it's just a programmers error? Any information would be greatly appreciated!!
Thanks!do you have any filters against the recordset ? technically you should be able to see the same resultset you get in data tab and preview sections.|||I have some filtering in the SQL statement that I built for the recordset, but I do not have any other filters. I know that technically I should be able to see the same resultset in the data and in the preview, but it does not. Every once in a while, it skips a record. There really is no set reason for the record not to appear, it just does not all of them all of the time. It seems like it doesn't want to parse a random record when it makes the report.|||Well. Are u doing some grouping in thr report. Check it out thoroghly.

Cannot guess but it could be the case to grpup the records in to one if you have same type of rows exist...|||I have never seen recrods just go dropping out, and I have authored 100+ SRS reports. So I agree that it is probably a) some kind of grouping issue, or b) some kind if issue with the sql string you are building. I'd check both carefully.|||Thanks everyone for replying. Anyhow, to answer some of the questions, I am not using grouping, other than that there is a page for each record. The SQL statement is very straightforward, nothing special, though I do have a few joins and one or two case statements, but nothing outlandish. I do a IsNull on all of the fields that might have null values in them so that this isn't a dbnull issue...|||Well it could be joining problem with in tables. I think there is no problem with reports services. You should better check out the joins. If you are using inner joins it may cuase of dropping soemrecords.

If the record is null may be it hide the whole record. What all other thinks about that
???|||Hmmm..... Well, I don't think it's the joins, as the record shows up in the data tab of the report designer. But, it could very well be that I am not doing IsNulls on every field...... I had thought that I had done that, but I realized there are a few that I didn't do. I'll check that and see if it works...|||Ok, so that all of you don't think that I am insane or anything, I have made some screenshots of what's happening.

First

http://www.bojesphob.com/issue1.jpg
http://www.bojesphob.com/issue2.jpg
http://www.bojesphob.com/issue3.jpg

If this forum doesn't allow links to show up, just copy and paste. The first one is the data view. In this screenshot, you will see a number that is highlighted, 24339. It is before 24343 and after 24337. The second is the 9th record (of 21) (24337) and the third is the 10th record (24343) as they show up in the report. I blurred out the personally identifiable information to protect the customers who show up. As I understand it, if they show up in the data block, they should show up in the report. If you notice the query at the top of "issue1.jpg", there are no nulls coming through at all (SerialNumber will never be null.). Please let me know if you've seen something like this before!!!|||Holy crap I feel retarded.....

Turns out that I didn't understand the "detail/header/footer" concept in Reporting services as well as i had thought that I did...... I had inserted the design work into the footer, and for some reason it was condesing a few of the records.....

I moved the design up into the detail portion and the records come through fine. Thanks for all of the help, everyone....|||yes there are a lot of issues with the footer/body/header sections..we cant use tables in header/footer we cant use dataset fields in the header/fotoer..etc.. RS is not a full fledged reporting product. ..still plenty of work needs to be done.

Missing Records in Linked Table

I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
an MDB file. The db is used by about 30 users on a LAN, and an additional 10
or so on a WAN.
Recently, one of the WAN users complained of intermittently not being able
to find certain customers in the customers table (contains around 40,000
records). There are specific "problem records" of customers that can't be
found, and they all start with the letter "F" (the table has a numeric
customer ID as its primary key).
For these three or four customers that have been identified so far as
problem records, the customer exists in the customer list (a drop-down based
on a front-end table), but, when trying to go to the record, are told that
the record's not found.
Similarly, when the user searches for one of these problem records using the
custom search function, which searches the linked tables, the search
function says the record cannot be found.
Note that this is an intermittent problem with this single WAN user, and no
other users (on WAN or LAN are having this problem).
Any ideas about what might be causing it, or what can be done to this single
user's machine to alleviate the problem?
Thanks!
NeilAre they accessing the table directly or through a View that might be
limiting records?
--
KARL DEWEY
Build a little - Test a little
"Neil" wrote:

> I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
> an MDB file. The db is used by about 30 users on a LAN, and an additional
10
> or so on a WAN.
> Recently, one of the WAN users complained of intermittently not being able
> to find certain customers in the customers table (contains around 40,000
> records). There are specific "problem records" of customers that can't be
> found, and they all start with the letter "F" (the table has a numeric
> customer ID as its primary key).
> For these three or four customers that have been identified so far as
> problem records, the customer exists in the customer list (a drop-down bas
ed
> on a front-end table), but, when trying to go to the record, are told that
> the record's not found.
> Similarly, when the user searches for one of these problem records using t
he
> custom search function, which searches the linked tables, the search
> function says the record cannot be found.
> Note that this is an intermittent problem with this single WAN user, and n
o
> other users (on WAN or LAN are having this problem).
> Any ideas about what might be causing it, or what can be done to this sing
le
> user's machine to alleviate the problem?
> Thanks!
> Neil
>
>|||No, directly, the entire table. Also, note that the functionality is fine,
and on one (and now I found out two) computers the data for those records is
intermittently not there. But on other computers it's there. Nothing in the
functionality that would be limiting it.
"KARL DEWEY" <KARLDEWEY@.discussions.microsoft.com> wrote in message
news:3D9F901F-12EB-479D-9981-AE808A4E76EB@.microsoft.com...[vbcol=seagreen]
> Are they accessing the table directly or through a View that might be
> limiting records?
> --
> KARL DEWEY
> Build a little - Test a little
>
> "Neil" wrote:
>|||hi Neil,
Neil wrote:
> Note that this is an intermittent problem with this single WAN user, and n
o
> other users (on WAN or LAN are having this problem).
Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
the client.
mfG
--> stefan <--|||Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
(msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
as the other users. Are there any other files you suggest I check?
The fact that these records all have names starting with "F" leads me to
wonder if it's a paging issue, even though the PK is the customer ID, not
name, though there is an index on last name.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:uEy7$tM2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> hi Neil,
> Neil wrote:
> Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
> the client.
>
> mfG
> --> stefan <--|||hi Neil,
Neil wrote:
> Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), J
et
> (msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the sam
e
> as the other users. Are there any other files you suggest I check?
No.

> The fact that these records all have names starting with "F" leads me to
> wonder if it's a paging issue, even though the PK is the customer ID, not
> name, though there is an index on last name.
Can you browse the linked table on that specific client?
mfG
--> stefan <--|||
>
> Can you browse the linked table on that specific client?
>
> mfG
Good question. I'll have to check on that tomorrow.
BTW, what's mfG?

> --> stefan <--|||hi Neil,
Neil wrote:
> BTW, what's mfG?
"mit freundlichen Gren" means "with kind regards"
mfG
--> stefan <--

Missing Records in Linked Table

I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
an MDB file. The db is used by about 30 users on a LAN, and an additional 10
or so on a WAN.
Recently, one of the WAN users complained of intermittently not being able
to find certain customers in the customers table (contains around 40,000
records). There are specific "problem records" of customers that can't be
found, and they all start with the letter "F" (the table has a numeric
customer ID as its primary key).
For these three or four customers that have been identified so far as
problem records, the customer exists in the customer list (a drop-down based
on a front-end table), but, when trying to go to the record, are told that
the record's not found.
Similarly, when the user searches for one of these problem records using the
custom search function, which searches the linked tables, the search
function says the record cannot be found.
Note that this is an intermittent problem with this single WAN user, and no
other users (on WAN or LAN are having this problem).
Any ideas about what might be causing it, or what can be done to this single
user's machine to alleviate the problem?
Thanks!
Neil
Are they accessing the table directly or through a View that might be
limiting records?
KARL DEWEY
Build a little - Test a little
"Neil" wrote:

> I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
> an MDB file. The db is used by about 30 users on a LAN, and an additional 10
> or so on a WAN.
> Recently, one of the WAN users complained of intermittently not being able
> to find certain customers in the customers table (contains around 40,000
> records). There are specific "problem records" of customers that can't be
> found, and they all start with the letter "F" (the table has a numeric
> customer ID as its primary key).
> For these three or four customers that have been identified so far as
> problem records, the customer exists in the customer list (a drop-down based
> on a front-end table), but, when trying to go to the record, are told that
> the record's not found.
> Similarly, when the user searches for one of these problem records using the
> custom search function, which searches the linked tables, the search
> function says the record cannot be found.
> Note that this is an intermittent problem with this single WAN user, and no
> other users (on WAN or LAN are having this problem).
> Any ideas about what might be causing it, or what can be done to this single
> user's machine to alleviate the problem?
> Thanks!
> Neil
>
>
|||No, directly, the entire table. Also, note that the functionality is fine,
and on one (and now I found out two) computers the data for those records is
intermittently not there. But on other computers it's there. Nothing in the
functionality that would be limiting it.
"KARL DEWEY" <KARLDEWEY@.discussions.microsoft.com> wrote in message
news:3D9F901F-12EB-479D-9981-AE808A4E76EB@.microsoft.com...[vbcol=seagreen]
> Are they accessing the table directly or through a View that might be
> limiting records?
> --
> KARL DEWEY
> Build a little - Test a little
>
> "Neil" wrote:
|||hi Neil,
Neil wrote:
> Note that this is an intermittent problem with this single WAN user, and no
> other users (on WAN or LAN are having this problem).
Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
the client.
mfG
--> stefan <--
|||Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
(msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
as the other users. Are there any other files you suggest I check?
The fact that these records all have names starting with "F" leads me to
wonder if it's a paging issue, even though the PK is the customer ID, not
name, though there is an index on last name.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:uEy7$tM2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> hi Neil,
> Neil wrote:
> Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
> the client.
>
> mfG
> --> stefan <--
|||hi Neil,
Neil wrote:
> Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
> (msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
> as the other users. Are there any other files you suggest I check?
No.

> The fact that these records all have names starting with "F" leads me to
> wonder if it's a paging issue, even though the PK is the customer ID, not
> name, though there is an index on last name.
Can you browse the linked table on that specific client?
mfG
--> stefan <--
|||
> Can you browse the linked table on that specific client?
>
> mfG
Good question. I'll have to check on that tomorrow.
BTW, what's mfG?

> --> stefan <--
|||hi Neil,
Neil wrote:
> BTW, what's mfG?
"mit freundlichen Gren" means "with kind regards"
mfG
--> stefan <--

Missing records after COMMIT TRAN

Hi

Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity??

I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.

Thanks,
Inon.http://www.dbforums.com/showthread.php?t=1196943|||The process or the environment are irrelevant, although the question is general the problem is very specific, people may have encountered the same symptom with a totally different situation.
I want to hear all the reasons that you can think of to when a transaction is committed and the record that was inserted in the transaction is missing.
One example is when a trigger is responsible to delete the record or prevent it from being insert like an INSTEAD OF trigger, but there are no triggers on the table, so what else can cause it??

Thanks,
Inon.|||a bug in your code.|||a bug in your code.

ok... thanks!! what else?

(First of all you can see that I wrote "UNDER HEAVY LOAD", meaning it's working fine most of the time and only under heavy load (of the same process) SOME of the records are missing, second, A bug would cause a rollback, and I will also add that @.@.IDENTITY returns the correct new identity of the record which is SOMETIMES missing and cause the gap in the table)

Inon.|||have you ran a trace yet and stepped through it yet?|||have you ran a trace yet and stepped through it yet?

No, the thing is, it's not my code, I'm trying to help someone, he already tried some debug prints and debug queries (inserting some information in the middle of the code), it seems that it's going the way it should, remember that it's working most of the time, what makes me believe it's not the code, but I could be wrong, I know that when it's a question of the computer or the human, the human is usually the blame.

I don't see how can a trace assist? Please advise.

Addition, the server is rather slow and with only 512 MB of RAM, the HD has about 3 GB of free space, but anyway I would expect to get some error or some message… anything, but no, all is going well yet records are missing and a gap is formed.

Thanks,
Inon.|||a properly setup profiler trace can you show you many things. sometimes things do not quite fire off like you expect them to. For this I would probably use the default columns and and use only the T-SQL and Stored procedure event classes and and all of the events that end with :completed.

It takes a little reading and some practice to interpert what you are looking at but Profiler helps me diagnose stuff everyday.|||You’re right, I do use trace sometimes, but I hoped that maybe someone who has already experienced this problem could pinpoint the cause and save me some time.

Addition, BTW I have a solution for this, I just do things a bit differently, but its still curios me, strange...

Thanks,
Inon.|||OK ... here's a few.

1. The row was deleted by a subsequent transaction

2. Even though the commit was issued, the transaction was rolled back (more specifically where a two-phase commit is required.)

3. The commit was issued, but the transaction timed out before completion (web based).

4. It fell off into the bit bucket. ;)

Missing records

I am trying to find missing Checks from Check Table (I know there are no
tables).
Here is a sample table (oops did it again) :):
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'TestTable')
DROP table TestTable
CREATE TABLE [dbo].[TestTable] (
[SeqNumber] [int] NULL ,
[AName] [varchar] (30) NULL
) ON [PRIMARY]
GO
Insert TestTable Values (1,'Joe')
Insert TestTable Values (2,'Mary')
Insert TestTable Values (3,'Frank')
Insert TestTable Values (5,'Larry')
Insert TestTable Values (6,'Jerry')
Insert TestTable Values (7,'Greg')
Insert TestTable Values (10,'Ron')
Insert TestTable Values (11,'Pat')
Insert TestTable Values (12,'Beth')
Insert TestTable Values (13,'Lynn')
Insert TestTable Values (20,'Sharon')
go
Is there an easy way to do a select and come back with the missing sequences
(in this case - 4,8,9,14,15,16,17,18,19).
Thanks,
TomUse a table consisting of all the possible check numbers:
SELECT N.num
FROM numbers AS N
LEFT JOIN testtable AS T
ON N.num = T.seqnumber
WHERE T.seqnumber IS NULL
AND N.num BETWEEN 1 AND 20 ;
Or, if you prefer to see ranges of numbers:
SELECT T1.seqnumber+1 AS missing_from,
MIN(T2.seqnumber)-1 AS missing_to
FROM testtable AS T1
JOIN testtable AS T2
ON T1.seqnumber < T2.seqnumber
GROUP BY T1.seqnumber
HAVING T1.seqnumber < MIN(T2.seqnumber)-1 ;
David Portas
SQL Server MVP
--

Missing History for Jobs (msdb.dbo.sysjobhistory)

New server running our SQL... upgrade from Advanced 2000 to Enterprise
2003... our SQL Instance normally records all history (View History from Job
s
in SQL Agent). For some reason, currently unknown, history disappears every
so often. In this case, all history from 11/24 going backward is gone. All
history from 11/25 through today is here. This happened previously. Is
there any logical explanation for this? I'd settle for a server reboot, but
to my knowledge, this did not happen either.
Also there is a disk space problem on one of the drives. The msdb does not
touch that drive so, in my way of thinking the drive space issue cannot be
connected to the missing history (sysjobhistory). Is this logical to assume
?
An additional thought comes to mind. The jobs were imported from one server
to another. The owner was either the service account (with plenty of
permission) or the sa account, but the jobs were recreated from script using
the sa login. Could this have an effect on the job history?
--
Regards,
JamieAlso I believe this is happenning each week. Saturday night or Sunday
morning to be sure.
--
Regards,
Jamie
"thejamie" wrote:

> New server running our SQL... upgrade from Advanced 2000 to Enterprise
> 2003... our SQL Instance normally records all history (View History from J
obs
> in SQL Agent). For some reason, currently unknown, history disappears eve
ry
> so often. In this case, all history from 11/24 going backward is gone. A
ll
> history from 11/25 through today is here. This happened previously. Is
> there any logical explanation for this? I'd settle for a server reboot, b
ut
> to my knowledge, this did not happen either.
> Also there is a disk space problem on one of the drives. The msdb does no
t
> touch that drive so, in my way of thinking the drive space issue cannot be
> connected to the missing history (sysjobhistory). Is this logical to assu
me?
> An additional thought comes to mind. The jobs were imported from one serv
er
> to another. The owner was either the service account (with plenty of
> permission) or the sa account, but the jobs were recreated from script usi
ng
> the sa login. Could this have an effect on the job history?
> --
> Regards,
> Jamie|||thejamie,
Recreating the jobs would cause them to have a new job_id, so any history
records that might still exist in sysjobhistory will no longer join to
existing jobs. (If you still have your previous server and can figure out
the old job_id to new job_id mapping, you could update rowsn in your
sysjobhistory and all would be well.)
Also, make sure that your SQL Agent Properties - History settings are the
same on you new server as your old, or you will find SQL Agent (by default)
deleting history fairly frequently.
RLF
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:C078BDDA-ED20-4F42-AD0E-2FF1AED4EE7F@.microsoft.com...
> New server running our SQL... upgrade from Advanced 2000 to Enterprise
> 2003... our SQL Instance normally records all history (View History from
> Jobs
> in SQL Agent). For some reason, currently unknown, history disappears
> every
> so often. In this case, all history from 11/24 going backward is gone.
> All
> history from 11/25 through today is here. This happened previously. Is
> there any logical explanation for this? I'd settle for a server reboot,
> but
> to my knowledge, this did not happen either.
> Also there is a disk space problem on one of the drives. The msdb does
> not
> touch that drive so, in my way of thinking the drive space issue cannot be
> connected to the missing history (sysjobhistory). Is this logical to
> assume?
> An additional thought comes to mind. The jobs were imported from one
> server
> to another. The owner was either the service account (with plenty of
> permission) or the sa account, but the jobs were recreated from script
> using
> the sa login. Could this have an effect on the job history?
> --
> Regards,
> Jamie|||Thanks for the reply Russell. Actually, I moved the data over on 11/4. I
didn't expect any history to go back before the move. What bothers me is
that on Saturday I had history going back to 11/4. On Sunday, it extended
only from 11/25. Everything prior to 11/24 is wiped. It still exists in
sysjobhistory. I am not the sole DBA so it is possible that someone other
than myself wiped it, but I have to presume that is not the case. It isn't
vital. I can filter the log for a given job to get the history. The histor
y
was set to 1000 - I have lots of disk space at the moment so I moved it to
10000 and from 100 to 1000 on the rows. Still a mystery. I suspect that
the disk space on the one drive being low may have something to do with the
history disappearing.
--
Regards,
Jamie
"Russell Fields" wrote:

> thejamie,
> Recreating the jobs would cause them to have a new job_id, so any history
> records that might still exist in sysjobhistory will no longer join to
> existing jobs. (If you still have your previous server and can figure out
> the old job_id to new job_id mapping, you could update rowsn in your
> sysjobhistory and all would be well.)
> Also, make sure that your SQL Agent Properties - History settings are the
> same on you new server as your old, or you will find SQL Agent (by default
)
> deleting history fairly frequently.
> RLF
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:C078BDDA-ED20-4F42-AD0E-2FF1AED4EE7F@.microsoft.com...
>
>|||Jamie,
If the rows are still in sysjobhistory then they should connect to the jobs.
If the jobs will not show history further back than 11/24, then did someone
redeploy the jobs again, such that they got new job_ids?
RLF
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A8C507C7-D1B6-46FF-AD2F-9E9EBCF829F5@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply Russell. Actually, I moved the data over on 11/4. I
> didn't expect any history to go back before the move. What bothers me is
> that on Saturday I had history going back to 11/4. On Sunday, it extended
> only from 11/25. Everything prior to 11/24 is wiped. It still exists in
> sysjobhistory. I am not the sole DBA so it is possible that someone other
> than myself wiped it, but I have to presume that is not the case. It
> isn't
> vital. I can filter the log for a given job to get the history. The
> history
> was set to 1000 - I have lots of disk space at the moment so I moved it to
> 10000 and from 100 to 1000 on the rows. Still a mystery. I suspect that
> the disk space on the one drive being low may have something to do with
> the
> history disappearing.
> --
> Regards,
> Jamie
>
> "Russell Fields" wrote:
>|||Sounds very possible. Thanks.
--
Regards,
Jamie
"Russell Fields" wrote:

> Jamie,
> If the rows are still in sysjobhistory then they should connect to the job
s.
> If the jobs will not show history further back than 11/24, then did someon
e
> redeploy the jobs again, such that they got new job_ids?
> RLF
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A8C507C7-D1B6-46FF-AD2F-9E9EBCF829F5@.microsoft.com...
>
>