Skip to main content

SQL Server / Relativity driver Rounding issue with linked server

  • February 11, 2016
  • 9 replies
  • 0 views
D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

9 replies

D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Scott, this sounds like a problem that we've seen before between VB and Relativity.  In that case, we were never able to determine why ADO was doing what it was doing.  We'll see if we can reproduce the problem with SQLServer.  That does not mean that we'll have any better luck finding a workaround, but we'll give it another look.

D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Thanks Mike.  If there is anything I can provide (including access to the server for review) please let me know.

Have a good weekend,

Scott

D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Any news on this Mike?  

Thanks,

Scott

Juan Urraburu
  • Juan Urraburu
  • Participating Frequently
  • May 5, 2016

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Hi Scott,

May you please share how to fill the option for a Linked Server in SQL Server with Relativity so we can check your problem here? We have linked a Relativity DSN into SQL Server Management Studio but no columns are shown. (Solved. I was using a 32bit DSN, I made a 64bit and worked with the help of some tips from google search)

Thomas Morrison
  • Thomas Morrison
  • Participating Frequently
  • May 5, 2016

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Scott, since you might return to this issue...

Have you attempted to use CAST to force SQL Server to do your bidding?  I have noticed several issues where SQL Server default behavior is to truncate, but can be forced into submission using CAST.

Tom Morrison, Consultant, South Carolina, USA
D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

We received the following from Steve J in August which resolved the issue for us.  Basically we moved from the ODBC 2.5 to ODBC 3.0 driver.  Hope this helps someone out there.

"[...]

Once I got the environment configured, I was able to reproduce the decimal truncation issue that you reported.  Interestingly, though, I was also unable to get decimals fields to work from a linked MySQL database either.  I encountered different, but still, errant behavior with both data sources.  Below is the MySQL error:

-------------------------

OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.1.46-community]Numeric value out of range".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "MYSQL".

-------------------------

Since I am unable to get any decimal fields to work properly, through the OLE for ODBC interface, my best guess is that there is some inherent problem with the SQL Server implementation of this interface.  In particular, though, it seems that there is a problem with the ODBC 3.x implementation.  Thus, you should be able to work around this problem using our older ODBC 2.5 compliant driver.

Below is a link to the version 12.07 Relativity Data Client for Windows:

amerftp.microfocus.com/.../relativity_1207.exe

Try using the version 12.07 (ODBC 2.5) Relativity Data Client, and, let me know if the behavior changes.

[...]

"

D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

We received the following from Steve J in August which resolved the issue for us.  Basically we moved from the ODBC 2.5 to ODBC 3.0 driver.  Hope this helps someone out there.

"[...]

Once I got the environment configured, I was able to reproduce the decimal truncation issue that you reported.  Interestingly, though, I was also unable to get decimals fields to work from a linked MySQL database either.  I encountered different, but still, errant behavior with both data sources.  Below is the MySQL error:

-------------------------

OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.1.46-community]Numeric value out of range".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "MYSQL".

-------------------------

Since I am unable to get any decimal fields to work properly, through the OLE for ODBC interface, my best guess is that there is some inherent problem with the SQL Server implementation of this interface.  In particular, though, it seems that there is a problem with the ODBC 3.x implementation.  Thus, you should be able to work around this problem using our older ODBC 2.5 compliant driver.

Below is a link to the version 12.07 Relativity Data Client for Windows:

amerftp.microfocus.com/.../relativity_1207.exe

Try using the version 12.07 (ODBC 2.5) Relativity Data Client, and, let me know if the behavior changes.

[...]

"

A
  • Anonymous
  • September 30, 2016

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

Scott,

We ran into some similar issues using the standard Relativity Data Client with our linked servers.

We are using a Windows Server 2008 R2 and Relativity 2.1 and the SQL Server version of ODBC is 6.01.7601.17514  The solution for us was to use the Micro Focus Relativity Data Client for ADO specifically and now the decimal data is processed properly.

D

Hi,

We have installed the 64-bit relativity (ver 12.10.2015.304) driver on our Windows 2012 R2 machine running SQL Server 2012.

In SQL Server, we have linked to the DB using the MSASQL provider and while working we are having problems where numeric fields are being truncated. As an example, the following query returns 14.00 where the result should be 14.45

select *
FROM
OPENQUERY(test, '
select orderno, LineNo, str(UnitCost )
from oslines
where orderno = ''H21234''
and LineNo = 1
')

The same driver works great in SSIS so I know the ODBC is ok, it's just the SQL Server. I'm hoping its simply a configuration issue but I've fiddled with the available settings I could find to no advantage. I'd be grateful if anyone had any tips!

Thanks,

Scott


#relativityodbcsqlserver

SQL server is relativity rounding the driver issue with the linked server so for that, you can take the help of Dell printer support which will be the best option.

Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement