Converting Error character string to smalldatetime

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Converting Error character string to smalldatetime

Author

Topic

faijurrahuman
Starting Member

15 Posts

Posted - 2012-07-05 : 03:30:21

Hi ;

Declare @table Table (id INT , datevalue SMALLDATETIME)

INSERT INTO @table
SELECT 1 , '01/01/2004'
UNION ALL
SELECT 2 , '02/01/2004'
UNION ALL
SELECT 3 , '03/01/2004'
UNION ALL
SELECT 4 , '04/01/2004'
UNION ALL
SELECT 5 , '05/01/2004'
UNION ALL
SELECT 6, '06/01/2004'
UNION ALL
SELECT 6, '11/01/2000 , '

SELECT * FROM @table

Error "Msg 295, Level 16, State 3, Line 3
Conversion failed when converting character string to smalldatetime data type."

Error_columnname : datevalue
Error_tablename : @table
Error_message : Conversion failed when converting character string to smalldatetime data type
Error_Value : '11/01/2000 ,'

I want to fetch the Error Column name and Error msg & Error values in separte table , How can i get this value .

This is my sample output table

CREATE TABLE Error_values (Id INT , table_name varchar(150), Error_msg Varchar(8000), Error_columnname VARCHAR(8000) , Error_Value VARCHAR(8000))

I want to store the all the error msg and related to Error columnname and table name and Error_value in this table

I need the T-SQL to achive ,any one help this logic ..

Thanks
FA

Share Knowledge team

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 04:00:05

Don't think you can get the column but you can trap the message.
You know the table name - just put that in a variable and use it in the error handler.

begin try
Declare @table Table (id INT , datevalue SMALLDATETIME)
INSERT INTO @table
SELECT 1 , '01/01/2004'
UNION ALL
SELECT 2 , '02/01/2004'
UNION ALL
SELECT 3 , '03/01/2004'
UNION ALL
SELECT 4 , '04/01/2004'
UNION ALL
SELECT 5 , '05/01/2004'
UNION ALL
SELECT 6, '06/01/2004'
UNION ALL
SELECT 6, '11/01/2000 , '

SELECT * FROM @table
end try
begin catch
select ERROR_MESSAGE(), ERROR_LINE()
end catch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

faijurrahuman
Starting Member

15 Posts

Posted - 2012-07-05 : 05:50:22

Hi ;
Thanks for your replay , The challenge is Retrieve the Error_columnname and Error_value .

any one help this problem..

Thanks

FA

Share Knowledge team

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 06:40:37

Don't think it's possible - unless your columns are all defined as nullable and you do a test insert column by column in the error handler.
You could also do a datatype conversion check column by column using the destination datatype - but that wouldn't cater for implicit conversion - maybe crreate a variable for the column and set that - may not be exactly the same.
It's easy to get the datatype error for the conversion and to search through the columns for something of that datatype but it won't tell yoou what actually caused the error.
You would also want the value or row that caused the error otherwise it's not much use - so probably a row by row insertion test - I've done that a few times in error handing using a binary chop to find an error row.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources