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'