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

[Ben Lam]

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]

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]

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]

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"