January 20, 2006 at 4:55 pm
Hello......
I had the following error number 4701 when checking BOL it gave no futher information about fixing the problem.
I'm trying to use the following T-SQL
TRUNCATE TABLE tblXXXXXXXX
This table is the primary table in the parent child relationship. Now before trying to truncate the table I ensured that the child table was empty, but still received the error, can anyone explain why I'm receiving this error and what to do to ensure that I can truncate the parent table after the child table has been emptied.
Cheers guys and gurls
January 20, 2006 at 5:14 pm
You can't truncate a table that has a foreign key referencing it, even if the table or tables doing the referencing are empty.
You'll have to temporarily drop/disable the foreign keys.
January 20, 2006 at 5:56 pm
Thanks for this. If this is the case anyone help with following then:
The data is pulled from an IVR Unix box in a flat file format:
CallDate, Calltime,InVDN, outVDN, MIpoint1, MIpoint2, MIpoint3.....
The data is then split out for reporting purposes:
tblCallData:
CallID, CallDate, CallTime, InVDN, OutVDN
tblKeyPress:
CallID, KeyPressPosition, KeyPressValue
Once the data is entered into a temp flat table (As it comes out of the IVR) it is transformed to a normalised format (as above). Once the data has been normalised it is then summarised in a report table.
Once the data is summarised I would like to truncate both KeyPress table (Child table) and the Call Data table (Parent Table). The reason for truncation is to minimise the log file, the other reason for truncation is that the number of records to be deleted are as follows:
Call Data (per week) 168,000 and Keypress (per week) 5,000,000, during testing the log file ended up being 6gb which although not huge it is a problem as data storage is tight. I can create a maintenance plan to ensure that the log file is shrunk each time the data is deleted, or create an alert that runs a job that shrinks the log once the log file grows above a set amount, this is not ideal. So any other suggestions are welcome
January 20, 2006 at 6:22 pm
If you set the "Cascade Delete Related Records" on the tables' relationship, this will allow you to Truncate them.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply