Find out which line caused the error

Question

I have a big fat query that is dynamically written to integrate some data. Basically, what he does is to query some tables, join some others, process some data, and then insert them into the final table.

The problem is that there is too much data, and we cannot trust the sources, because there may be some errors or inconsistent data.

For example, I spent almost an hour searching for errors when developing using the client database, because somewhere in the middle of my big thick request, an error occurred converting some varchar to datetime. It turned out that they had some sales dated "2009-02-29", with an out date out of range. And yes, I know. Why was it stored as a cook? Well, the source database contains three columns for the dates: “Month”, “Day” and “Year”. I have no idea why this is so, but still it is.

But how the hell would I take it if the source is not trustworthy?

I cannot rule out exceptions, I really need it to go to a different level with the original message, but I wanted to provide additional information so that the user could at least try to solve the problem before calling us.

So, I thought about showing the user a line number or some kind of identifier that would at least give him some idea of ​​which record he would have to fix. This is also hard work, because there will be times when integration will work for up to 80,000 records. And in the integration of 80,000 records, one error message appears: "Converting a varchar data type to a datetime data type results in a date and time value out of range" means nothing.

Therefore, any idea will be appreciated.

Oh, I'm using SQL Server 2005 Service Pack 3 (SP3).

---

EDIT:

, , , , , , , , , , - .

Answer 1

ETL: Extract, Transform Load. ( , , . ?)

/ " " ? " ", - , , , , RAISERROR, .

, :

• ()
• ( SQLMenace), , ( , ..).
• " " .

, ( ) ... . , - , ?

Answer 2

isdate

select ISDATE('20090229'),ISDATE('20090227')

, ,

Answer 3

, , , . , . , 20 80K. 20 , . , ( ) .

, .

Answer 4

- , :

...big fat query here...
WHERE ISDATE(YourBadVarcharColumn)!=1

Load the data into the staging table where most of the columns are varchar and allow NULL, where you have the status column.

Run the UPDATE command, for example

UPDATE Staging
    SET Status='X'
    WHERE ISDATE(CONVERT(YourCharYear+YourCharMonth+YourCharDat+)!=1 
        OR OtherColumn<4...

Then just paste from your staging table where Status! = 'X'

INSERT INTO RealTable
        (col1, col2...)
    SELECT
        col1, col2, ...
        where Status!='X'