resetting id values

hi guys i was wondering if anyone could help me, i have a table with a field called id that did have numbers 1,2,3,4,5,6,7,8 and so on! but after some tinkering i have removed a few value and added more so i now have 1, 4,8,19,20 and so on!

i was wondering if i can run a query to update those value and return them to 1,2,3,4,5,6,7,8 and so on?

Cheers

Tupps

This article might help

http://www.juliankuiters.id.au/article.php/sql2000-reset-identity

|||

sorted thanks!! couldnt get an answer! but thats cos i wasnt using the correct term cheers!!

Tupps

RESEEDING & INCREMENT VALUES

hi,
I am maintaining around 180 tables, out of these-125 have identity values
I need set new SEED value and new increment values for these 125 tables
Is there any easier way to do this?
Thanks,
Soura
you can do something like this
SELECT 'DBCC CHECKIDENT ('+TABLE_NAME +',RESEED , 50 )'
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME) IS NOT NULL
This will reseed the next identity value to 50.
I'm not sure how to change the increment.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:352CE298-4960-43AB-8C9B-A098FF4CAB6F@.microsoft.com...
> hi,
> I am maintaining around 180 tables, out of these-125 have identity
values
> I need set new SEED value and new increment values for these 125 tables
> Is there any easier way to do this?
> Thanks,
> Soura

Require an SQL

Hi all
From one of the table having the values like below, I need
to take only top 3 records for each EquipmentID.
(I don't prefer to use a simple stored procedure using
temp table) Expecting an SQL Statement if possible.
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BELine 1 8005 32
L4-BELine 1 1013 28
L4-BELine 1 3054 24
L4-BELine 1 5005 24
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA01 1 33 24
L4-DA01 1 74 18
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
L4-DA02 1 203 458
L4-DA02 1 74 346
So the Result expected is as follows:
EquipmentID AreaID AlarmID Duration
-- -- -- --
L4-BELine 1 8241 17484
L4-BELine 1 6038 62
L4-BELine 1 2042 52
L4-BUF1 1 1 17340
L4-BUF1 1 2 2056
L4-BUF1 1 8 856
L4-DA01 1 18 6196
L4-DA01 1 1 4924
L4-DA01 1 200 4390
L4-DA02 1 80 3920
L4-DA02 1 73 2858
L4-DA02 1 18 2214
To try I am here with giving the Schema and data
CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
[EquipmentID] [nvarchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AreaID] [int] NOT NULL ,
[AlarmID] [int] NULL ,
[Duration] [int] NULL
) ON [PRIMARY]
GO
Data:
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8241,17484)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
[AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
Thanks in advance.
AnandYou probably want something like this
SELECT EquipmentID, AreaID, AlarmID, Duration
FROM tempRTM_EQM_Top10Alarms A
WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM tempRTM_EQM_Top10Alarms B WHERE
B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
ORDER BY EquipmentID, AlarmID
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
> Hi all
> From one of the table having the values like below, I need
> to take only top 3 records for each EquipmentID.
> (I don't prefer to use a simple stored procedure using
> temp table) Expecting an SQL Statement if possible.
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BELine 1 8005 32
> L4-BELine 1 1013 28
> L4-BELine 1 3054 24
> L4-BELine 1 5005 24
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA01 1 33 24
> L4-DA01 1 74 18
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
> L4-DA02 1 203 458
> L4-DA02 1 74 346
> So the Result expected is as follows:
> EquipmentID AreaID AlarmID Duration
> -- -- -- --
> L4-BELine 1 8241 17484
> L4-BELine 1 6038 62
> L4-BELine 1 2042 52
> L4-BUF1 1 1 17340
> L4-BUF1 1 2 2056
> L4-BUF1 1 8 856
> L4-DA01 1 18 6196
> L4-DA01 1 1 4924
> L4-DA01 1 200 4390
> L4-DA02 1 80 3920
> L4-DA02 1 73 2858
> L4-DA02 1 18 2214
>
> To try I am here with giving the Schema and data
> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
> [EquipmentID] [nvarchar] (12) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [AreaID] [int] NOT NULL ,
> [AlarmID] [int] NULL ,
> [Duration] [int] NULL
> ) ON [PRIMARY]
> GO
> Data:
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-
> BELine',1,8241,17484)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,6038,62)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,2042,52)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,8005,32)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,1013,28)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,3054,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BELine',1,5005,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,1,17340)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,18,6196)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,200,4390)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,80,3920)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,73,2858)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,18,2214)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,203,458)
> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>
> Thanks in advance.
> Anand|||Hi Allan
This is what I want. Thanks a lot.
Anand.
>--Original Message--
>You probably want something like this
>
>SELECT EquipmentID, AreaID, AlarmID, Duration
>FROM tempRTM_EQM_Top10Alarms A
>WHERE AlarmID IN (SELECT TOP 3 AlarmID FROM
tempRTM_EQM_Top10Alarms B WHERE
>B.EquipmentID = A.EquipmentID ORDER BY AlarmID DESC)
>ORDER BY EquipmentID, AlarmID
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Anand" <gurusanand1@.sifymail.com> wrote in message
>news:09be01c366f5$ffb8fd00$a501280a@.phx.gbl...
>> Hi all
>> From one of the table having the values like below, I
need
>> to take only top 3 records for each EquipmentID.
>> (I don't prefer to use a simple stored procedure using
>> temp table) Expecting an SQL Statement if possible.
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BELine 1 8005 32
>> L4-BELine 1 1013 28
>> L4-BELine 1 3054 24
>> L4-BELine 1 5005 24
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA01 1 33 24
>> L4-DA01 1 74 18
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>> L4-DA02 1 203 458
>> L4-DA02 1 74 346
>> So the Result expected is as follows:
>> EquipmentID AreaID AlarmID Duration
>> -- -- -- --
>> L4-BELine 1 8241 17484
>> L4-BELine 1 6038 62
>> L4-BELine 1 2042 52
>> L4-BUF1 1 1 17340
>> L4-BUF1 1 2 2056
>> L4-BUF1 1 8 856
>> L4-DA01 1 18 6196
>> L4-DA01 1 1 4924
>> L4-DA01 1 200 4390
>> L4-DA02 1 80 3920
>> L4-DA02 1 73 2858
>> L4-DA02 1 18 2214
>>
>> To try I am here with giving the Schema and data
>> CREATE TABLE [dbo].[tempRTM_EQM_Top10Alarms] (
>> [EquipmentID] [nvarchar] (12) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [AreaID] [int] NOT NULL ,
>> [AlarmID] [int] NULL ,
>> [Duration] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> Data:
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
>> BELine',1,8241,17484)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,6038,62)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,2042,52)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,8005,32)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,1013,28)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,3054,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BELine',1,5005,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
BUF1',1,1,17340)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,2,2056)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-BUF1',1,8,856)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,18,6196)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,1,4924)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA01',1,200,4390)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,33,24)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA01',1,74,18)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,80,3920)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,73,2858)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,18,2214)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-
DA02',1,203,458)
>> INSERT INTO [temprtm_eqm_Top10Alarms] ([EquipmentID],
>> [AreaID],[AlarmID],[Duration])VALUES('L4-DA02',1,74,346)
>>
>> Thanks in advance.
>> Anand
>
>.
>

RequestType field in the ExecutionLog table

Hi to all
someone can tell me what the RequestType field in the ExecutionLog table
means?
From some experiments seems that there are 2 values:
0 - Reports executed manually
1 - Reports execution due to a subscription event
there are other values? Is it possibile to know them? I'm tryin with write a
Report that will help to read the ExecutionLog table.
Davide MauriThere are only two values. 0 means that the report was run in the web
service process, 1 means it was run in the reportserver windows service
process.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davide Mauri" <mauri_davide@.libero.it> wrote in message
news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Hi to all
> someone can tell me what the RequestType field in the ExecutionLog table
> means?
> From some experiments seems that there are 2 values:
> 0 - Reports executed manually
> 1 - Reports execution due to a subscription event
> there are other values? Is it possibile to know them? I'm tryin with write
a
> Report that will help to read the ExecutionLog table.
> Davide Mauri
>|||Thanx a lot Daniel
just another one question:
can you also tell me what the values in the column "source" mean? i always
have a value of 1 here.
Thanx a lot again & in advance :-)
Davide
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uCVEZBasEHA.1016@.TK2MSFTNGP10.phx.gbl...
> There are only two values. 0 means that the report was run in the web
> service process, 1 means it was run in the reportserver windows service
> process.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Davide Mauri" <mauri_davide@.libero.it> wrote in message
> news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Hi to all
>> someone can tell me what the RequestType field in the ExecutionLog table
>> means?
>> From some experiments seems that there are 2 values:
>> 0 - Reports executed manually
>> 1 - Reports execution due to a subscription event
>> there are other values? Is it possibile to know them? I'm tryin with
>> write
> a
>> Report that will help to read the ExecutionLog table.
>> Davide Mauri
>>
>|||1 = Live report
2 = Run from cache
3 = Run from snapshot
4 = run from history
More info can be found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_7942.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davide Mauri" <mauri_davide@.libero.it> wrote in message
news:O#ZcN#fsEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Thanx a lot Daniel
> just another one question:
> can you also tell me what the values in the column "source" mean? i always
> have a value of 1 here.
> Thanx a lot again & in advance :-)
> Davide
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uCVEZBasEHA.1016@.TK2MSFTNGP10.phx.gbl...
> > There are only two values. 0 means that the report was run in the web
> > service process, 1 means it was run in the reportserver windows service
> > process.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Davide Mauri" <mauri_davide@.libero.it> wrote in message
> > news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
> >> Hi to all
> >>
> >> someone can tell me what the RequestType field in the ExecutionLog
table
> >> means?
> >>
> >> From some experiments seems that there are 2 values:
> >>
> >> 0 - Reports executed manually
> >> 1 - Reports execution due to a subscription event
> >>
> >> there are other values? Is it possibile to know them? I'm tryin with
> >> write
> > a
> >> Report that will help to read the ExecutionLog table.
> >>
> >> Davide Mauri
> >>
> >>
> >
> >
>

Representing currency?

We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
Consider using the money datatype in SQL Server. It keeps 4 decimal places.
I'd never use real or float for money. It can handle very large numbers,
somewhat akin to my weekly salary. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
We have a pretty large financial application using SQL Server 2000 and an
Access front-end. We're having trouble with rounding of currency values. In
the database, we're using a Real datatype for money amounts. The problem is
that in the VBA program we calculate a invoice header value for Invoice
Total, and store it in a Real field. But when we do a SQL Sum() on the
invoice line items, the value returned is "sometimes" off by a penny or so.
Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
In the VBA program, we store the database values in a variable of type
Single.
Any comments?
Thanks!
|||Have you considered using the money data type?
J
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23yPanXMXFHA.3488@.tk2msftngp13.phx.gbl...
> We have a pretty large financial application using SQL Server 2000 and an
> Access front-end. We're having trouble with rounding of currency values.
> In
> the database, we're using a Real datatype for money amounts. The problem
> is
> that in the VBA program we calculate a invoice header value for Invoice
> Total, and store it in a Real field. But when we do a SQL Sum() on the
> invoice line items, the value returned is "sometimes" off by a penny or
> so.
> Should we be using the SQL Server Decimal (12,2) datatype instead of Real?
> In the VBA program, we store the database values in a variable of type
> Single.
> Any comments?
> Thanks!
>
|||I'll differ from the other replies by saying don't use MONEY or SMALLMONEY
for monetary amounts. Use NUMERIC or DECIMAL instead. The money types have
no particular advantage (trivially you might save 1 byte in storage over the
equivalent DECIMAL) but they do have some significant problems with rounding
that can cause you to lose precision in calculations.
In any case REAL is an inexact numeric and so is probably totally unsuitable
for currency amounts.
David Portas
SQL Server MVP