July 22, 2011 at 5:40 am
Hello everybody,
I have a two tables. A want to INSERT data from table1 to table2. But table1 has one column less than table2. It's clear that INSERT will not work. I receive error message, but I need to CATCH this error message and store it somewhere to LogTable.
Here is my code:
BEGIN TRY
INSERT INTO table2 SELECT * FROM table1
END TRY
BEGIN CATCH
print 'There was an error: ' + ERROR_MESSAGE()
END CATCH
Query result:
Column name or number of supplied values does not match table definition.
But why there is no 'There was an error: '. CATCH block was not reached ? Why ?
Thanks for any advice.
July 22, 2011 at 5:50 am
Because that code is never executed. That error is found and thrown at compile time (not sure if this is the exact term).
July 22, 2011 at 5:57 am
Yes, I thought that, but how to insert this error to some log table ? How I catch this error ?
July 22, 2011 at 6:02 am
try catch from the calling application would be my only guess. I never had to do deal with this.
July 22, 2011 at 6:05 am
Thanks for advice. But there is no another application over it. It's pure SQL script.
July 22, 2011 at 6:12 am
raztos (7/22/2011)
Thanks for advice. But there is no another application over it. It's pure SQL script.
I'm guessing dba generated audit triggers?
If not, what are you trying to do? I can't think of any other good reason to have that code voluntarily fail in production (because you'd want to know right away that you seriously messed up the latest ddl changes instead of months later down the lane where a new critical column is not getting tracked in the history).
July 22, 2011 at 6:17 am
I try to import xls file to tempTable(table2) and sometime users who export xls file delete one column. And column counts in tempTable(table2) are different than in table1(which is destination table).
That's why I need to have logged messages.
July 22, 2011 at 6:21 am
I'd do a select into from excel to temp table. Then validate that the columns match in qty, size and data types. Then I'd import to the real table if there are no errors.
Maybe SSIS as something built in to do this, but I don't know that tool at all.
July 22, 2011 at 6:22 am
To avoid a compile time error, you'd have to execute it dynamically E.g.:
BEGIN TRY
DECLARE @sql VARCHAR (8000)
SET @sql='INSERT INTO table2 SELECT * FROM table1'
EXEC(@sql)
END TRY
BEGIN CATCH
print 'There was an error: ' + ERROR_MESSAGE()
END CATCH
July 22, 2011 at 6:26 am
HowardW (7/22/2011)
To avoid a compile time error, you'd have to execute it dynamically E.g.:
BEGIN TRYDECLARE @sql VARCHAR (8000)
SET @sql='INSERT INTO table2 SELECT * FROM table1'
EXEC(@sql)
END TRY
BEGIN CATCH
print 'There was an error: ' + ERROR_MESSAGE()
END CATCH
Ya but you still have to return a meaningful error to the end user. I always hated the general oledb error we get in access instead of something useful.
July 22, 2011 at 6:29 am
Ninja's_RGR'us, HowardW thank you both very much.
Ninja's_RGR'us: I don't like using SSIS. I am exhausted of clicking : )
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply