Showing posts with label whenever. Show all posts
Showing posts with label whenever. Show all posts

Monday, March 12, 2012

Msg 4712: cannot truncate table

Hello,

I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.

A code snippet (T-SQL) that demonstrates the problem is beneath.

Does anybody know a fix-it?

Thanks in advance,
Intenion


USE tempdb

Go

-- Clearing the 'tmpdb' database

IFOBJECT_ID('user')ISNOTNULL

DROPTABLE [user];

IFOBJECT_ID('address')ISNOTNULL

DROPTABLE address;

-- Creating tables: 'address' and 'user'

CREATETABLE address

(

id INTIDENTITY(0, 1)CONSTRAINT pk_address PRIMARYKEY,

zip SMALLINTNOTNULLCONSTRAINT chk_zip CHECK(zip > 0),

street NVARCHAR(50)NOTNULLCONSTRAINT chk_street CHECK(LEN(street)> 0),

number SMALLINTNOTNULLCONSTRAINT chk_number CHECK(number > 0)

CONSTRAINT unq_address UNIQUE(zip, street, number)

);

Go

CREATETABLE [user]

(

email VARCHAR(50)CONSTRAINT pk_user PRIMARYKEY,

address INTNULLCONSTRAINT fk_user_address FOREIGNKEYREFERENCES address (id)ONDELETESETNULL

);

Go

-- Populating tables

INSERTINTO address VALUES(2365,'street 1', 2);

INSERTINTO address VALUES(23369,'street 2', 2);

Go

INSERTINTO [user] VALUES('someone@.isp.net', 0);

INSERTINTO [user] VALUES('somebody@.isp-r-us.com', 1);

Go

-- Truncate query that fails

TRUNCATETABLE address;

-- Delete query that passes

--DELETE address;

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.

Thank you for your answer,
Intenion.

|||

Jens K. Suessmeyer wrote:

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.

Kevin

Msg 4712: cannot truncate table

Hello,

I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.

A code snippet (T-SQL) that demonstrates the problem is beneath.

Does anybody know a fix-it?

Thanks in advance,
Intenion


USE tempdb

Go

-- Clearing the 'tmpdb' database

IF OBJECT_ID('user') IS NOT NULL

DROP TABLE [user];

IF OBJECT_ID('address') IS NOT NULL

DROP TABLE address;

-- Creating tables: 'address' and 'user'

CREATE TABLE address

(

id INT IDENTITY (0, 1) CONSTRAINT pk_address PRIMARY KEY,

zip SMALLINT NOT NULL CONSTRAINT chk_zip CHECK (zip > 0),

street NVARCHAR(50) NOT NULL CONSTRAINT chk_street CHECK (LEN(street) > 0),

number SMALLINT NOT NULL CONSTRAINT chk_number CHECK (number > 0)

CONSTRAINT unq_address UNIQUE (zip, street, number)

);

Go

CREATE TABLE [user]

(

email VARCHAR(50) CONSTRAINT pk_user PRIMARY KEY,

address INT NULL CONSTRAINT fk_user_address FOREIGN KEY REFERENCES address (id) ON DELETE SET NULL

);

Go

-- Populating tables

INSERT INTO address VALUES (2365, 'street 1', 2);

INSERT INTO address VALUES (23369, 'street 2', 2);

Go

INSERT INTO [user] VALUES ('someone@.isp.net', 0);

INSERT INTO [user] VALUES ('somebody@.isp-r-us.com', 1);

Go

-- Truncate query that fails

TRUNCATE TABLE address;

-- Delete query that passes

--DELETE address;

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.

Thank you for your answer,
Intenion.

|||

Jens K. Suessmeyer wrote:

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.

Kevin

Monday, February 20, 2012

MSDTC

Hi,

I am running SQl 7.0 (SP4) on Windows XP Professional (SP2). Whenever I try an insert/update type of activity, the System returns the following message :

Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'SERVER' is unavailable.

However, all the required services including MSDTC are running on the System.

Please help ASAP.

Thanks & Regards - Rajesh Pathak

It sounds like DTC is turned off on the other machine. Can you confirm that it is on and try again?

Thanks,
Sam Lester (MSFT)

|||Hello Sam,

Thanks for the prompt reply. As a matter of fact, trying any operation on the Server System which itself runs SQL 7.0 returns the MSDTC not running message. If it works on the Server System then I can try debugging problems on other Client Systems.

Kindly let me have probable resolution to this.

Thanks & Regards - Rajesh Pathak