Numeric Data Type conversion not working over a linked server

Posted on 2011-03-14
Last Modified: 2012-05-11
We have a service provider who collects certain information for us.

We download that data into a local data warehouse on a periodic basis.

They present to us a database that we can remotely connect to that has a series of views that we can select from to create our local data warehouse.

Expanding one particular view we can see that the first column in the view (item_id) is supposedly of type 'int'.

However, they tell us that the underlying table has this column of type bigint.  

We know that it has to be a bigint because when we run a query on the table, some of the values in the item_id column are above the 2^31-1 (2,147,483,647) field size limit of the int data type.

Perusing the data there's some 61,500 rows.

When I connect directly to their database over the internet (in SSMS 2008) and execute the following:

select * from dbo.input_table
where item_id > 2147483647

There are 483 rows returned.

The smallest value is 2968669707 and the largest value is 2981731917.   Both are way over the int limit.

We can connect to this remote database via a linked server on our local database.  
We also have the access to connect remotely over the internet to the database directly.

Connecting remotely: select * from input_table (works)
Connecting via linked server: select * from linked_svr.dbname.dbo.input_table (fails)

Error message:
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "SQLNCLI10" for linked server "linked_svr". Conversion failed because the data value overflowed the data type used by the provider.

So, tried this:

select item_id
from linked_svr.dbname.dbo.input_table
where item_id  >= 2147483647

Same error.

Modified to this and it works!

select CONVERT(bigint, item_id) item_id
from linked_svr.dbname.dbo.input_table
where item_id  >= 2147483647

Now where getting somewhere, right?   Not quite.

Having nailed down how to fetch the data, we try to create our warehouse table:

select CONVERT(bigint, item_id) item_id
into local_wh_table
from linked_svr.dbname.dbo.input_table
where item_id  >= 2147483647

We get the same data transformation error as above.

Also tried creating a local copy of the table called test3 with the item_id column defined as a bigint instead of an int and doing this:

insert into test3
select CONVERT(bigint, item_id) item_id
from linked_svr.dbname.dbo.input_table
where CONVERT(bigint, item_id) >= 2147483647

I'm still not sure why the view is showing a datatype of int and how it contains values outside the range of supported values for an int datatype.

Anyone got any pointers ?

Question by:AddOnsInc
  • 4
LVL 11

Expert Comment

ID: 35134774
Does the link server running MSSQL ?
Can that link server connected/created as SQL Server instead of OLE DB?

Author Comment

ID: 35143319
Will test this ASAP and let you know.  Remote database is SQL Server.

Author Comment

ID: 35145073
Looked into the definition of the linked server and read some of the definitions of the function

The core description of linked server is:
Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

It appears that the servers are OLE DB by default.

The linked server has no service definition and the default is:

[ @provider = ] 'provider_name'
Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL; however, if provider_name is omitted, SQLNCLI is used. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.) The OLE DB provider is expected to be registered with the specified PROGID in the registry.

So, it appears that your suggestion is unfortunately not viable.

Anyone else ?

Accepted Solution

AddOnsInc earned 0 total points
ID: 35156904
Closing this question.  It appears that the service provider when presenting the view had vast the column in the view as an int when the underlying column on the table was bigint.  This morning they have recreated the view with the affected column as bigint and everything is working again.

Author Closing Comment

ID: 35187289
It appears that the service provider when presenting the view had vast the column in the view as an int when the underlying column on the table was bigint.  This morning they have recreated the view with the affected column as bigint and everything is working again

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question