Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Get Column Name causing exception

    Hi,

    I am writing queries with CONVERT function. I have more than 200 columns in query. I am processing each row and write it to file.

    I need to log all errors occured during process. I need to capture column which is causing exception (Arithmetic overflow error converting numeric to data type varchar.) and log it in other file and send to administrator.

    How can I get Column which is causing exception?

    Thanks in advance...

  2. #2
    Join Date
    Mar 2010
    Posts
    5
    Anyone has solution for above problem?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is very much like using Excel to write a love letter or a hammer to drive a nail... Writing Transact-SQL to do ETL tasks takes way more work than the problem is worth. Try using SSIS (the SQL Server Integration Services), it was made for doing this kind of task!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2010
    Posts
    5
    Can SQL Server Integration Services create error files with column name?

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If I had to do this, I would write a cursor that would, one-by-one, increment through each column of the table. On each increment, I would use a cursor to scan through that column's data and perform whatever test you might require.

    This kind of programming is purely utility work, not meant for production coding.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Mar 2010
    Posts
    5
    We have more than 215 columns and it does not seem to be correct way. any other solution?

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What difference does the number of columns make?

    I loved Pat's analogy that this is like using Excel to write a love letter. There is no very clean way to do this in SQL.

    Right now you have no solution. Using a double-cursor methodology you would have a solution. It's the kind of solution I would use as a utility when doing, say, some sort of database conversion. It is not the kind of solution I would allow users to access in a production environment.

    Is this intended for some one-time data analysis? Or, is this intended for a production environment.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    To go through columns in TSQL does not require a cursor, but it does require data type validation of the content of each column on each row. I agree with usage of SSIS for on-going tasks, but for what you describe TSQL may not be "the last resourt". I'd recommend to write content validation function for each data type contained in the set. Then I would write a function that would return the data type identifier that you created content validation function for. Then, I would write a function that would take the key value for each row, and return a set containing as many rows with error descriptions (those are going to be your custom errors) as there are errors that met error trap conditions in your content validation functions, along with a row key value and the column name where the error was caught. Then you can use 1 select (no cursor), and generate the output which you can send directly to a text file.

    ...or...

    In order to avoid above-mentioned complexities, you can use a cursor to iterate through each row, and have 215 validation sections per iteration to account for 215 columns.

    Either way prepare for some coding
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •