Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1984
  • Last Modified:

Numeric Data Type conversion not working over a linked server

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 ?

Thanks
0
AddOnsInc
Asked:
AddOnsInc
  • 4
1 Solution
 
JoeNuvoCommented:
Does the link server running MSSQL ?
Can that link server connected/created as SQL Server instead of OLE DB?
0
 
AddOnsIncAuthor Commented:
Will test this ASAP and let you know.  Remote database is SQL Server.
0
 
AddOnsIncAuthor Commented:
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 ?
0
 
AddOnsIncAuthor Commented:
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.
0
 
AddOnsIncAuthor Commented:
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now