Geek Answers Handbook

SQL 2000 "TRY CATCH like" Error Handling

This is the sql 2000 database I'm working with.

I have what I call a staging table, which is the original data dump, so it is all ntext or nvarchar (255).

I need to convert all this data to the corresponding data types (e.g. int, decimal, nvarchar, etc.).

The way I was going to do this was to iterate over all the records using a while loop and try to use CAST for each column in one record during each iteration, after I visit a specific record, I mark it as processed ( field bit).

But how can I register an error when / if it occurs, but allow the continuation of the while loop.

At first I implemented this using TRY CATCH in a local instance of SQL 2005 (to get the project) and everything works fine, but today I found out that the dev and production database created by the international database administrator is SQL 2000, so I have to match .

EDIT . I use the SSIS package to populate the staging table. I see that now I have to revise this package and implement a script component to handle conversions. Thanks guys

EDIT . I do this by writing based on records, not in a batch insert, so the idea of ​​a transaction looks like it would be possible, but I'm not sure how to catch the @@ ERROR trap and allow the stored procedure to be saved.

EDIT: Guy, .

+5
5

, "loop through the record", , , , .

...

, , SQL, , .

.

UPDATE staging_table
SET status_code = 'FAIL_TEST_1'
WHERE status_code IS NULL
AND ISDATE(ntext_column1) = 0;

UPDATE staging_table
SET status_code = 'FAIL_TEST_2'
WHERE status_code IS NULL
AND ISNUMERIC(ntext_column2) = 0;

etc...


INSERT INTO results_table ( mydate, myprice )
SELECT ntext_column1 AS mydate, ntext_column2 AS myprice
FROM staging_table
WHERE status_code IS NULL;

DELETE FROM staging_table
WHERE status_code IS NULL;

, .

+2

? DTS , . DTS, ? , .

, , .
http://www.sqlteam.com/article/using-dts-to-automate-a-data-import-process

IF @@Error <> 0
 GOTO LABEL

@op
SSIS " " . , , .

+2

, . . .

TL/DR: - sql script , . . @@ERROR, .

, , varchar (255) - - ?

, , / script, varchar, prod db.

+1

@@ERROR, , .

+1

.

, :

ISNUM -  ,     
ISDATE -  ,       DATETIME
+1

More articles:


All Articles