Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with the code


Need help with the code

Author
Message
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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,
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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,
David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 8584
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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.
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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,
sql2k8
sql2k8
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1115
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search