When you want to empty a table, and trancate returns this not-very-helpful error message …

TRUNCATE TABLE dbo.SomeTable

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘dbo.SomeTable’ because it is being referenced by a FOREIGN KEY constraint.

Try using delete instead …

begin tran
DELETE dbo.SomeTable
rollback tran

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint “FK__SomeTable__7BFD7C01”. The conflict occurred in database “SomeDatabase”, table “dbo.SomeTable”, column ‘SomeColumn’.

… for a more helpful message.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s