February 16, 2006 at 1:04 pm
I'm truncating a table in my DTS package and I've ran into the situation that someone might have that file open sometime during the day. So the DTS package hangs until the user closes the window because the Truncate Table command is blocked.
So I was thinking.... Set the lock_timeout to a low number, and if it causes an error, catch the error and use the delete statement instead. I can't seem to catch the error though. @@error doesn't return anything.
Anyone have any ideas?
February 16, 2006 at 1:56 pm
This might help:
February 17, 2006 at 7:57 am
I came across that site but I still can't seem to catch the error. This is what I want to do in the DTS using T-SQL.
SET LOCK_TIMEOUT 100
Truncate Table WhseProductCodes
IF @@error <> 0
Delete from WhseProductCodes
But as soon as it hits the truncate part, the step just fails. So instead of trying to catch the error and use the delete command, I add another t-sql on failure to delete. Kind of a shabby way to do it, but it works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply