Timestamp column is not working properly

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 2005 Forums

Transact-SQL (2005)

Timestamp column is not working properly

Author

Topic

anilkumar.forum
Starting Member

5 Posts

Posted - 2011-03-27 : 04:57:33

Hello Everyone,

I am facing a very strange problem. I m using a timestamp column in my database to handle concurency. As per my knowledge if you declare any column as timestamp, it would change its value whenever there is any modification to that row. This is fine.

But in my table the timestamp value is returning diferent values even for SELECT statement also. So when i am trying to update any row, values are not matching. i mean in my where condition i am checking for timestamp value.

Can anyone please tell me why the value for the timestamp column is changing for SELECT statement.

Any help on this would be higly appreciated.

Thanks in advance

Anil Kumar

With Best Regards
Anil Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-27 : 05:18:33

Are you using a calculated column in the table?
Do you have a trigger on the column?

Post your actual DDL so we have a chance to reproduce the behaviour.

N 56°04'39.26"
E 12°55'05.63"

anilkumar.forum
Starting Member

5 Posts

Posted - 2011-03-27 : 13:31:31

Following is the script of that table. and my select statement is

SELECT chgTimeStamp from Event_Master

And this behaviour is happening intermittently. Sometimes the value remains the same but sometimes for the same query it changes.

USE [PSQL24]
GO

/****** Object: Table [dbo].[Event_Master] Script Date: 03/27/2011 22:54:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Event_Master](
[Poll_Id] [int] IDENTITY(1,1) NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
[EventPath] [nvarchar](max) NOT NULL,
[Enable] [bit] NOT NULL,
[Retries] [smallint] NULL,
[LinkPrevious] [int] NULL,
[LinkNext] [int] NULL,
[LastResult] [nvarchar](50) NULL,
[AfterSuccess] [bit] NULL,
[ExecuteEvery] [int] NULL,
[ExecuteInterval] [nvarchar](50) NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[RepeatForever] [bit] NOT NULL,
[Type] [text] NULL,
[Parameter] [text] NULL,
[Options] [text] NULL,
[LastUpdate] [text] NULL,
[Description] [text] NULL,
[PollAfterSiteCtrler] [bit] NULL,
[ExeDate] [datetime] NOT NULL,
[Siteid] [nvarchar](max) NULL,
[ImportPollData] [bit] NOT NULL,
[CardUpdate] [bit] NOT NULL,
[PollTankGauge] [bit] NOT NULL,
[ChgTimeStamp] [timestamp] NULL,
[Reportparameters] [varchar](400) NULL,
CONSTRAINT [PK_Event_Master] PRIMARY KEY CLUSTERED
(
[Poll_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Event_Master] ADD CONSTRAINT [DF_Event_Master_ImportPollData] DEFAULT ((0)) FOR [ImportPollData]
GO

ALTER TABLE [dbo].[Event_Master] ADD CONSTRAINT [DF_Event_Master_CardUpdate] DEFAULT ((0)) FOR [CardUpdate]
GO

ALTER TABLE [dbo].[Event_Master] ADD CONSTRAINT [DF_Event_Master_PollTankGauge] DEFAULT ((0)) FOR [PollTankGauge]
GO

quote:
Originally posted by Peso

Are you using a calculated column in the table?
Do you have a trigger on the column?

Post your actual DDL so we have a chance to reproduce the behaviour.

N 56°04'39.26"
E 12°55'05.63"

With Best Regards
Anil Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-27 : 13:58:59

How are you noticing the changes?

N 56°04'39.26"
E 12°55'05.63"

anilkumar.forum
Starting Member

5 Posts

Posted - 2011-03-28 : 00:31:28

I ran the above query for 6 times and i got the following results

0x0000000000004506

0x0000000000004508

0x0000000000004522

0x000000000000452A

0x0000000000004530

0x000000000000453A

quote:
Originally posted by Peso

How are you noticing the changes?

N 56°04'39.26"
E 12°55'05.63"

With Best Regards
Anil Kumar

matty
Posting Yak Master

161 Posts

Posted - 2011-03-29 : 03:21:39

Did you miss the WHERE clause?
SELECT chgTimeStamp from Event_Master WHERE ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources