Most Weired Problem. - 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 2000 Forums

Transact-SQL (2000)

Most Weired Problem.

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 07:16:39

Hi All,

Does any one of your ever had face this kind of problem then please let me know what you did for Resolving the Issue.

TableName : CvDataBank
Primary Key : CanID(Int,Identity)

This Table is used for Storing the CV Details of a Candidates.

Sometime, when the users are trying to input the CV details, the Record at CanID =9 is deleted and the new record is inserted at the CanID =9

and it doesnt happen on the regular basis.. once in 15 days it happens..

There is a audit Trail on that table, but in the audit tablethe normal Identity value is saved.

I dont know how this happening..??

Any pointers will be of great help

Thanks

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 07:20:32

Strange!

I assume you have triggers on the table for auditing purpose...Have you checked them for any foul play?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 07:27:42

Yeah, Man in the Auditing Table, the normal Identity Value is saved. And more strange is that datetime in the CVDatabank table and in the Audit Table is also same.

First i thought someone would be trying to play around with the Table, so i went an changed the password.

Then I thought may be due some internal indexing or somthing this thing must be happening, so I migrated the Table to the Temp Table and then drop the table and recreated and imported all the data again.

but then after 5-6 days, same issue raised.

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 07:32:45

Is this all started recently?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 07:35:24

I implemented the system there in Sept 2006, and this issue was raised in nov end.. and then i was trying all possible solutions.. with no lucks.. :-(

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 07:42:12

Can you post trigger code here (if possible)?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 07:53:58

yeah.. i wrote this code just to trap that incident...



Create Trigger InsCVDetail On CvDatabank
For Insert,Update 
As 
Begin 
	If (Update(Code) Or Update(CanID))
	Begin 
		Insert CvDatabank_bk(NewCanId,NewCode)
		Select CanID,Code From Inserted
		if Exists(Select * From Deleted)
		Begin 
			Insert CvDatabank_bk(OldCanId,OldCode)
			Select CanId,Code From Deleted 
		End 
	End 
End

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2006-12-26 : 13:24:46

I don't suppose you are using @@IDENTITY some where along the line, instead of SCOPE_IDENTITY(), are you?

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-27 : 00:45:41

quote:
Originally posted by Kristen

I don't suppose you are using @@IDENTITY some where along the line, instead of SCOPE_IDENTITY(), are you?

Kristen

No I am not using that, its just an Identity Column.

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-28 : 17:50:28

Are you telling us that an IDENTITY column resets it's seed?
Who is running DBCC CHECKIDENT?

Peter Larsson
Helsingborg, Sweden

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2006-12-29 : 11:30:27

turn on profiler to capture all events against that table. seems likely u have an update instead of insert somewhere in client code, but now trigger ought to catch that...

if someone is messing with the data, they will disable/re-enable trigger. profiler will show u whats going on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources