Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Numeric Data Type conversion not working over a linked server

Posted on 2011-03-14
5
Medium Priority
?
1,959 Views
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 ?

Thanks
0
Comment
Question by:AddOnsInc
[X]
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
5 Comments
 
LVL 11

Expert Comment

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

Author Comment

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

Author Comment

by:AddOnsInc
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 ?
0
 

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:AddOnsInc
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

718 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