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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now