June 23, 2013 at 9:39 pm
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,
June 23, 2013 at 10:43 pm
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 24, 2013 at 4:27 pm
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,
June 24, 2013 at 4:48 pm
What else does the job do? Are there other tasks it performs or other parts of the script that invokes the stored proc?
June 25, 2013 at 8:16 am
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/
June 25, 2013 at 8:44 am
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.
June 25, 2013 at 8:56 am
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/
June 25, 2013 at 9:09 am
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.
June 25, 2013 at 9:36 am
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,
June 25, 2013 at 9:37 am
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).
June 25, 2013 at 9:52 am
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/
June 25, 2013 at 9:59 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 26, 2013 at 1:03 pm
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.
June 26, 2013 at 1:13 pm
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/
June 26, 2013 at 1:13 pm
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