Need help with the code

  • Hi,

    When I am running the below stored procedure through a job I am getting the below error message.

    Executed as user: NT AUTHORITY\SYSTEM. Conversion failed when converting the varchar value '2314K' to data type int. [SQLSTATE 22018] (Error 245). The step failed.

    Can you please help me in fixing the above mentioned error.

    Stored procedure code:

    USE [Nice]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    CREATE PROCEDURE [dbo].[SP_Test1] AS

    BEGIN

    BULK INSERT Table_Test

    FROM 'C:\Test\Test.csv'

    WITH (

    FIELDTERMINATOR = '","',

    ROWTERMINATOR = '\"'

    )

    INSERT INTO [dbo].[Table_Test1]

    ([emp_code]

    ,[first_name]

    ,[last_name]

    ,[middle_name]

    ,[initials]

    ,[pay_code]

    ,[pay_rate]

    ,[paid_by]

    ,[birth_date]

    ,[gender]

    ,[ssn]

    ,[street_addr1]

    ,[street_addr2]

    ,[city]

    ,[state]

    ,[zip]

    ,[phone1]

    ,[phone2]

    ,[hire_date]

    ,[sen_date]

    ,[dept_date]

    ,[term_date]

    ,[status]

    ,[wrk_status]

    ,[hrs_week]

    ,[homedept_id]

    ,[Test_Code]

    ,[user_name]

    ,[Testing_Code2_1]

    ,[Testing_Code2_2]

    ,[Testing_Code2_3]

    ,[Testing_Code2_4]

    ,[Testing_Code2_5])

    SELECT left([col3],6)

    ,[col5]

    ,[col4]

    ,null

    ,null

    ,null

    ,0

    ,[col21]

    ,null

    ,'M'

    ,right([col26],4)

    ,null

    ,null

    ,null

    ,null

    ,null

    ,[col10]

    ,[col11]

    ,[col6]

    ,[col7]

    ,null

    ,[col8]

    ,CASE [col22] WHEN 'B' THEN 'U' WHEN 'U' THEN 'U' WHEN 'T' then 'C' ELSE 'C' END

    ,[col9]

    ,null

    ,-1

    ,[col16]

    ,left([col25],len([col25])-len('NSTES'))

    ,null

    ,[col20]

    ,[col12]

    ,[col14]

    ,left([col15],30)

    FROM [dbo].[Table_Test]

    update Table_Test1 set Test_Code=1001 where Test_Code=1000

    END

    Thank You,

  • If you search your input file for the text 2314K, it should point you in the right direction.

    It looks like whichever column that data is in is mapped to an INT column in SQL Server.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi ,

    I am using SQL Server 2005 Enterprise edition SP4 (64 BIT) ON Windows Server 2008 R2 Standard edition 64 bit SP1 .

    Below mentioned is the structure of the two tables.

    USE [Nice]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Table_Test1](

    [emp_code] [varchar](10) NULL,

    [first_name] [varchar](30) NULL,

    [last_name] [varchar](30) NULL,

    [middle_name] [varchar](30) NULL,

    [initials] [varchar](10) NULL,

    [pay_code] [varchar](10) NULL,

    [pay_rate] [varchar](10) NULL,

    [paid_by] [varchar](10) NULL,

    [birth_date] [varchar](20) NULL,

    [gender] [varchar](10) NULL,

    [ssn] [varchar](10) NULL,

    [street_addr1] [varchar](40) NULL,

    [street_addr2] [varchar](40) NULL,

    [city] [varchar](40) NULL,

    [state] [varchar](40) NULL,

    [zip] [varchar](40) NULL,

    [phone1] [varchar](20) NULL,

    [phone2] [varchar](20) NULL,

    [hire_date] [varchar](20) NULL,

    [sen_date] [varchar](20) NULL,

    [dept_date] [varchar](20) NULL,

    [term_date] [varchar](20) NULL,

    [status] [varchar](10) NULL,

    [wrk_status] [varchar](10) NULL,

    [hrs_week] [varchar](40) NULL,

    [homedept_id] [varchar](10) NULL,

    [Test_Code] [varchar](40) NULL,

    [user_name] [varchar](30) NULL,

    [Testing_Code2_1] [varchar](32) NULL,

    [Testing_Code2_2] [varchar](32) NULL,

    [Testing_Code2_3] [varchar](40) NULL,

    [Testing_Code2_4] [varchar](32) NULL,

    [Testing_Code2_5] [varchar](32) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    USE [Nice]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Table_Test](

    [col1] [varchar](40) NULL,

    [col2] [varchar](40) NULL,

    [col3] [varchar](40) NULL,

    [col4] [varchar](40) NULL,

    [col5] [varchar](40) NULL,

    [col6] [varchar](40) NULL,

    [col7] [varchar](40) NULL,

    [col8] [varchar](40) NULL,

    [col9] [varchar](40) NULL,

    [col10] [varchar](40) NULL,

    [col11] [varchar](40) NULL,

    [col12] [varchar](40) NULL,

    [col13] [varchar](40) NULL,

    [col14] [varchar](40) NULL,

    [col15] [varchar](40) NULL,

    [col16] [varchar](40) NULL,

    [col17] [varchar](40) NULL,

    [col18] [varchar](40) NULL,

    [col19] [varchar](40) NULL,

    [col20] [varchar](40) NULL,

    [col21] [varchar](40) NULL,

    [col22] [varchar](40) NULL,

    [col23] [varchar](40) NULL,

    [col24] [varchar](120) NULL,

    [col25] [varchar](40) NULL,

    [col26] [varchar](40) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    The tables which I am using doesn't have an int data type. I am not using SSIS. If i use SSIS i can skip each column like you had said.

    If i found the int data type i could have fixed the issue myself. I suspect the code. That's why I had asked your help.

    When i notified to the user he went and deleted the data which has the values 2314K. After that the Job is running successfully . I am looking in the code perspective what had caused the issue. If the user enters bad data this issue can happen again.

    Thank You,

  • What else does the job do? Are there other tasks it performs or other parts of the script that invokes the stored proc?


    And then again, I might be wrong ...
    David Webb

  • Why are all your datatypes varchar? You have dates as varchar, numerics as varchar, State as varchar(40), gender varchar(10), zip varchar(40), phone varchar(20).

    Your datatypes need some serious review and normalization wouldn't hurt.

    I suspect that the issue was that Test_Code was the culprit. After you do your insert you have an update statement with ints. Since the update has as int in the where clause it will attempt an implicit conversion on the entire column.

    The short fix is to change your update.

    update Table_Test1 set Test_Code = '1001' where Test_Code = '1000'

    The proper fix is to use proper datatypes.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • There are other steps in the Job. The job is failing when it is running the stored procedure which i had mentioned.

    I don't see any issues with other steps in the Jobs. when it process the first step it will run the second and third step with out any issues. I am not worried about the other steps.

  • sql2k8 (6/25/2013)


    There are other steps in the Job. The job is failing when it is running the stored procedure which i had mentioned.

    I don't see any issues with other steps in the Jobs. when it process the first step it will run the second and third step with out any issues. I am not worried about the other steps.

    The fix I posted should solve that. It was easy to spot because that is the only int anywhere in that script. 🙂

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I did not design those datatypes. There are already present and I was working on those. I will change the code and give a try.

    I will update you about the status.

  • Hi ,

    Even without making changes like the one you have mentioned above the Job is running successfully.

    If i assume Test_Code column is a culprit in the table Table_Test1 how is the job running now successfully.

    Without making the above mentioned changes it should fail right. But it is not that case.

    If the Job is running successfully now how can we ask the user to modify the code in the stored procedure.

    Thank You,

  • I will change the code like what you had said but when it was having issues with the Test_Code Column in the Table_Test1 . It should throw an error message like it cannot convert 1000 to 1001 but why was it throwing an error message like the one mentioned below.

    Conversion failed when converting the varchar value '2314K' to data type int. [SQLSTATE 22018] (Error 245).

  • sql2k8 (6/25/2013)


    Hi ,

    Even without making changes like the one you have mentioned above the Job is running successfully.

    If i assume Test_Code column is a culprit in the table Table_Test1 how is the job running now successfully.

    Without making the above mentioned changes it should fail right. But it is not that case.

    If the Job is running successfully now how can we ask the user to modify the code in the stored procedure.

    Thank You,

    It failed because of the implicit datatype conversion from varchar to int.

    This is simple to demonstrate.

    create table #Table_Test1

    (

    Test_Code varchar(10)

    )

    insert #Table_Test1

    select '2314K'

    The above is a simple example of your table. The insert part of your proc would have easily inserted the data. The problem is when you update it.

    update #Table_Test1 set Test_Code=1001 where Test_Code=1000

    That will fail because it tries to convert all values in the table to an int because of datatype precedence. Using varchar in your update will allow to not fail.

    update #Table_Test1 set Test_Code='1001' where Test_Code='1000'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sql2k8 (6/25/2013)


    I will change the code like what you had said but when it was having issues with the Test_Code Column in the Table_Test1 . It should throw an error message like it cannot convert 1000 to 1001 but why was it throwing an error message like the one mentioned below.

    Conversion failed when converting the varchar value '2314K' to data type int. [SQLSTATE 22018] (Error 245).

    Because you used this

    update Table_Test1 set Test_Code=1001 where Test_Code=1000

    instead of this

    update Table_Test1 set Test_Code='1001' where Test_Code='1000'

    Your version forces SQL Server to attempt to convert the column to an INT in order to compare it with 1000. Version 2 uses the literal text.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi ,

    After the data named 2314K was removed from the table the Job was working fine.

    I changed the code from

    update Table_Test1 set Test_Code=1001 where Test_Code=1000

    update Table_Test1 set Test_Code='1001' where Test_Code='1000'

    The Job is working after I had changed the Code also.

    Thank You very much for helping me.

  • Glad you got it working. The important is, do you understand why changing the code will work?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi ,

    After the data named 2314K was removed from the table the Job was working fine.

    I changed the code from

    update Table_Test1 set Test_Code=1001 where Test_Code=1000

    update Table_Test1 set Test_Code='1001' where Test_Code='1000'

    The Job is working after I had changed the Code also.

    Thank You very much for helping me.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply