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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

617 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