Cannot create view WITH VIEW_METADATA

I'm trying to create a view and make it updatable using an INSTEAD OF UPDATE trigger. This works OK on SQL Server 2005 (even with compatibility level 80), but on SQL Server 2000 I receive an error message:

"Could not perform CREATE VIEW because WITH VIEW_METADATA was specified and the view is not updatable."

The following sql reproduces the problem.

CREATE TABLE test (a int, b int)
go
INSERT INTO test VALUES(1,0)
INSERT INTO test VALUES(2,0)
INSERT INTO test VALUES(3,1)
INSERT INTO test VALUES(3,1)
go

CREATE VIEW testview WITH VIEW_METADATA AS
SELECT a,SUM(b) b FROM test GROUP BY a
go

CREATE TRIGGER TR_testview_U ON testview INSTEAD OF UPDATE AS
BEGIN
UPDATE test SET b=inserted.b
FROM test INNER JOIN inserted ON test.a = inserted.a
END
go

SELECT * FROM test
SELECT * FROM testview
UPDATE testview SET b=2 WHERE a=3
SELECT * FROM test
SELECT * FROM testview
go

DROP VIEW testview
DROP TABLE test

I want my view to be updatable so I can use it in my VB6/ADO application, and the trigger won't fire unless I specify VIEW_METADATA.

When you create any view with following operation (operator/clause) , that view will be become as NOT UPDATEABLE VIWE, when it is not UPDATABLE you can’t specify the WITH VIEW_METADATA.

TOP,

GROUP BY,

UNION

DISTINCT

|||Thanks for the reply. As I said in SQL Server 2005 this works, just wondering why not in SQL Server 2000 - WITH VIEW_METADATA is precisely for making non UPDATABLE views updatable, according to BOL

"When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers."

which seems to imply that the columns wouldn't have been updatable without VIEW_METADATA.