Query giving variable results at different runs

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

SQL Server Development (2000)

Query giving variable results at different runs

Author

Topic

inadilemma
Starting Member

3 Posts

Posted - 2008-11-05 : 11:26:29

Hi,

We are developing a back office management software using sql server 2000 as the back end database. I am getting some annoying problems with an specific query which gives different results in different execution when executed through the query analyzer or enterprise manager. Though in most of the runs it gives same result but some times the result comes out different even though the database remains untouched and unchanged :( Is it a bug or something ?

The query is on a table named Stock:

SELECT SUM(Quantity) AS Expr1 FROM stock s WHERE (sDate IN (SELECT MAX(p.SDate) FROM Stock AS p WHERE p.SDate <= '11/5/2008' AND p.ExchangeName = s.ExchangeName AND p.InstrumentId = s.InstrumentId AND p.InvestorId = s.InvestorId AND p.BranchId = s.BranchId)) AND (InvestorId = '00001')

The schema definition of Stock table is pasted here also if anyone's interested:
CREATE TABLE [dbo].[stock](
[Id] [bigint] NOT NULL,
[InstrumentId] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InvestorId] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[sDate] [datetime] NULL,
[Quantity] [bigint] NOT NULL,
[BuyRate] [float] NULL,
[BranchId] [int] NULL,
[PayRecShare] [bigint] NULL,
[Exchangename] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MatShare] [bigint] NOT NULL,
[MatPledge] [bigint] NULL,
[DematPledge] [bigint] NULL,
CONSTRAINT [PK_STOCK] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

I MaY NoT AwAkEn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:29:27

can you give an example of how the results can vary?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 11:29:52

What datetype is p.SDate ?
If you have different locale in QA and EM the string '11/5/2008' will be either treated as "Nov 5 2008" or "May 5 2008".

Take this as a learning lesson to
1) Always use proper datatype to store dates (SMALLDATETIME or DATETIME)
2) Always put constant dates in unambigous format such as ANSI "20080511" May 11 or "20081105" Nov 5.

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

inadilemma
Starting Member

3 Posts

Posted - 2008-11-05 : 11:44:45

quote:
Originally posted by visakh16

can you give an example of how the results can vary?

Most of the times the result is 800
But sometimes it varies within a wide range like 1300 ,78200 etc.

And for Peso the datatype is datetime and the locale is mm-dd-yyyy both in EM and QA.

I MaY NoT AwAkEn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:47:28

and you're running this alone without merging with any other batch?

inadilemma
Starting Member

3 Posts

Posted - 2008-11-05 : 12:02:18

quote:
Originally posted by visakh16

and you're running this alone without merging with any other batch?

yes. Just this stand alone query

I MaY NoT AwAkEn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources