Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help with the code Expand / Collapse
Author
Message
Posted Sunday, June 23, 2013 9:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
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,


Post #1466565
Posted Sunday, June 23, 2013 10:43 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1466574
Posted Monday, June 24, 2013 4:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047

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,
Post #1466941
Posted Monday, June 24, 2013 4:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 897, Visits: 7,096
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
Post #1466946
Posted Tuesday, June 25, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,110, Visits: 11,940
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)
Post #1467181
Posted Tuesday, June 25, 2013 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
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.
Post #1467207
Posted Tuesday, June 25, 2013 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,110, Visits: 11,940
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)
Post #1467214
Posted Tuesday, June 25, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
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.
Post #1467223
Posted Tuesday, June 25, 2013 9:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
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,
Post #1467243
Posted Tuesday, June 25, 2013 9:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
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).
Post #1467244
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse