Dynamic Sql: Truncate Table: Trap @@ERROR

Greeting All, I have a stored proc that dynamically truncates all the
tables in my databases. I use a cursor and some dynamic sql for this:

.....
create cursor
Loop through sysobjects and get all table names in my database.

...
exec ('truncate table ' + @.TableName)

Now, I want to be able to determine if an error occurred or not nad log
that error to a table in another database.

However, when I try to trap the value of @.@.ERROR after the
exec ('truncate table ' + @.TableName) when an actual error occurs it
fails. My error was synthetically created by placing a foreign key on
the table which precludes the option of truncation:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.

The actual relevant code snippet is:

BEGIN
BEGIN
SET @.v_RowCount = (
SELECT rowcnt
FROM sysindexes
WHERE id = (
SELECT id
FROM sysobjects
WHERE name = @.v_Name
)
AND indid IN (0,1)
)
EXEC('truncate table ' + @.v_Name)
-- If there was an error truncating the current table.
-- Write the event to the MessageLog table.
IF (@.@.ERROR <> 0)
BEGIN
SET @.v_OutputMessage = ('There was an error ' + @.v_name)
INSERT INTO MessageLog (message) values (@.v_outputmessage)
RETURN (-1)
END

Like I was saying, when the error is generated because of the foreign
key the variable @.@.error is never set to 4712, in fact if I were to put
a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
would never be executed. The only thing that would show up in
Enterprise Manager would be the:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.
Any ideas as to what is going on here?

Thanks, TFD."LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1106171149.461401.53660@.f14g2000cwb.googlegro ups.com...
> Greeting All, I have a stored proc that dynamically truncates all the
> tables in my databases. I use a cursor and some dynamic sql for this:
>
> ....
> create cursor
> Loop through sysobjects and get all table names in my database.
> ...
> exec ('truncate table ' + @.TableName)
> Now, I want to be able to determine if an error occurred or not nad log
> that error to a table in another database.
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> The actual relevant code snippet is:
> BEGIN
> BEGIN
> SET @.v_RowCount = (
> SELECT rowcnt
> FROM sysindexes
> WHERE id = (
> SELECT id
> FROM sysobjects
> WHERE name = @.v_Name
> )
> AND indid IN (0,1)
> )
> EXEC('truncate table ' + @.v_Name)
> -- If there was an error truncating the current table.
> -- Write the event to the MessageLog table.
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.v_OutputMessage = ('There was an error ' + @.v_name)
> INSERT INTO MessageLog (message) values (@.v_outputmessage)
> RETURN (-1)
> END
>
> Like I was saying, when the error is generated because of the foreign
> key the variable @.@.error is never set to 4712, in fact if I were to put
> a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
> would never be executed. The only thing that would show up in
> Enterprise Manager would be the:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> Any ideas as to what is going on here?
> Thanks, TFD.

That particular error terminates the current batch, which is why the rest of
the code doesn't execute - Erland has a detailed article about error
handling in MSSQL, in which he points out that there is unfortunately no
real consistency about which errors will do this:

http://www.sommarskog.se/error-hand...#statementbatch

If your goal is to delete all rows from all tables, then truncation won't
work anyway, so you would have to look at something else. Cascading foreign
keys with DELETE would work, but of course it wouldn't perform as well (and
it wouldn't reset any identity seeds, if that's relevant). Rebuilding the
tables (and constraints and indexes) from scripts or restoring an 'empty'
backup would be other options.

Simon|||LineVoltageHalogen (tropicalfruitdrops@.yahoo.com) writes:
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.

This error can be avoided before-hand by

IF NOT EXISTS (SELECT * FROM sysforeignkeys
WHERE rkeyid = object_name(@.tbl))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and Simon, thank you for your responses. The example I showed
you was created so that I could create an error (testing phase of a big
project) during the truncation operation, it was the only way I knew of
that would guarentee an error. This database actually has no foreign
keys in it, it is a persistent store which feeds a dimensional data
store for an OLAP server. This small snippet I brought forth is just a
tiny piece of a much larger data load. I just wanted to be able to
trap an error during the truncation process if one should arise, I
can't imagine any such thing happening but I just wanted to be able to
log it should it occur.

Regards, TFD.