Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rds207
Posting Yak Master
198 Posts |
 Posted - 2010-03-18 : 01:09:12
|
| Here is the structure of my table ....CREATE TABLE [dbo].[DW_T_EC_HCI_BREW]( [EC_JOB_IND] [varchar](255) NULL, [EC_JOB_NAME] [nvarchar](255) NOT NULL, [EC_PROCEDURE_NAME] [nvarchar](255) NULL, [EC_PROJECT_NAME] [nvarchar](255) NOT NULL, [EC_JOB_ID] [bigint] NULL, [EC_JOB_STEP_NAME] [nvarchar](255) NOT NULL, [EC_CREATED_TIME] [datetime] NULL, [EC_START_TIME] [datetime] NULL, [EC_RUNNABLE_TIME] [datetime] NULL, [EC_OWNER] [nvarchar](255) NULL, [EC_HOST_NAME] [nvarchar](255) NULL, [EC_RESOURCE_NAME] [nvarchar](255) NULL, [EC_OUTCOME] [nvarchar](255) NULL, [EC_STATUS] [nvarchar](9) NULL, [EC_STEP_INDEX] [int] NULL, [EC_START_MILLIS] [bigint] NULL, [EC_FINISH_MILLIS] [bigint] NULL, [EC_RUNNABLE_MILLIS] [bigint] NULL, [EC_STEP_ID] [int] NOT NULL, [EC_PROJECT_ID] [bigint] NOT NULL, [EC_LAUNCHED_BY_USER] [nvarchar](255) NULL, [EC_FINISH_TIME] [datetime] NULL, [EC_PARENT_ID] [bigint] NULL, [EC_JOB_PRIORITY] [int] NOT NULL, [JOB_TYPE] [varchar](50) NULL) ON [PRIMARY]My query :Insert INTO DW_T_EC_HCI_BREW(EC_JOB_IND ,EC_JOB_NAME ,EC_PROCEDURE_NAME ,EC_PROJECT_NAME,EC_JOB_ID,EC_JOB_STEP_NAME, EC_CREATED_TIME,EC_START_TIME ,EC_FINISH_TIME ,EC_RUNNABLE_TIME, EC_OWNER ,EC_HOST_NAME,EC_RESOURCE_NAME, EC_OUTCOME,EC_STATUS ,EC_STEP_INDEX,EC_START_MILLIS, EC_FINISH_MILLIS, EC_RUNNABLE_MILLIS, EC_STEP_ID ,EC_PROJECT_ID,EC_LAUNCHED_BY_USER,EC_PARENT_ID,EC_JOB_PRIORITY,JOB_TYPE )select * from dbo.DW_T_EC_HCI_BREW_TEMP where dbo.DW_T_EC_HCI_BREW_TEMP.EC_STEP_ID not in (select EC_STEP_ID from DW_T_EC_HCI_BREW )DW_T_EC_HCI_BREW_TEMP and DW_T_EC_HCI_BREW has the same structure , i just want to enter new records enterted in temp table to new table via SSIS packages, when i execute the package the package just hangs at 2032 rows , so i tried to do manually by writing the above query and see what is the issue, i get the following error messageMsg 241, Level 16, State 1, Line 1Conversion failed when converting date and/or time from character string.Please Help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-03-18 : 01:23:30
|
| yes both them are in same order here the structure of another table ,CREATE TABLE [dbo].[DW_T_EC_HCI_BREW_TEMP]( [EC_JOB_IND] [varchar](255) NULL, [EC_JOB_NAME] [nvarchar](255) NOT NULL, [EC_PROCEDURE_NAME] [nvarchar](255) NULL, [EC_PROJECT_NAME] [nvarchar](255) NOT NULL, [EC_JOB_ID] [bigint] NULL, [EC_JOB_STEP_NAME] [nvarchar](255) NOT NULL, [EC_CREATED_TIME] [datetime] NULL, [EC_START_TIME] [datetime] NULL, [EC_RUNNABLE_TIME] [datetime] NULL, [EC_OWNER] [nvarchar](255) NULL, [EC_HOST_NAME] [nvarchar](255) NULL, [EC_RESOURCE_NAME] [nvarchar](255) NULL, [EC_OUTCOME] [nvarchar](255) NULL, [EC_STATUS] [nvarchar](9) NULL, [EC_STEP_INDEX] [int] NULL, [EC_START_MILLIS] [bigint] NULL, [EC_FINISH_MILLIS] [bigint] NULL, [EC_RUNNABLE_MILLIS] [bigint] NULL, [EC_STEP_ID] [int] NOT NULL, [EC_PROJECT_ID] [bigint] NOT NULL, [EC_LAUNCHED_BY_USER] [nvarchar](255) NULL, [EC_FINISH_TIME] [datetime] NULL, [EC_PARENT_ID] [bigint] NULL, [EC_JOB_PRIORITY] [int] NOT NULL, [JOB_TYPE] [varchar](50) NULL |
 |
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-03-18 : 01:44:57
|
| AND while executing the package, the packages hangs at 2032 rows...i dont understand why its just hangs up at this pacticular number .. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rds207
Posting Yak Master
198 Posts |
|
|
rds207
Posting Yak Master
198 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-03-18 : 21:45:57
|
I double checked all the data type, temp table and main table has same datatypes......how can data be loaded into temp table if there is something wrong with the data?The problem is data is not getting loaded into main table....cannot understand whats wrong with this......any tips are highly appraciatedplease help...quote:
Originally posted by tkizer
No, because according to the error, the data in question is trying to go from a string into a datetime column. Either the format of the date/time is incorrect, or the date/time is outside of the dates that the datetime data type supports.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-03-18 : 21:49:54
|
quote:
how can data be loaded into temp table if there is something wrong with the data?
quote:
Msg 241, Level 16, State 1, Line 1Conversion failed when converting date and/or time from character string.
The error message says it fail to convert from string to datetime. The problem is not in the mixmatch of data but the data itself. You will not have problem storing this "@#$%" in string.But how do you expect SQL Server to convert "@#$%" in string this to datetime ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-03-18 : 21:55:21
|
what am saying is as both tables have same datatypes,if at all "@#$%" is the string data which cannot be converted to datetime, it should fail while entering into the Temp table itself, but there is no issue in getting the data loaded into Temp which same as main table...Basically am getting the DiffData from Temp table to Main table...Hope i am able to explain what the problem is...quote:
Originally posted by khtan
quote:
how can data be loaded into temp table if there is something wrong with the data?
quote:
Msg 241, Level 16, State 1, Line 1Conversion failed when converting date and/or time from character string.
The error message says it fail to convert from string to datetime. The problem is not in the mixmatch of data but the data itself. You will not have problem storing this "@#$%" in string.But how do you expect SQL Server to convert "@#$%" in string this to datetime ?
KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-03-18 : 22:04:37
|
try specify the column list explicitly don't use *
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-03-18 : 23:07:09
|
that does'nt work either :-(it got struck on 2271 row in debugging mode.....does not go any further...i waited for 20min now....i select top 3000 rows from temp and saw all the datetime fields , and there is no string data ...all are datetime values....Is there any way where i can see which at which record the packages gets hung?quote:
Originally posted by khtan
try specify the column list explicitly don't use *
KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
|
|
|
|
|
Conversion failed when converting date and/or time