Professional Web Applications Themes

distributed partitioned view?? - Microsoft SQL / MS SQL Server

it seems that i have something missing when updating a distributed partitioned view...using sqlserver 2000 ent edition, win 2000 server.... i get ==> union all view 'customers' in not updateable because the defintion contains a disallowed construct (42000,4416) has anyone had this problem?? thx, chester -- On Server1 - tucker drop TABLE dbo.Customers_tucker2 CREATE TABLE Customers_tucker2 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 99999), cust_name varchar(30) NOT NULL, state_cd char(2) NOT NULL ) insert into customers_tucker2 values (11101, 'chet1', 'tx') insert into customers_tucker2 values (11102, 'chet2', 'xx') insert into customers_tucker2 values (11103, 'chet3', 'tv') insert into customers_tucker2 values ...

  1. #1

    Default distributed partitioned view??

    it seems that i have something missing when updating a distributed
    partitioned view...using sqlserver 2000 ent edition, win 2000 server....

    i get ==> union all view 'customers' in not updateable because the
    defintion contains
    a disallowed construct (42000,4416)

    has anyone had this problem?? thx, chester



    -- On Server1 - tucker
    drop TABLE dbo.Customers_tucker2
    CREATE TABLE Customers_tucker2
    (CustomerID INTEGER PRIMARY KEY
    CHECK (CustomerID BETWEEN 1 AND 99999),
    cust_name varchar(30) NOT NULL,
    state_cd char(2) NOT NULL
    )

    insert into customers_tucker2 values (11101, 'chet1', 'tx')
    insert into customers_tucker2 values (11102, 'chet2', 'xx')
    insert into customers_tucker2 values (11103, 'chet3', 'tv')
    insert into customers_tucker2 values (11104, 'chet4', 'vv')
    insert into customers_tucker2 values (11105, 'chet5', 'zz')


    -- On Server2 - jag
    drop TABLE Customers_jag;

    CREATE TABLE Customers_jag
    (CustomerID INTEGER PRIMARY KEY
    CHECK (CustomerID BETWEEN 100000 AND 999999),
    cust_name varchar(30) NOT NULL,
    state_cd char(2) NOT NULL
    )


    insert into customers_jag values (111001, 'chet41', 'tt')
    insert into customers_jag values (111002, 'chet42', 'rr')
    insert into customers_jag values (111003, 'chet43', 'dd')
    insert into customers_jag values (111004, 'chet44', 'gg')
    insert into customers_jag values (111005, 'chet45', 'ff')

    /*************************************
    run the following on both servers
    ************************************/

    drop view customers
    go

    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    go
    select top 2 * from jag.pubs.dbo.customers_jag
    select top 2 * from tucker2.pubs.dbo.customers_tucker2

    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    SET XACT_ABORT ON
    go
    create view customers
    as
    select customerid, cust_name, state_cd
    from tucker2.pubs.dbo.customers_tucker2
    union all
    select customerid, cust_name, state_cd
    from jag.pubs.dbo.customers_jag



    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    go
    select min(customerid), max(customerid) from customers

    select * from customers
    where customerid in (111005,11101)

    /* the following 3 update/insert/del fail with msg:
    union all view 'customers' in not updateable because the defintion contains
    a disallowed construct (42000,4416)
    */

    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    go
    SET XACT_ABORT ON
    update customers
    set state_cd = 'la'
    where customerid = 11101

    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    go
    insert into customers values ( 1122, 'chet', 'tx')

    set ansi_nulls, ansi_warnings, ANSI_PADDING ON
    go
    SET XACT_ABORT ON
    delete from customers
    where customerid = 11101



    chet Guest

  2. #2

    Default Re: distributed partitioned view??

    You might check that the collations are the same on the
    two databases (or add explicit collation specifications to the
    varchar and char columns). Also be sure the ansi_nulls (and
    others maybe) settings matched when the tables were created,
    not just afterwards.

    -- Steve Kass
    -- Drew University
    -- Ref: E8589052-7502-411C-8C9D-FEE1DC912874

    chet gwin wrote:
     

    Steve Guest

  3. #3

    Default Re: distributed partitioned view??

    that was it...thank you sooo much....(i set ansi nulls, etc BEFORE i created
    the table; the server collations were both set the same)....

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    contains 
    >[/ref]


    chet Guest

Similar Threads

  1. db2move and partitioned db on v8.1
    By Prince in forum IBM DB2
    Replies: 7
    Last Post: October 14th, 05:21 PM
  2. Replies: 5
    Last Post: October 6th, 11:43 PM
  3. Partitioned views
    By Amrit in forum IBM DB2
    Replies: 2
    Last Post: August 25th, 07:06 PM
  4. Partitioned View Question
    By Jane Kelly in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 12th, 11:40 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139