Cannot execute this operation while the property DeleteAction is set to 'SetNull'

Topics: Database Publishing Wizard
Apr 9, 2007 at 5:32 AM
Hi,

I'm using the SqlPubWiz 1.1. When I run it off my local database (SQL Server 2005), I get the following error:

Cannot execute this operation while the property DeleteAction is set to 'SetNull'

I do have some table relationships that are Set Null on Delete. But I don't know which one is causing this. Is it all such relationships that cause this error? I really do need those relationships...

Can you help please? Thanks very much.
Apr 9, 2007 at 9:40 PM
Edited Apr 9, 2007 at 9:43 PM
multiplex77,

Hey, thanks much for using our tool! The reason you're running into this issue is because you've most likely specified a target server of 2000. SQL Server 2000 doesn't support Foreign Key Delete actions of either SET_NULL or SET_DEFAULT; these are only available in 2005.

So if you're not able to change the target version of the database, you could a change all of your Delete actions to NONE and b write a trigger on each of the Primary Key tables that would effectively do the same thing. Here's some example SQL, munge it as you see fit:


CREATE TRIGGER dbo.DeleteForeignKeys
ON dbo.PK_Table_Name
INSTEAD OF DELETE
AS
BEGIN

SET NOCOUNT ON;

-- update foreign key values on dependent tables
IF EXISTS (SELECT * FROM [FK_Table_Name] s, deleted d WHERE s.TypeID = d.TypeID)
UPDATE [FK_Table_Name] SET TypeID = NULL WHERE TypeID IN (SELECT TypeID FROM deleted)

-- delete from the table you originally wanted to delete
DELETE FROM [PK_Table_Name] WHERE TypeID in (SELECT TypeID FROM deleted)

END



Good luck!

Ib
Apr 10, 2007 at 2:20 AM
Thanks! You were right. Great tool. It is really helping me a lot!