Any help with this problem would be greatly appreciated
-------
I developed a database under SQL Server 2000, with Access 2000 on Windows
2000 as client. This had been running fine for several years. The client is
now upgrading to Windows XP, and has come across a display problem on the
reports. The figures are correct but are now displayed with lots of trailing
zeros e.g
365.00000
I have built a test system but cannot duplicate the problem :-( . Also the
problem varies between computers, and even between different users on the
same computer. One solution that seems to work is to explicitly cast all
output from the server e.g.
CAST ( TonsLoaded AS INT) TonsLoaded
but this is a pita since there are 141 stored procedures.
Has anyone seen this problem? I haven't found anything in the newsgroups or
any Knowlege Base article. It looks like it is to do with Windows XP as the
client ...
David"David Greenwood" <david at greenwood dot lu> wrote in message
news:40274c80$1_1@.news.vo.lu...
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on Windows
> 2000 as client. This had been running fine for several years. The client
is
> now upgrading to Windows XP, and has come across a display problem on the
> reports. The figures are correct but are now displayed with lots of
trailing
> zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
the
> problem varies between computers, and even between different users on the
> same computer. One solution that seems to work is to explicitly cast all
> output from the server e.g.
> CAST ( TonsLoaded AS INT) TonsLoaded
> but this is a pita since there are 141 stored procedures.
> Has anyone seen this problem? I haven't found anything in the newsgroups
or
> any Knowlege Base article. It looks like it is to do with Windows XP as
the
> client ...
> David
If the behaviour varies between users on the same PC, then the issue may be
somehow related to the language or regional settings in their profiles,
although 4 decimal places seems like a strange format. Or possibly the
updated MDAC version in Windows XP is behaving slightly differently. What
data type is the TonsLoaded column in the result set - decimal?
In any case, I guess you should be able to modify your Access client to
display these figures as integers - it's usually easier to handle display
issues in the front end. And you might consider posting this in an Access
newsgroup (if you haven't already), in case it's a known Access/XP issue.
Simon|||If the worse comes to the worse then I shall just have to fix the problem on
the client end, somehow. But this is a big file (10 mega) full of forms and
reports so that will be a major job.
I checked on the client's system and either there are 3 trailing zeros or 6
trailing zeros, though I can't see why. Also, it appears that all the
machines have exactly the same problem. The only difference I can see with
my test system is that they have Active Directory ... (?)
I shall try posting to the Access newsgroup as you suggested.
David
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4027d81a$1_2@.news.bluewin.ch...
> "David Greenwood" <david at greenwood dot lu> wrote in message
> news:40274c80$1_1@.news.vo.lu...
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
Windows
> > 2000 as client. This had been running fine for several years. The client
> is
> > now upgrading to Windows XP, and has come across a display problem on
the
> > reports. The figures are correct but are now displayed with lots of
> trailing
> > zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> the
> > problem varies between computers, and even between different users on
the
> > same computer. One solution that seems to work is to explicitly cast all
> > output from the server e.g.
> > CAST ( TonsLoaded AS INT) TonsLoaded
> > but this is a pita since there are 141 stored procedures.
> > Has anyone seen this problem? I haven't found anything in the newsgroups
> or
> > any Knowlege Base article. It looks like it is to do with Windows XP as
> the
> > client ...
> > David
> If the behaviour varies between users on the same PC, then the issue may
be
> somehow related to the language or regional settings in their profiles,
> although 4 decimal places seems like a strange format. Or possibly the
> updated MDAC version in Windows XP is behaving slightly differently. What
> data type is the TonsLoaded column in the result set - decimal?
> In any case, I guess you should be able to modify your Access client to
> display these figures as integers - it's usually easier to handle display
> issues in the front end. And you might consider posting this in an Access
> newsgroup (if you haven't already), in case it's a known Access/XP issue.
> Simon|||David Greenwood (david at greenwood dot lu) writes:
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on
> Windows 2000 as client. This had been running fine for several years.
> The client is now upgrading to Windows XP, and has come across a display
> problem on the reports. The figures are correct but are now displayed
> with lots of trailing zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
> the problem varies between computers, and even between different users
> on the same computer. One solution that seems to work is to explicitly
> cast all output from the server e.g.
I have to admit that I saw your post in .clients, but I let it pass, since
1) I don't know Access, 2) There seems to be information missing from
your posting.
What data type are the columns in SQL Server? How does the code look
like in Access to bring the data from SQL Server to the output? Do you
just associate a record set with a window control, or is there some
processing on the way?
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I rebuilt my test Windows XP system, managed to duplicate the error, and
have at last got to the bottom of the problem. It would appear that Windows
XP does not correctly interprets the data for fields based on the SQL Server
"decimal" type, even though this has always worked fine under Windows 2000.
We use decimal fields with fixed three decimal places to store tonnes and
kilos. Once these have been totalled, averaged etc they automatically track
6 decimal places of precision. After ROUNDing to 0 places the data is
returned to the client. However, running tests under Query Analyser shows
that the 6 zeros are still part of the decimal answer. Windows 2000 has
never had a problem with this, no matter what the language settings. The
data has displayed okay. In contrast, Windows XP chokes on the data. I
guess not many people use the decimal data type, so this has not been an
issue.
The work round is to CAST the data to either INT or FLOAT as required on the
server.
Thanks for the feedback,
David
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AF2EB79AB2Yazorman@.127.0.0.1...
> David Greenwood (david at greenwood dot lu) writes:
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
> > Windows 2000 as client. This had been running fine for several years.
> > The client is now upgrading to Windows XP, and has come across a display
> > problem on the reports. The figures are correct but are now displayed
> > with lots of trailing zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> > the problem varies between computers, and even between different users
> > on the same computer. One solution that seems to work is to explicitly
> > cast all output from the server e.g.
> I have to admit that I saw your post in .clients, but I let it pass, since
> 1) I don't know Access, 2) There seems to be information missing from
> your posting.
> What data type are the columns in SQL Server? How does the code look
> like in Access to bring the data from SQL Server to the output? Do you
> just associate a record set with a window control, or is there some
> processing on the way?
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||David Greenwood (david at greenwood dot lu) writes:
> We use decimal fields with fixed three decimal places to store tonnes
> and kilos. Once these have been totalled, averaged etc they
> automatically track 6 decimal places of precision. After ROUNDing to 0
> places the data is returned to the client. However, running tests under
> Query Analyser shows that the 6 zeros are still part of the decimal
> answer. Windows 2000 has never had a problem with this, no matter what
> the language settings. The data has displayed okay. In contrast,
> Windows XP chokes on the data. I guess not many people use the decimal
> data type, so this has not been an issue.
You still don't tell us what you are doing, so I have to guess that you
are doing something like:
declare @.d decimal(18, 6)
select @.d = 21
select round(@.d, 0)
This displays as 21.000000 everywhere I tried, and this appears correct to
me. The return value of round() is the same as the input value.
I don't think Windows XP vs. 2000 has anything to do it. I tried it on
two Windows 2000 machines, and I got 21.00000 back on both. Rather I
would guess that it depends on the MDAC version. Windows XP comes with
MDAC 2.7, whereas Windows 2000 comes with MDAC 2.5 or even earlier, but
to get full use of SQL 2000, it's good to have MDAC 2.6 at least.
Interesting enough, I ran the above from ISQL, which uses DB-Library,
which is not part of the MDAC, and basically unchanged since 1997. It,
too, displays 21.000000.
So it seems that your code took a shortcut, depending of what actually is a
bug in an earlier versions on the MDAC.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment