October 22, 2010 at 9:05 am
Hi.
I have implemented a stored procedure in SQL Server 2005. I need to capture the possible errors. I have written an Insert To ... Select statement, so I read a row block from a Table1 to insert it into a Table2. But I could have an error, f.e. a conversion error. I want to capture the row of the block that has generated the error for the insert. Is it possible? How?
Any helps for me, please? Many thanks
October 22, 2010 at 12:57 pm
There are certainly ways to do this. In order for anyone to help you, you have to help us help you. Post table definitions, inserts with sample data. Then what you have tried and details about what you are looking for. We are all volunteers and you will be much more likely to get help if you make it easy on us. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2010 at 5:12 am
Have you looked into using TRY/CATCH blocks within the procedure? That's the best way I know of to handle errors.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2010 at 1:30 am
Hi. Thanks for your replies.
I know try and catch statement, but I need to capture which is the row data into a block data of the select that could generate an error in the INSERT INTO Table 2 SELECT ... FROM Table1 statement.
Is it possible? Is it not more clear my question?
Thanks
October 25, 2010 at 6:05 am
Maybe, so you have data that is suspect, like it might not have the right data types in a column or something? The only way to be sure ahead of time would be to run queries against that data prior to attempting the insert to validate it. Say, you have a string column that you're moving into a date column, you could run SELECT... WHERE ISDATE(mystringcolumn) = 0. On the other hand, you could put that in the WHERE clause so that you only move data that is correct.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2010 at 6:26 am
Hi, thanks.
It is occured to have data with an error (f.e. conversion error, arithmetic overflow, ...). The goal isn't to insert the right data but to find which row data has generated the error in order to correct the data and re-execute the statement. Thanks
October 26, 2010 at 8:04 am
I have not used it before, but the Output clause might work.
October 26, 2010 at 2:41 pm
If you send us the table definitions (eg, column names, data types) for each of the tables we might be able to help.
October 26, 2010 at 3:42 pm
pmscorca (10/25/2010)
Hi, thanks.It is occured to have data with an error (f.e. conversion error, arithmetic overflow, ...). The goal isn't to insert the right data but to find which row data has generated the error in order to correct the data and re-execute the statement. Thanks
If your data is giving you a certain error, how is this any different than what Grant suggested? Based on that error, you still need to query your source data to determine which rows are canidates to cause that error.
If your insert truely is a INSERT INTO table1 SELECT...FROM Table2, than it should be pretty easy to anticipate most of the errors based on the differences in the table definitions, then account for those errors in your load logic.
One thing you could do would be to use the TRY/CATCH block to insert the recordset that caused the error somewhere (another table or XML blob)where you can do analysis on it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply