SQL Server money data type is synced over with 4 decimal places bug?

Ben Lam

New Member
Hi, I created an ODBC data source for a SQL Server 2014 database and using Connect to sync. The SQL Server column type is 'money'.

When Connect syncs over the data it seems to do so to 4 decimal places in the session data.

Ex. the value in SQL Server shows '10.99' and when I view the session data it's 10.9900

Is this a TracerPlus bug? When I display the value via custom SQL in the mobile client it shows 10.9900 but I would like it to show 10.99.

Thanks,
Ben
 

Dan Peluso

Member
Staff member
Hi,

I can't think what might cause this. I will put in request from someone on our side to take a look. It's possible something in TP Connect is incorrectly converting it but it may also be something in the ODBC driver for SQL server. I will see what we can find out. I will create a ticket for this and add you in the response list if that is ok.
 

Ben Lam

New Member
Yes, please, thanks. I'm using SQL Server 2014 with ODBC driver 'SQL Server' version 10.00.14393.00 on win 10. Per youtube training video I used odbcad32.exe (not sure if that matters).
 

Dan Peluso

Member
Staff member
Hi,

As an update to this forum from your support ticket....

We are able to replicate this behavior but it looks like the data is being store in the SS database as 4 digit decimal. Something in the data viewer of SQL Server appears to be truncating it (probably using configurable display options which would default to 2 digit decimal I would guess.).

One of our techs had what I thought was a good suggestion. If you want to send 2 digit decimal over to TracerPlus, you should create a view in SQL Server that includes a SELECT clause but also a cast of that number value to 2 digits. Once that view is created, you can actually sync your SQL Server data from that view as opposed to directly from the table.

Something like this SELECT clause as a simple example would do what I am describing. Of course, the table and column names would need to be modified to your schema.

"CREATE VIEW [dbo].[vw_currency_cast] AS SELECT CAST(price AS decimal(12,2)) AS price FROM dbo.currency_test"
 
Top