?
Solved

Error Converting Data Type nvarchar to binint

Posted on 2008-11-13
10
Medium Priority
?
1,259 Views
Last Modified: 2012-05-05
I have a query that failing with this error:

"Error converting data type nvarchar to bigint"

My line that's failing is this:
INNER JOIN Country ON ColMeta.xCountry = Country.countryID

ColMeta.xCountry is nvarchar(1000)
Country.countryID = bigint

Is there a way to convert this on the fly (here in the query) without having to modify the database tables?

Thanks
0
Comment
Question by:greddin
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22952393
Is colMeta.xcountry the NAME of the country?  If so, perhaps your country table contains a NAME as well and you could join on the name instead of the country.countryID


So:

INNER JOIN Country ON ColMeta.xCountry = Country.countryNAME
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952395
What are the values of ColMeta.xCountry?

Think issue is most likely the field size if your data consists of all numeric/whole number values and no extraneous text.

You can try a cast, but may have to do some trimming first.
0
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 2000 total points
ID: 22952402
inner join country on colmeta.xcountry = cast(country.countryid as nvarchar)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952434
I would agree with Brandon as I double checked and size doesn't impact this with NVARCHAR.  I have seen it with some datatypes that it needs a numeric data type with same length, but I was able to cast NVARCHAR(1000) as long as the length of actual data strings are 19 characters or less.

If they are over that, then you have to use a different data type.

If you have string values, then find different column to match against like Brandon indicated.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952443
LOL, that will work too...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952468
Sometimes it is the simpliest ways that work best.  Gallitin's solution to focus on casting the BIGINT to NVARCHAR(1000) is a good approach.  Although the fact that the xcountry would not cast to BIGINT would state that it will probably not match up to your BIGINT countryid for at least one value which may be what you want anyway since you are using an INNER JOIN.

0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22952492
Yeah don't know if it would work, but figured it was a place to start.  When I first got on here cast and convert were the hardest thing for me, but know I have a whole knowledge base from everyone helping me:)
0
 

Author Comment

by:greddin
ID: 22952494
ColMeta.xCountry is just a numeric value. Thanks.
0
 

Author Closing Comment

by:greddin
ID: 31516484
Thanks to all who responded. But this is exactly what I was looking for.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952569
Greddin, glad Gallitin's solution worked for.  To solve the root of the issue though, I would check your data for xcountry value greater than 19 characters if you can't change datatype.  Spaces don't impact the cast, so it is actual data length.

SELECT xCountry
FROM ColMeta
WHERE LEN(LTrim(RTrim(xCountry))) > 19

Good luck!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

862 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