Update partitioned view using transform data task

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

Import/Export (DTS) and Replication (2000)

Update partitioned view using transform data task

Author

Topic

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-20 : 10:48:29

I would like to update a partitioned view using the transform data task in DTS. The server complains that the view cannot be updated as part of a bulk operation. If I turn off the use fast loads option, the server complains that a multiple step OLE operation failed, but does not explain why.

Is it possible to insert rows to a partitioned view through DTS? If so, how. If not, what is the preferred method for large data volumes?

-------
Moo.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 15:58:11

Did you check out BOL to make sure you're meeting all of the reqs?:

BOL:

Conditions for Modifying Partitioned Views

Only the Developer and Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE operations on partitioned views. To modify partitioned views, the statements must meet these conditions:

The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow NULLs. For those member table columns that have DEFAULT definitions, the statements cannot use the keyword DEFAULT explicitly.

The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the INSERT action will fail with a constraint violation.

UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table.

PRIMARY KEY columns cannot be modified through an UPDATE statement if the member tables have text, ntext, or image columns.

Columns in the view that are an IDENTITY column in one or more of the member tables cannot be modified through an INSERT or UPDATE statement.

If one of the member tables contains a timestamp column, the view cannot be modified through an INSERT or UPDATE statement.

INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

Note To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-21 : 02:58:00

Yep. The issue only arises during the transfer of data in DTS. I could merily insert rows into the view through query analyzer all day, but, since I have other things to do, would prefer to do it in one go through DTS or similar. But it wont let me.

-------
Moo.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-21 : 13:15:08

Oh, you're the guy who says moo. I was wondering where I saw that.

Did you try bcp, or Bulk insert? Do they fail as well?

More importantley why do you say moo?

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-22 : 02:56:13

quote:

Did you try bcp, or Bulk insert? Do they fail as well?

I have not tried these yet. I'm not sure how to replace a transform data task with using BCP.

quote:

More importantley why do you say moo?

Because cows are great.

-------
Moo.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-22 : 03:45:38

quote:
quote:
--------------------------------------------------------------------------------

More importantley why do you say moo?

--------------------------------------------------------------------------------

Because cows are great.

LOL... Brett, I think you must be disappointed that the origin of this phrase lies in some sort of cow fetish, rather than a philosophy behind the defense of one's opinions