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_OutputMessag e = ('There was an error ' + @v_name)
INSERT INTO MessageLog (message) values (@v_outputmessa ge)
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. 3 8660
"LineVoltageHal ogen" <tr************ ****@yahoo.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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_OutputMessag e = ('There was an error ' + @v_name) INSERT INTO MessageLog (message) values (@v_outputmessa ge) 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-handl...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
LineVoltageHalo gen (tr************ ****@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(@tb l))
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: martin |
last post by:
Hi,
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would like to issue a
truncate table statement, but with millions of rows we are a bit wary
of how this will affect server performance. The alternative is to
delete is stages using rowcount but of course this will generate a
large amount of logging....
|
by: Eugene |
last post by:
In a DB2 V8.1 FP4 database I am trying to create a table SQL UDF that
is to return a contents of a temporary table with in this UDF:
create function getitemdata(pint int)
returns table
(
hostid smallint,
owid bigint
)
|
by: Peter Bailey |
last post by:
Could someone please tell me how to pass criteria as if it were a parameter.
I have a routine now that creates the sql string (well almost). at present
the parameter is so I can pass one item ie Module M10S.
Want I want to do is send 1 or more parameters ie M10S OR M10SA OR ......
The query works with one parameter can I send the dynamic sql from vba as a
complete parameter string once the form calls the query?
also I notice the...
|
by: ronin 47th |
last post by:
Hi group,
In one of the books 'Gurus Guide to Transact SQL' i found this info:
------------------------------------------------------------
TRUNCATE TABLE empties a table without logging row deletions in the
transaction log. It can't be used with
tables referenced by FOREIGN KEY constraints, and it invalidates the
transaction log for the entire database.
Once the transaction log has been invalidated, it can't be backed up
until the...
|
by: rdraider |
last post by:
Hi,
I am trying to create a script that deletes transaction tables and leaves
master data like customer, vendors, inventory items, etc. How can I use
TRUNCATE TABLE with an Exists? My problem is I have 200+ tables, if I
simply use a list like:
truncate table01
truncate table02
truncate table03
....
| |
by: pramod |
last post by:
Hi
I know we can truncate a table in DB2 by first creating it with NOT
LOGGED INITIALLY option. and when we need to truncate it, run the
following command
alter table <table name> activate not logged initially with empty table
My question is:
1. Do we have to connect to the database with auto-commit off every
|
by: Neil |
last post by:
Can one use Truncate Table on a linked server table? When I try it, I get a
message that only two prefixes are allowed. Here's what I'm using:
Truncate Table svrname.dbname.dbo.tablename
|
by: Cindy |
last post by:
I'm trying to use the NEWID function in dynamic SQL and get an error
message Incorrect syntax near the keyword 'ORDER'. Looks like I can't
do an insert with an Order by clause.
Here's the code:
SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen...
|
by: Boogha |
last post by:
I have a cursor looping through a list of tables that I want to
truncate and then do a bulk insert into, Is this possible in SQL
Server 2000 or do script each table individually.
Cheers,
Adam
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |