Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2002
    Posts
    16

    Exclamation Unanswered: Very urgent : bcp or dts data to partitioned views

    someone please tell me if i can bcp or dts data in to partition views
    it is failing with the error the bulk operation does not support this

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Have you read the following:

    A view is considered an updatable partitioned view if:

    The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

  3. #3
    Join Date
    Mar 2002
    Posts
    16
    Yes I am using union all with two different base tables (local tables). I am able to insert, update,delete and bulk insert. Only bcp and dts won't work.

    Originally posted by rnealejr
    Have you read the following:

    A view is considered an updatable partitioned view if:

    The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Post the exact error you are receiving.

  5. #5
    Join Date
    Mar 2002
    Posts
    16
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Partitioned view 'tbl_MLS
    _Leads_Test1' is not updatable as the target of a bulk operation.

    Originally posted by rnealejr
    Post the exact error you are receiving.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Did you use the CHECK_CONSTRAINTS option ?

  7. #7
    Join Date
    Mar 2002
    Posts
    16
    yep. I did with bcp -hCHECK_CONSTRAINTS.. still the same error
    Originally posted by rnealejr
    Did you use the CHECK_CONSTRAINTS option ?

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Did you always use that option in your bcp statement ? Also, did you try the bcp (with the CHECK_CONSTRAINTS option set) before the successful bulk insert ?

  9. #9
    Join Date
    Mar 2002
    Posts
    16
    Initially I didn't use that option.. i used it later. i tried the bcp after the bulk insert.
    BCP works with -hFIRE_TRIGGERS option with isntead of trigger( I'd like to avoid firing the triggers for every row).
    BCP does not work with -hCHECK_CONSTRAINTS ( without the isntead of trigger) and with jsut the cehck cosntraint on the table

    Originally posted by rnealejr
    Did you always use that option in your bcp statement ? Also, did you try the bcp (with the CHECK_CONSTRAINTS option set) before the successful bulk insert ?

  10. #10
    Join Date
    Mar 2002
    Posts
    16
    Does it matter if bulk insert is used before bcp?Thanks

    Originally posted by rnealejr
    Did you always use that option in your bcp statement ? Also, did you try the bcp (with the CHECK_CONSTRAINTS option set) before the successful bulk insert ?

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    It can - when you used the bulk insert - did you use CHECK_CONSTRAINTS ?

  12. #12
    Join Date
    Mar 2002
    Posts
    16
    Yes. I tried both CHECK_CONSTRAINTS and FIRE_TRIGGERS.
    Can you please explain me how this impacts bcp?
    Thank you
    Originally posted by rnealejr
    It can - when you used the bulk insert - did you use CHECK_CONSTRAINTS ?

  13. #13
    Join Date
    Mar 2002
    Posts
    16
    I TAKE IT BACK. ONLY FIRE_TRIGGERS WORK WITH BULK INSERT. I JUST TRIED CHECK_COSNTRAINTS , IT DOES NOT WORK. ANY IDEA?

    Originally posted by Lakshmi
    Yes. I tried both CHECK_CONSTRAINTS and FIRE_TRIGGERS.
    Can you please explain me how this impacts bcp?
    Thank you

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    It is possible that your partitioned view has been marked as read-only by these operations. You will need to drop and recreate the check constraint for your partitioned view. Then use bcp with CHECK_CONSTRAINTS.

  15. #15
    Join Date
    Mar 2002
    Posts
    16
    I'll try that., but I am able to do regular insert and update using the view which indicates the view is not read-only. Can you please tell me if BCP is expected to work with CHECK_CONSTRAINTS and if yes why this error might be occuring?Thanks
    Originally posted by rnealejr
    It is possible that your partitioned view has been marked as read-only by these operations. You will need to drop and recreate the check constraint for your partitioned view. Then use bcp with CHECK_CONSTRAINTS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •