<p>This is a sql 2000 database that I am working with.</p>

<p>I have what I call a staging table that is a raw data dump of data, so everything is ntext or nvarchar(255). </p>

<p>I need to cast/convert all of this data into the appropriate data types (ie int, decimal, nvarchar, etc.)</p>

<p>The way I was going to do this was to iterate through all records using a while loop and attempt a CAST on each column on a single record during each iteration, after I visit a particular record I flag it as processed (bit field).</p>

<p>But how can I log the error when/if it occurs but allow the while loop to continue.</p>

<p>At first I implemented this using a TRY CATCH in a local SQL 2005 instance (to get the project going) and all was working well, but i learned today that the dev &amp; production database that the international DBA's have set up is a SQL 2000 instance so I have to conform.</p>

<p><strong>EDIT</strong>: I am using a SSIS package to populate the staging table. I see that now I must revisit that package and implement a script component to handle the conversions. Thanks guys</p>

<p><strong>EDIT</strong>: I <em>am</em> doing this on a record by record basis, not a batch insert, so the transaction idea seems like it would be feasible but I'm not sure how to trap @@ERROR and allow the stored procedure to continue.</p>

<p><strong>EDIT</strong>: I really like <a href="https://stackoverflow.com/questions/33685/sql-2000-try-catch-like-error-handling#39161">Guy's approach</a>, I am going to implement it this way.</p>

[{'answer_id': 33717, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 2, 'selected': False, 'text': '<p>What are you using to import the file?  DTS has scripting abilities that can be used for data validation.  If your not using DTS are you using a custom tool?  If so do your validation there.</p>\n\n<p>But i think this is what your looking for.<br>\n<a href="http://www.sqlteam.com/article/using-dts-to-automate-a-data-import-process" rel="nofollow noreferrer">http://www.sqlteam.com/article/using-dts-to-automate-a-data-import-process</a></p>\n\n<pre><code>IF @@Error &lt;&gt; 0\n GOTO LABEL\n</code></pre>\n\n<p>@op<br>\nIn SSIS the "red line" from a data import task can redirect bad rows to a separate destination or transform.  I haven\'t played with it in a while but hope it helps.</p>\n'}, {'answer_id': 33725, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 1, 'selected': False, 'text': '<p>It looks like you are doomed. See <a href="http://www.sommarskog.se/error-handling-I.html#whathappens" rel="nofollow noreferrer">this</a> document.</p>\n\n<p>TL/DR: A data conversion error always causes the whole batch to be aborted - your sql script will not continue to execute no matter what you do. Transactions won\'t help. You can\'t check @@ERROR because execution will already have aborted.</p>\n\n<p>I would first reexamine why you need a staging database full of varchar(255) columns - can whatever fills that database do the conversion?</p>\n\n<p>If not, I guess you\'ll need to write a program/script to select from the varchar columns, convert, and insert into the prod db.</p>\n'}, {'answer_id': 33740, 'author': 'FlySwat', 'author_id': 1965, 'author_profile': 'https://Stackoverflow.com/users/1965', 'pm_score': 1, 'selected': False, 'text': '<p>Run each cast in a transaction, after each cast, check @@ERROR, if its clear, commit and move on.</p>\n'}, {'answer_id': 39136, 'author': 'kristof', 'author_id': 3241, 'author_profile': 'https://Stackoverflow.com/users/3241', 'pm_score': 1, 'selected': False, 'text': '<p>You could try checking for the data type before casting and actually avoid throwing errors.</p>\n\n<p>You could use functions like:<PRE>\nISNUM - to check if the data is of a numeric type\nISDATE - to check if it can be cast to DATETIME</PRE></p>\n'}, {'answer_id': 39161, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 3, 'selected': True, 'text': '<p>Generally I don\'t like "loop through the record" solutions as they tend to be slow and you end up writing a lot of custom code.</p>\n\n<p>So...</p>\n\n<p>Depending on how many records are in your staging table, you could post process the data with a series of SQL statements that test the columns for correctness and mark any records that fail the test.</p>\n\n<p>i.e.</p>\n\n<pre><code>UPDATE staging_table\nSET status_code = \'FAIL_TEST_1\'\nWHERE status_code IS NULL\nAND ISDATE(ntext_column1) = 0;\n\nUPDATE staging_table\nSET status_code = \'FAIL_TEST_2\'\nWHERE status_code IS NULL\nAND ISNUMERIC(ntext_column2) = 0;\n\netc...\n</code></pre>\n\n<p>Finally</p>\n\n<pre><code>INSERT INTO results_table ( mydate, myprice )\nSELECT ntext_column1 AS mydate, ntext_column2 AS myprice\nFROM staging_table\nWHERE status_code IS NULL;\n\nDELETE FROM staging_table\nWHERE status_code IS NULL;\n</code></pre>\n\n<p>And the staging table has all the errors, that you can export and report out.</p>\n'}]
['https://Stackoverflow.com/questions/33685', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1950/']