Sql error converting data type nvarchar to bigint

Error converting data type nvarchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I execute the next code:

SELECT CAST(Order_Number AS bigint)
FROM Orders
WHERE Order_Number IS NOT NULL
AND ISNUMERIC(Order_Number) = 1
AND Order_Number NOT LIKE ‘%-%’
AND Order_Number NOT LIKE ‘%.%’

And besides having all those checks, it falis with: ‘ Error converting data type nvarchar to bigint.’

How can I spot the row that is breaking this? The table has 1,000,000 rows, so I can’t look for it visually.

Thanks in advance.

Answers

Using NOT LIKE ‘%[^0-9]%’ should exclude any OrderNumber that has any character other than ‘0’ through ‘9’. Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn’t be trying the conversion on a record that will not be selected. (If I know that I’m not going to select the record, why would I try to do work on it. )

I’m wondering, do you need to CONVERT(BIGINT, 2147483647)?? Might it be trying to convert the BIGINT into an INT to do the comparison?? (Just a WAG, but I’ve seen stranger things. )

Источник

Error converting data type nvarchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I execute the next code:

SELECT CAST(Order_Number AS bigint)
FROM Orders
WHERE Order_Number IS NOT NULL
AND ISNUMERIC(Order_Number) = 1
AND Order_Number NOT LIKE ‘%-%’
AND Order_Number NOT LIKE ‘%.%’

And besides having all those checks, it falis with: ‘ Error converting data type nvarchar to bigint.’

How can I spot the row that is breaking this? The table has 1,000,000 rows, so I can’t look for it visually.

Thanks in advance.

Answers

Using NOT LIKE ‘%[^0-9]%’ should exclude any OrderNumber that has any character other than ‘0’ through ‘9’. Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn’t be trying the conversion on a record that will not be selected. (If I know that I’m not going to select the record, why would I try to do work on it. )

I’m wondering, do you need to CONVERT(BIGINT, 2147483647)?? Might it be trying to convert the BIGINT into an INT to do the comparison?? (Just a WAG, but I’ve seen stranger things. )

Источник

Error converting data type nvarchar to bigint

Question

Hi: I am getting this data conversion error while I am trying to insert one table into another existing table (appending data). I would really appreciate any assistance on this please.

Answers

You should list the columns in your SELECT statement in order of the INSERT columns list. But here you haven’t inserted any column. Also you can use a SELECT INFO statement. Therefore a table will be automatically created and you won’t have these problems.

Sorry the screen shot didn’t work, please see below.

* from [dbo] . [PS Trans Jan20]

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to bigint.

Completion time: 2020-02-14T14:39:58.2229306+10:00

Источник

Error converting data type nvarchar to bigint

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

This is a common error for SQL Server, but I got it in a uncommon way. Can anyone please explain the scenario below?

I have a table called — tblIDNumber where there are two columns — IDN_Number [NVarchar(200)] and Temp [BigInt]

SELECT *
FROM dbo.tblIDNumber
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0
AND CAST(IDN_Number AS BIGINT) = 1

SQL Server give me the error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

I first thought IDN_Number in type 33 has characters, but it doesn’t, becasue the below query works.

UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0

To workaround, I ran the query,

UPDATE dbo.tblIDNumber
SET IDN_Number = ‘123’
WHERE IDN_IDNumberTypeStaticValue = 33
AND IDN_Removed = 0

and then I ran the first query, and SQL Server does NOT give me the same error — Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.

Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause.

finally, I found the root cause to be an index that the first query uses :

CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

If I remove the index, the first query works without the error .

Is there anything wrong with the index OR . .

Источник

Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered [duplicate]

I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT. I’m well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1 . Despite this, I still get an error when trying to query the data stating that Error converting data type nvarchar to bigint.

The following works fine (no errors reported by SQL):

The following throws the error:

This variation also throws the error:

Keep in mind that this does not error and returns all the records successfully.

I was able to find a solution, which was to convert my BIGINT to NVARCHAR in the subquery:

If I insert the records into a temporary table:

I can use that temporary table in a subquery successfully:

What in the world is going on? It seems like SQL refuses to use the subquery to get a smaller result set before running the outer query, it wants to use the whole table regardless of what I do. SQL Server 2012 Developer Edition

2 Answers 2

Use try_cast() instead.

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

SqlZim already gave you a good method to avoid the error in his answer. However, in the question and in comments you seem curious as to why one query throws an error and the other does not. I am able to reproduce your issue:

This query works fine:

This query throws an error:

Msg 8114, Level 16, State 5, Line 25

Error converting data type nvarchar to bigint.

The SQL Server query optimizer can reorder elements of a query as it sees fit to try to find a query plan with a good enough estimated cost, as long as the changes do not affect the final results of the query. To illustrate the concept lets walk through one possible way the second query can be refactored. To be clear, this is not the actual step-by-step process that the query optimizer goes through for this example. Start with the query:

Push down the predicate:

The derived table is no longer needed so get rid of that:

We know that BI.ID = NV.ID_NV so we can apply the filter on Z.ID to NV.ID_NV as well:

The join no longer needs to be implemented as INNER JOIN because we are filtering down to a single value for both join columns. We can rewrite as a CROSS JOIN :

If we look at the query plan for the second query we can tell that the end result is very similar to the final transformed query:

Here is the text of the filter predicate for reference:

If SQL Server evaluates the CONVERT_IMPLICIT part of the predicate before the isnumeric part then we get an error.

As a general rule, avoid relying on implied order of operations when writing SQL queries. You may have a query that works well today but starts to throw errors if data is added to the table or if a different query plan is chosen. There are, of course, exceptions (kind of). In practice, you will usually see the different parts of a CASE statement to evaluate in the order that you’ve written them, but even then it’s possible to get errors that you weren’t expecting. You can also add a superfluous TOP to parts of your query to encourage a certain order of operations. Consider the following query:

You and I know that the TOP will not change the results of the query, However, there is not a guarantee to the optimizer that the derived table won’t return more than 9223372036854775807 rows so it must evaluate the TOP . Technically, in that query we ask for the first 9223372036854775807 rows and then we want to filter out rows with an ID different from 500. Pushing the ID = 500 predicate down to the derived table could change the results so SQL Server will not do that. In this example, the query executes without an error and the filtering is done at the very end:

Источник

ABhinav i already check my parameters and i didnt put a varchar value can you please see this program as well to check..

Dim conn As New SqlConnection("Data Source=192.168.0.4;Initial Catalog=CYBERYA;Persist Security Info=True;User ID=cyberya;Password=Piso4minutes;MultipleActiveResultSets=True")

        

        Dim cmdtest As New SqlCommand("Populatecsv", conn)
        cmdtest.CommandType = CommandType.StoredProcedure
        conn.Open()
        cmdtest.Connection = conn
        
        cmdtest.Parameters.Clear()
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@srn", "srn"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Id", "Id"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@status", "status"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CustomerName", "customername"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@HomeContact", "homecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BusinessContact", "businesscontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@MobileContact", "mobilecontact"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@LotHouseNo", "lothouseno"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@RoomUnitStall", "roomunitstall"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgFloor", "bldgfloor"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@BldgName", "bldgname"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Street", "street"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Subdivision", "subdivision"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Barangay", "barangay"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CityMunicipality", "citymunicipality"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Province", "province"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Region", "region"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Package", "package"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@PromoCode", "promocode"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ApplicationDate", "applicationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@EndorsedDate", "endorseddate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@paymenttype", "paymenttype"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@scheduledate", "scheduledate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@loanstatus", "loanstatus"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@ActivationDate", "activationdate"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcdelivery", "pcdelivery"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@nopcavailed", "nopcavailed"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@datepcpickup", "datepcpickup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@pcissuance", "pcissuance"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@salesagent", "salesagent"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@Coordinator", "Coordinator"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesChannel", "saleschannel"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@SalesGroup", "salesgroup"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@CreatedBy", "CreatedBy"))
        cmdtest.Parameters.Add(New Data.SqlClient.SqlParameter("@timestamp", "timestamp"))
        

——————————-stored procedure——————————

ALTER procedure [dbo].[Populatecsv]
@ID BIGINT,
@SRCode BIGINT,
@SRNumber BIGINT,
@Status NVARCHAR (MAX),
@CustomerName NVARCHAR (MAX),
@HomeContact NVARCHAR (MAX),
@BusinessContact NVARCHAR (MAX),
@MobileContact NVARCHAR (MAX),
@LotHouseNo NVARCHAR (MAX),
@RoomUnitStall NVARCHAR (MAX),
@BldgFloor NVARCHAR (MAX),
@BldgName NVARCHAR (MAX),
@Street NVARCHAR (MAX),
@Subdivision NVARCHAR (MAX),
@Barangay NVARCHAR (MAX),
@CityMunicipality NVARCHAR(MAX),
@Province NVARCHAR (MAX),
@Region NVARCHAR (MAX),
@Package NVARCHAR (MAX),
@PromoCode NVARCHAR (MAX),
@PaymentType Nvarchar (MAX),
@ApplicationDate DATETIME,
@EndorsedDate DATETIME,
@PaymentDate DATETIME,
@ScheduleDate DATETIME,
@ActivationDate DATETIME,
@PCDelivery NVARCHAR (MAX),
@NoPCAvailed BIGINT,
@datepcpickedup DATETIME,
@pcissuance nvarchar (MAX),
@SalesAgent NVARCHAR (MAX),
@Coordinator NVARCHAR (MAX),
@SalesChannel NVARCHAR (MAX),
@SalesGroup NVARCHAR (MAX),
@CreatedBy NVARCHAR (MAX),
@LoanStatus NVARCHAR (MAX),
@TimeStamp DATETIME,
@SRN BIGINT OUTPUT
AS
BEGIN
    INSERT INTO SRN (ID,SRCode,SRNumber,Status,CustomerName, HomeContact,BusinessContact,MobileContact,LotHouseNo,RoomUnitStall,BldgFloor,BldgName,Street, Subdivision,Barangay,CityMunicipality,Province,Region ,Package, PromoCode,PaymentType,ApplicationDate,EndorsedDate,PaymentDate,ScheduleDate,ActivationDate,PCDelivery ,NoPCAvailed,datepcpickedup, pcissuance,SalesAgent ,Coordinator,  SalesChannel,SalesGroup, CreatedBy,LoanStatus,TimeStamp )
    VALUES (@ID, @SRCode,@SRNumber,@Status,@CustomerName,@HomeContact,@BusinessContact,@MobileContact,@LotHouseNo,@RoomUnitStall,@BldgFloor,@BldgName,@Street,@Subdivision,@Barangay,@CityMunicipality,@Province,@Package,@PromoCode,@PaymentType,@ApplicationDate,@EndorsedDate ,@PaymentDate,@ScheduleDate,@ActivationDate,@PCDelivery,@NoPCAvailed,@datepcpickedup,@pcissuance,@SalesAgent,@Coordinator,@SalesChannel ,@SalesGroup,@CreatedBy, @LoanStatus, @TimeStamp  );
    SELECT @SRN= SCOPE_IDENTITY();
END

Johan Bijnens

SSC Guru

Points: 135009