Conerting Error character string to smalldatetime

• sqlfriends@sql.com

  SSCommitted

  Points: 1504

  More actions

  July 5, 2012 at 1:16 am

  #263270

  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."

  [p]

  Error_columnname : datevalue

  Error_tablename : @table

  Error_message : Conversion failed when converting character string to smalldatetime data type

  Error_Value : '11/01/2000 ,' [/p]

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

  [/p]

  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))

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

  [p] I need the T-SQL to achive ,any one help this logic ..[/p]

  Thanks

  FA

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  July 5, 2012 at 1:40 am

  #1508953

  Take a look at BEGIN TRY along with a BEGIN CATCH aswell as @@Error

• sqlfriends@sql.com

  SSCommitted

  Points: 1504

  More actions

  July 5, 2012 at 1:46 am

  #1508957

  Thanks for you replay , I am using the error_handling function and Begin Transaction

  Error_Number, Error_Message, Error_Severity, Error_State, Error_Line,

  Error_Procedure

  I want to get the Error_columnname AND Error_value in other log table ..

  Thanks;

  FA