Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.
Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>
|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:
> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Scale Problems Between SQL 2K SP3 and SP4

This is a really strange error that I am seeing and I'm wondering if
anyone else has seen it yet.
I have a small database (less than 100k for backup file) that I send to a
third party. On several tables in this database there are fields that are
set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
running 2K sp4. When they restore the database on their side, SOME of
these same numeric fields are showing a scale of 0, so in effect the scale
is lost and rather than reporting "1.5000" we are reporting a value of
"1". The precision and scale on third party's end is decimal(18,0).
Is there any way to explain why the scale for SOME of these fields is
changing? It's not even a complete conversion. There are some fields that
are not getting converted, which REALLY confuses me.
The only thing I can figure is that a version of the database with this
scale exists somewhere on the server and that when they do a restore,
somehow the scale for these fields is retained from that old database,
even though we are restoring from a backup. How this would happen, I have
no idea, but it is the only thing I can think of at this point.Perhaps the database backup file contains multiple backups and the first
(older schema) is restored by default. You can check this with RESTORE
HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyBackup.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"KBarrett" <kelseybarrett@.matteicos.com> wrote in message
news:op.szk8rrbkbpkth2@.tmc-kbarrett.matteicos.com...
> This is a really strange error that I am seeing and I'm wondering if
> anyone else has seen it yet.
> I have a small database (less than 100k for backup file) that I send to a
> third party. On several tables in this database there are fields that are
> set to decimal(19,4). I am running SQL Server 2000 sp3/3a. Third party is
> running 2K sp4. When they restore the database on their side, SOME of
> these same numeric fields are showing a scale of 0, so in effect the scale
> is lost and rather than reporting "1.5000" we are reporting a value of
> "1". The precision and scale on third party's end is decimal(18,0).
> Is there any way to explain why the scale for SOME of these fields is
> changing? It's not even a complete conversion. There are some fields that
> are not getting converted, which REALLY confuses me.
> The only thing I can figure is that a version of the database with this
> scale exists somewhere on the server and that when they do a restore,
> somehow the scale for these fields is retained from that old database,
> even though we are restoring from a backup. How this would happen, I have
> no idea, but it is the only thing I can think of at this point.
>|||Thanks for the tip Dan. I will check on that.
-k
On Tue, 01 Nov 2005 20:35:28 -0800, Dan Guzman
<guzmanda@.nospam-online.sbcglobal.net> wrote:

> Perhaps the database backup file contains multiple backups and the first
> (older schema) is restored by default. You can check this with RESTORE
> HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyBackup.bak'

Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Precision on Money data type

How do I set the precision on the money data type in SQL Server. I want it to display only 2 decimal places and get rid of any more than that id someone tries to insert more without throwing an error.

Example:

i input 10.5432

It makes it 10.54
automatically.

If this is not possible, can I at least put in an amount like 10.56 and not have the database automatically turn this into 10.5600

Please help::How do I set the precision on the money data type in SQL Server.

Tried the documentation? Mean, this is so obvious that I would look there first.

Let me quote from "money data type / overview":

::Monetary data values from -2^63 (-922,337,203,685,477.5808) through
::2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary
::unit. Storage size is 8 bytes.

::I want it to display only 2 decimal places and get rid of any more than that id someone
::tries to insert more without throwing an error.

Welcome to programming. YOu have to do so in your input layer.

::If this is not possible, can I at least put in an amount like 10.56 and not have the database
::automatically turn this into 10.5600

You can us an insert / update trigger to round the values. I would normally handle this i n the business objects :-)|||Use decimal instead of money. You can exactly specify the precision and scale|||Thanks Dutch,... I appreciate that

Precision and Scale in a calulated column

How do I set Precision and Scale in a calulated column?

I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please?

Thanks,

Scott

hi Scott,

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE #t (

d decimal(18,4) DEFAULT 1234.123,

D2 AS CONVERT(decimal(8,2), d)

);

GO

INSERT INTO #t DEFAULT VALUES;

SELECT * FROM #t;

GO

DROP TABLE #t;

--<

d D2

--

1234.1230 1234.12

regards

|||

Either using a CREATE TABLE or ALTER TABLE statement, and add the precision,scale to the computed column declaration.

Here is an example:

CREATE TABLE #MyTable
( RowID int IDENTITY,
Cost decimal(6,2),
Quantity int,
Total AS cast(( Cost * Quantity ) AS decimal(8,4))
)

INSERT INTO #MyTable VALUES ( 5, 10 )
INSERT INTO #MyTable VALUES ( 2.5, 5 )
INSERT INTO #MyTable VALUES ( 4.55, 5 )

SELECT * FROM #MyTable

DROP TABLE #MyTable

|||

Thank you for helping.

I'm using the Management Studio. Is there a way to accomplish this in the formula line?

Thanks again.

Scott

|||

Thanks for helping.

I tried the following:

***********************************

Use SIR
ALTER TABLE dbo.Table_1
ALTER COLUMN Results
nPRECISION(6,2)

Go

************************************

Recieved the following message:

************************************

Msg 4928, Level 16, State 1, Line 2
Cannot alter column 'Results' because it is 'COMPUTED'.

What else might I try?

|||

You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula.

Do something like this:

USE SIR;

ALTER TABLE dbo.Table_1
DROP COLUMN Results;

ALTER TABLE dbo.Table_1
ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2)));

precision

Precision is the number of digits in a number. Scale is the number of digits
to the right of the decimal point in a number. For example, the number
123.45 has a precision of 5 and a scale of 2.
Is the above statement true? or Is the precision 3 and the scale is 2?
Thank you in advance.In your example 123.45 the precision (p) is 5 and scale (s) is 2.
For more information:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
--
Rohtash Kapoor
http://www.sqlmantra.com
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> Precision is the number of digits in a number. Scale is the number of
digits
> to the right of the decimal point in a number. For example, the number
> 123.45 has a precision of 5 and a scale of 2.
> Is the above statement true? or Is the precision 3 and the scale is 2?
> Thank you in advance.
>|||So on this one 1234567890.1234 the precision is 14 and the scale is 4 same
as (14,4)?
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
> In your example 123.45 the precision (p) is 5 and scale (s) is 2.
> For more information:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> > Precision is the number of digits in a number. Scale is the number of
> digits
> > to the right of the decimal point in a number. For example, the number
> > 123.45 has a precision of 5 and a scale of 2.
> >
> > Is the above statement true? or Is the precision 3 and the scale is 2?
> >
> > Thank you in advance.
> >
> >
>|||Yes
>--Original Message--
>So on this one 1234567890.1234 the precision is 14 and
the scale is 4 same
>as (14,4)?
>"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in
message
>news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
>> In your example 123.45 the precision (p) is 5 and scale
(s) is 2.
>> For more information:
>> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-
dz_3grn.asp
>> --
>> Rohtash Kapoor
>> http://www.sqlmantra.com
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in
message
>> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
>> > Precision is the number of digits in a number. Scale
is the number of
>> digits
>> > to the right of the decimal point in a number. For
example, the number
>> > 123.45 has a precision of 5 and a scale of 2.
>> >
>> > Is the above statement true? or Is the precision 3
and the scale is 2?
>> >
>> > Thank you in advance.
>> >
>> >
>>
>
>.
>