Professional Web Applications Themes

Help with linked server qry (error doing delete!) - Microsoft SQL / MS SQL Server

Hi all, We are trying to insert rows from one table on an application db server into a destination table on a linked server. First we do a delete of matching rows, then an insert. But we get this error: Could not open table '"navoutboundcis"."dbo"."archivenav"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset ...

  1. #1

    Default Help with linked server qry (error doing delete!)

    Hi all,

    We are trying to insert rows from one table on an
    application db server into a destination table on a linked
    server.

    First we do a delete of matching rows, then an insert.
    But we get this error:

    Could not open table '"navoutboundcis"."dbo"."archivenav"'
    from OLE DB provider 'SQLOLEDB'. The provider could not
    support a row lookup position. The provider indicates that
    conflicts occurred with other properties or requirements.
    [OLE/DB provider returned message: Multiple-step OLE DB
    operation generated errors. Check each OLE DB status
    value, if available. No work was done.]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    IOpenRowset::OpenRowset returned 0x80040e21:
    [PROPID=DBPROP_BOOKMARKS VALUE=True
    STATUS=DBPROPSTATUS_CONFLICTING],
    [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
    STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
    VALUE=True STATUS=DBPROPSTATUS_OK],
    [PROPID=DBPROP_IRowsetLocate VALUE=True
    STATUS=DBPROPSTATUS_CONFLICTING],
    [PROPID=DBPROP_IRowsetChange VA...


    Can anyone help please?

    Here is the query that first builds a temp tbl on the
    local server using a mapping table that maps key columns,
    then deletes any matching rows from the destination table,
    then inserts the new rows:

    /*================================================= ========
    ====================================

    PROJECT: NAVSuite => NAVOutbound Month End feed May/June
    2003

    TASK: Feed data from NAVSuite table hst_CurrentPrice to
    NAV Outbound Archive_Nav


    DEVELOPER: Philip Livingstone & Marc Prum (CTS)

    NOTES: join the NAVOutbound table with NAVSuite,
    retrieving only records
    from NavSuite (the source) where NAVOutbound (the
    destination) is null
    thus indicating that those classes (nvs.classcode) of
    those funds (nvs.fundcode)
    have not been imported for that date (nvs.date)!

    IMPORTANT: we cannot import the same data TWICE, that's
    why we join using the distributed
    qry to NAV Outbound

    ================================================== =========
    ==================================*/

    -- first, retrieve the data that matches the mapping
    table...
    -- into a temp. table called #Source drop table #Source
    select
    hs.fundcode, hs.archivedate, fs.destclass,
    NAV = case Flag
    when 'B' then
    hs.CurrentPriceBase
    else
    hs.CurrentPriceNonBase
    end,
    hs.PriceDateAsOf, hs.SharesOutstanding,
    TNA = case Flag
    when 'B' then
    hs.TotalNetAssetsBase
    else
    hs.TotalNetAssetsNonBase
    end,
    hs.DistRate, hs.ipr, hs.LastUpdate, hs.UserID,
    NVSArchiveDate=
    cast(substring(convert(VARCHAR
    (10),hs.PriceDateAsOf,101),7,4)
    + Substring(convert(VARCHAR
    (10),hs.PriceDateAsOf,101),1,2)
    + substring(convert(VARCHAR
    (10),hs.PriceDateAsOf,101),4,2) AS CHAR(8))
    into dbo.#Source from (select
    map.destfund,
    map.destclass,
    map.sourceclass,
    f.fundcurrency,
    c.currency,
    Flag = case when f.fundcurrency = c.currency
    then 'B' else 'N' end
    from navsuite.dbo.ad_MapNVS2NVO map
    left outer join navsuite.dbo.ad_fund f
    on f.fundcode = map.destfund
    left outer join navsuite.dbo.ad_class c
    on c.fundcode = map.destfund and c.classcode =
    map.sourceclass
    Where
    f.fundcurrency is not null
    and c.classcode is not null) fs
    left outer join
    navsuite.dbo.hst_currentprice hs
    on fs.destfund = hs.fundcode
    and fs.sourceclass = hs.classcode
    and fs.destclass<>'N/A'
    and fundcode is not null

    -- 2, Perform delete of existing matching rows
    delete from nvs2nvo.navoutboundcis.dbo.archivenav
    where exists(select Source.nav, Source.sharesoutstanding,
    Source.tna
    From dbo.#Source Source
    Inner join nvs2nvo.navoutboundcis.dbo.archivenav Dest
    ON Source.fundcode = Dest.FundNumber
    AND Source.destclass = Dest.ClassCode
    and Source.nav = dest.nav
    and Source.SharesOutstanding= dest.sharesoutstanding
    and Source.tna = dest.TotalNetAssets)

    -- Perform Insert of new rows
    Insert into nvs2nvo.navoutboundcis.dbo.archivenav
    (
    FundNumber,
    ClassCode,
    NAV,
    SharesOutstanding,
    TotalNetAssets,
    DateTimeUpdate,
    Dates
    )
    Select Source.Fundcode,
    Source.destClass,
    Source.NAV,
    Source.SharesOutstanding,
    Source.TNA,
    Source.NVSArchiveDate,
    Source.PriceDateAsOf
    From dbo.#Source Source
    left outer join
    nvs2nvo.navoutboundcis.dbo.archivenav dest
    on dest.fundnumber = source.Fundcode
    and dest.classcode = source.destClass
    where dest.fundnumber is null
    and cast(DATEPART(mm, Source.NVSArchiveDate) as
    varchar(2)) + '/'
    + cast(DATEPART(dd, Source.NVSArchiveDate)
    as varchar(2)) + '/'
    + cast(DATEPART(yyyy,
    Source.NVSArchiveDate) as varchar(4))
    = cast(DATEPART(mm, Getdate()) as varchar
    (2)) + '/'
    + cast(DATEPART(dd, Getdate()) as varchar
    (2)) + '/'
    + cast(DATEPART(yyyy, Getdate()) as
    varchar(4))

    drop table dbo.#Source

    go

    thanks for any help or assistance.

    Philip

    Philip Guest

  2. #2

    Default Help with linked server qry (error doing delete!)


    Not shure.... but I think that If there Isn't a UNIQUE or
    PRIMARY KEY constraint on the remote table you get that
    error.....Check it and I hope it helps
    >-----Original Message-----
    >Hi all,
    >
    >We are trying to insert rows from one table on an
    >application db server into a destination table on a
    linked
    >server.
    >
    >First we do a delete of matching rows, then an insert.
    >But we get this error:
    >
    >Could not open
    table '"navoutboundcis"."dbo"."archivenav"'
    >from OLE DB provider 'SQLOLEDB'. The provider could not
    >support a row lookup position. The provider indicates
    that
    >conflicts occurred with other properties or requirements.
    >[OLE/DB provider returned message: Multiple-step OLE DB
    >operation generated errors. Check each OLE DB status
    >value, if available. No work was done.]
    >OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    >IOpenRowset::OpenRowset returned 0x80040e21:
    >[PROPID=DBPROP_BOOKMARKS VALUE=True
    >STATUS=DBPROPSTATUS_CONFLICTING],
    >[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
    >STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
    >VALUE=True STATUS=DBPROPSTATUS_OK],
    >[PROPID=DBPROP_IRowsetLocate VALUE=True
    >STATUS=DBPROPSTATUS_CONFLICTING],
    >[PROPID=DBPROP_IRowsetChange VA...
    >
    >
    >Can anyone help please?
    >
    >Here is the query that first builds a temp tbl on the
    >local server using a mapping table that maps key columns,
    >then deletes any matching rows from the destination
    table,
    >then inserts the new rows:
    >
    >/*================================================= =======
    =
    >====================================
    >
    > PROJECT: NAVSuite => NAVOutbound Month End feed
    May/June
    >2003
    >
    > TASK: Feed data from NAVSuite table hst_CurrentPrice to
    >NAV Outbound Archive_Nav
    >
    >
    > DEVELOPER: Philip Livingstone & Marc Prum (CTS)
    >
    > NOTES: join the NAVOutbound table with NAVSuite,
    >retrieving only records
    > from NavSuite (the source) where NAVOutbound (the
    >destination) is null
    > thus indicating that those classes (nvs.classcode) of
    >those funds (nvs.fundcode)
    > have not been imported for that date (nvs.date)!
    >
    > IMPORTANT: we cannot import the same data TWICE, that's
    >why we join using the distributed
    > qry to NAV Outbound
    >
    >================================================= =========
    =
    >==================================*/
    >
    >-- first, retrieve the data that matches the mapping
    >table...
    >-- into a temp. table called #Source drop table #Source
    >select
    > hs.fundcode, hs.archivedate, fs.destclass,
    > NAV = case Flag
    > when 'B' then
    > hs.CurrentPriceBase
    > else
    > hs.CurrentPriceNonBase
    > end,
    > hs.PriceDateAsOf, hs.SharesOutstanding,
    > TNA = case Flag
    > when 'B' then
    > hs.TotalNetAssetsBase
    > else
    > hs.TotalNetAssetsNonBase
    > end,
    > hs.DistRate, hs.ipr, hs.LastUpdate, hs.UserID,
    > NVSArchiveDate=
    > cast(substring(convert(VARCHAR
    >(10),hs.PriceDateAsOf,101),7,4)
    > + Substring(convert(VARCHAR
    >(10),hs.PriceDateAsOf,101),1,2)
    > + substring(convert(VARCHAR
    >(10),hs.PriceDateAsOf,101),4,2) AS CHAR(8))
    > into dbo.#Source from (select
    > map.destfund,
    > map.destclass,
    > map.sourceclass,
    > f.fundcurrency,
    > c.currency,
    > Flag = case when f.fundcurrency = c.currency
    >then 'B' else 'N' end
    > from navsuite.dbo.ad_MapNVS2NVO map
    > left outer join navsuite.dbo.ad_fund f
    >on f.fundcode = map.destfund
    > left outer join navsuite.dbo.ad_class c
    >on c.fundcode = map.destfund and c.classcode =
    >map.sourceclass
    > Where
    > f.fundcurrency is not null
    > and c.classcode is not null) fs
    > left outer join
    >navsuite.dbo.hst_currentprice hs
    > on fs.destfund = hs.fundcode
    > and fs.sourceclass = hs.classcode
    > and fs.destclass<>'N/A'
    > and fundcode is not null
    >
    >-- 2, Perform delete of existing matching rows
    >delete from nvs2nvo.navoutboundcis.dbo.archivenav
    >where exists(select Source.nav, Source.sharesoutstanding,
    >Source.tna
    >From dbo.#Source Source
    >Inner join nvs2nvo.navoutboundcis.dbo.archivenav Dest
    >ON Source.fundcode = Dest.FundNumber
    >AND Source.destclass = Dest.ClassCode
    >and Source.nav = dest.nav
    >and Source.SharesOutstanding= dest.sharesoutstanding
    >and Source.tna = dest.TotalNetAssets)
    >
    >-- Perform Insert of new rows
    >Insert into nvs2nvo.navoutboundcis.dbo.archivenav
    > (
    > FundNumber,
    > ClassCode,
    > NAV,
    > SharesOutstanding,
    > TotalNetAssets,
    > DateTimeUpdate,
    > Dates
    > )
    > Select Source.Fundcode,
    > Source.destClass,
    > Source.NAV,
    > Source.SharesOutstanding,
    > Source.TNA,
    > Source.NVSArchiveDate,
    > Source.PriceDateAsOf
    > From dbo.#Source Source
    > left outer join
    >nvs2nvo.navoutboundcis.dbo.archivenav dest
    > on dest.fundnumber = source.Fundcode
    > and dest.classcode = source.destClass
    > where dest.fundnumber is null
    > and cast(DATEPART(mm, Source.NVSArchiveDate) as
    >varchar(2)) + '/'
    > + cast(DATEPART(dd, Source.NVSArchiveDate)
    >as varchar(2)) + '/'
    > + cast(DATEPART(yyyy,
    >Source.NVSArchiveDate) as varchar(4))
    > = cast(DATEPART(mm, Getdate()) as varchar
    >(2)) + '/'
    > + cast(DATEPART(dd, Getdate()) as varchar
    >(2)) + '/'
    > + cast(DATEPART(yyyy, Getdate()) as
    >varchar(4))
    >
    >drop table dbo.#Source
    >
    >go
    >
    >thanks for any help or assistance.
    >
    >Philip
    >
    >.
    >
    Alfred Gerum Guest

  3. #3

    Default Re: Help with linked server qry (error doing delete!)

    I have a guess. Sorry do not have time to check it out.

    Commit the changes. make sure the copy operations have flushed to hard
    disk
    before you drop. Otherwise you could get a conflict, because reader
    process
    is holding the table while waiting for the lazy writer to stop goofing
    around.

    "Alfred Gerum" <alfredgerum.net> wrote in message
    news:096f01c34785$94db0530$a101280aphx.gbl...
    >
    > Not shure.... but I think that If there Isn't a UNIQUE or
    > PRIMARY KEY constraint on the remote table you get that
    > error.....Check it and I hope it helps
    >
    > >-----Original Message-----
    > >Hi all,
    > >
    > >We are trying to insert rows from one table on an
    > >application db server into a destination table on a
    > linked
    > >server.
    > >
    > >First we do a delete of matching rows, then an insert.
    > >But we get this error:
    > >
    > >Could not open
    > table '"navoutboundcis"."dbo"."archivenav"'
    > >from OLE DB provider 'SQLOLEDB'. The provider could not
    > >support a row lookup position. The provider indicates
    > that
    > >conflicts occurred with other properties or requirements.
    > >[OLE/DB provider returned message: Multiple-step OLE DB
    > >operation generated errors. Check each OLE DB status
    > >value, if available. No work was done.]
    > >OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    > >IOpenRowset::OpenRowset returned 0x80040e21:
    > >[PROPID=DBPROP_BOOKMARKS VALUE=True
    > >STATUS=DBPROPSTATUS_CONFLICTING],
    > >[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
    > >STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
    > >VALUE=True STATUS=DBPROPSTATUS_OK],
    > >[PROPID=DBPROP_IRowsetLocate VALUE=True
    > >STATUS=DBPROPSTATUS_CONFLICTING],
    > >[PROPID=DBPROP_IRowsetChange VA...
    > >
    > >
    > >Can anyone help please?
    > >
    > >Here is the query that first builds a temp tbl on the
    > >local server using a mapping table that maps key columns,
    > >then deletes any matching rows from the destination
    > table,
    > >then inserts the new rows:
    > >
    > >/*================================================= =======
    > =
    > >====================================
    > >
    > > PROJECT: NAVSuite => NAVOutbound Month End feed
    > May/June
    > >2003
    > >
    > > TASK: Feed data from NAVSuite table hst_CurrentPrice to
    > >NAV Outbound Archive_Nav
    > >
    > >
    > > DEVELOPER: Philip Livingstone & Marc Prum (CTS)
    > >
    > > NOTES: join the NAVOutbound table with NAVSuite,
    > >retrieving only records
    > > from NavSuite (the source) where NAVOutbound (the
    > >destination) is null
    > > thus indicating that those classes (nvs.classcode) of
    > >those funds (nvs.fundcode)
    > > have not been imported for that date (nvs.date)!
    > >
    > > IMPORTANT: we cannot import the same data TWICE, that's
    > >why we join using the distributed
    > > qry to NAV Outbound
    > >
    > >================================================= =========
    > =
    > >==================================*/
    > >
    > >-- first, retrieve the data that matches the mapping
    > >table...
    > >-- into a temp. table called #Source drop table #Source
    > >select
    > > hs.fundcode, hs.archivedate, fs.destclass,
    > > NAV = case Flag
    > > when 'B' then
    > > hs.CurrentPriceBase
    > > else
    > > hs.CurrentPriceNonBase
    > > end,
    > > hs.PriceDateAsOf, hs.SharesOutstanding,
    > > TNA = case Flag
    > > when 'B' then
    > > hs.TotalNetAssetsBase
    > > else
    > > hs.TotalNetAssetsNonBase
    > > end,
    > > hs.DistRate, hs.ipr, hs.LastUpdate, hs.UserID,
    > > NVSArchiveDate=
    > > cast(substring(convert(VARCHAR
    > >(10),hs.PriceDateAsOf,101),7,4)
    > > + Substring(convert(VARCHAR
    > >(10),hs.PriceDateAsOf,101),1,2)
    > > + substring(convert(VARCHAR
    > >(10),hs.PriceDateAsOf,101),4,2) AS CHAR(8))
    > > into dbo.#Source from (select
    > > map.destfund,
    > > map.destclass,
    > > map.sourceclass,
    > > f.fundcurrency,
    > > c.currency,
    > > Flag = case when f.fundcurrency = c.currency
    > >then 'B' else 'N' end
    > > from navsuite.dbo.ad_MapNVS2NVO map
    > > left outer join navsuite.dbo.ad_fund f
    > >on f.fundcode = map.destfund
    > > left outer join navsuite.dbo.ad_class c
    > >on c.fundcode = map.destfund and c.classcode =
    > >map.sourceclass
    > > Where
    > > f.fundcurrency is not null
    > > and c.classcode is not null) fs
    > > left outer join
    > >navsuite.dbo.hst_currentprice hs
    > > on fs.destfund = hs.fundcode
    > > and fs.sourceclass = hs.classcode
    > > and fs.destclass<>'N/A'
    > > and fundcode is not null
    > >
    > >-- 2, Perform delete of existing matching rows
    > >delete from nvs2nvo.navoutboundcis.dbo.archivenav
    > >where exists(select Source.nav, Source.sharesoutstanding,
    > >Source.tna
    > >From dbo.#Source Source
    > >Inner join nvs2nvo.navoutboundcis.dbo.archivenav Dest
    > >ON Source.fundcode = Dest.FundNumber
    > >AND Source.destclass = Dest.ClassCode
    > >and Source.nav = dest.nav
    > >and Source.SharesOutstanding= dest.sharesoutstanding
    > >and Source.tna = dest.TotalNetAssets)
    > >
    > >-- Perform Insert of new rows
    > >Insert into nvs2nvo.navoutboundcis.dbo.archivenav
    > > (
    > > FundNumber,
    > > ClassCode,
    > > NAV,
    > > SharesOutstanding,
    > > TotalNetAssets,
    > > DateTimeUpdate,
    > > Dates
    > > )
    > > Select Source.Fundcode,
    > > Source.destClass,
    > > Source.NAV,
    > > Source.SharesOutstanding,
    > > Source.TNA,
    > > Source.NVSArchiveDate,
    > > Source.PriceDateAsOf
    > > From dbo.#Source Source
    > > left outer join
    > >nvs2nvo.navoutboundcis.dbo.archivenav dest
    > > on dest.fundnumber = source.Fundcode
    > > and dest.classcode = source.destClass
    > > where dest.fundnumber is null
    > > and cast(DATEPART(mm, Source.NVSArchiveDate) as
    > >varchar(2)) + '/'
    > > + cast(DATEPART(dd, Source.NVSArchiveDate)
    > >as varchar(2)) + '/'
    > > + cast(DATEPART(yyyy,
    > >Source.NVSArchiveDate) as varchar(4))
    > > = cast(DATEPART(mm, Getdate()) as varchar
    > >(2)) + '/'
    > > + cast(DATEPART(dd, Getdate()) as varchar
    > >(2)) + '/'
    > > + cast(DATEPART(yyyy, Getdate()) as
    > >varchar(4))
    > >
    > >drop table dbo.#Source
    > >
    > >go
    > >
    > >thanks for any help or assistance.
    > >
    > >Philip
    > >
    > >.
    > >

    Jay Schmitendorf Guest

Similar Threads

  1. Delete linked MS Office and PDF files
    By Ted Fitzpatrick in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: August 8th, 05:07 AM
  2. Replies: 0
    Last Post: March 18th, 09:53 PM
  3. Delete unused or not linked files
    By nikespex@hotmail.com in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: February 16th, 08:15 PM
  4. Replies: 4
    Last Post: June 11th, 12:16 PM
  5. Replies: 3
    Last Post: August 6th, 07:03 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