Cannot perform SET operation. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cannot perform SET operation.

Author

Topic

sistasi
Starting Member

5 Posts

Posted - 2008-10-16 : 06:13:38

Hi,

I am using MS SQL Version 8.0.
I have a stored procedure that actually what it does is just
delete records from a table (eg. Deal) and insert a record on a log table (eg. Archive_Log).

However this table (Deal) has a trigger, everytime deleted, it will insert into another table (eg. DealHistory).

here's the trigger code:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER    TRIGGER trg_del_Deal ON dbo.Deal
FOR DELETE
AS
BEGIN

SET IDENTITY_INSERT DealHistory ON
INSERT INTO DealHistory
        	 (	ID,
		EventID,
		DealName,
		CreatedDate,
		LastChangedDate)
SELECT  	del.ID,
		del.EventID,
		del.DealName,
		del.CreatedDate,
		getdate()
from deleted del

SET IDENTITY_INSERT CE_au_DealHistory OFF
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

However when executing the first stored procedure it failed and give me this error:

The current user is not the database or object owner of table 'DealHistory'. Cannot perform SET operation.

Any idea how to solve this?
Thanks.

-sista-

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-17 : 00:20:11

Sounds like the user executing the stored procedure doesn't have the permissions to run IDENTITY_INSERT on the DealHistory table.

But do you need an identity column on that table anyway? I'm guessing no since it's an archive table. If the source table has an identity column, then you do not need the identity option enabled on that same column in the destination table. Otherwise, you will end up with different values between the two tables which is not desireable for an archive table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sistasi
Starting Member

5 Posts

Posted - 2008-10-17 : 03:01:02

Hi tkizer,

Yes I need to use the IDENTITY_INSERT on the DealHistory as the table has ID and it is its identity.

Because if i don't use the SET IDENTITY_INSERT ON then it will give me this error:

Cannot insert explicit value for identity column in table 'DealHistory' when IDENTITY_INSERT is set to OFF. The statement has been terminated.

-sista-

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-17 : 11:34:39

You are missing my point. Turn off the identity option on that column. It isn't needed in an archive table.

For instance:

SourceTable
Column1 identity int
Column2 varchar(50)
Column3 datetime

ArchiveTable
Column1 int
Column2 varchar(50)
Column3 datetime

Notice how the identity option isn't there for Column1.

You will have identical tables this way and not need the IDENTITY_INSERT option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sistasi
Starting Member

5 Posts

Posted - 2008-10-20 : 04:18:07

oh but i cannot change the table structure. it is there all along and i shan't touch it.
any other alternative?

-sista-

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-20 : 17:07:40

But it's just an archive table!

So you don't care if the data will be different between the two tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sistasi
Starting Member

5 Posts

Posted - 2008-10-20 : 20:50:37

actually it's not just archive table, it's history table..
it's for audit purposes so there will be many different records on the table.

-sista-

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-21 : 12:23:40

Of course there'll be many records in the table. But shouldn't the row from the source table be exactly the same in the history table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sistasi
Starting Member

5 Posts

Posted - 2008-10-21 : 21:13:05

not all is the same.
eg. in deal table, one record is modified
in dealhistory table, there will be 2 records for this ID.
first one is the original one
second one is the modified one.

-sista-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources