none
Question about updating Linked Server's Table. on SQL Server 2000 RRS feed

  • 問題

  • Hi

    I am having problem on one of my stored procedure.
    The stored procedure need to do some Join from the currect database and update the tables from the linked server. And then update the data from the current table as well.

    When I try to execute the stored procedure it returns the following error.

    [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:SurprisepenRowset 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...
    Msg 7306, Level 16, State 2, Procedure UpdateLinkedServerTable, Line 52
    Could not open table '"DBName"."dbo"."DBTable"' 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.


    I would be grateful, if anyone could give me some suggestion whether there are some settings I need to do before I can update the table that is on the linked server.

    Thanks in advance.
    2008年6月30日 上午 08:21

解答

所有回覆

  • What kind of linked server? Possible to post your code? Can you update table on linked server from outside of stored procedure?

    2008年6月30日 下午 03:59
  • Hi Rmiao,

    Thanks for your reply.

    Sorry, I am not sure what do you mean by "What kind of linked server"?


    Hereis my situation

    We have 2 SQL Server:
    DataDB_Server <--- This stored the main data, which will be use by different application
    WebDB_Server <--- This stored the data that user enter from the website, which will then process/modify and finally update to the DataDB_Server.

    The login for Linked server is "sa"

    here it is the stored procedure
    Code Snippet

    CREATE PROCEDURE [dbo].[UpdateRemoteTable]GO


        -- Add the parameters for the stored procedure here
        @Ref_ID INT = 0,
        @CustomerCode VARCHAR(10)

    AS
    BEGIN
        DECLARE @OldPlan VARCHAR(10)
        DECLARE @NewPlan VARCHAR(10)
        SELECT @OldPlan=OldPlan, @NewPlan=NewPlan
        FROM TransactionTable
        WHERE Ref_ID=@Ref_ID AND CustomerCode=@CustomerCode


        UPDATE [LinkedServer].DataDB_Server.dbo.ProductCustomer
        SET Plan= @NewPlan
        WHERE CustomerCode = @CustomerCode AND Plan = @OldPlan


        UPDATE [LinkedServer].DataDB_Server.dbo.ProductCustomer
        SET Normal= ROUND(p.Normal/100,5),
            Silver = ROUND(p.Silver/100,5),
            Gold = ROUND(p.Gold/100,5)
        FROM TransactionTable t
        INNER JOIN [LinkedServer].DataDB_Server.dbo.ProductCustomer p
        ON c.Customercode= p.CustomerCode AND p.Plan = t.Plan AND p.ProductCode         = t.ProductCode
        WHERE t.Ref = @Ref_ID AND Completed=0 AND Client_Action = 'U'


        UPDATE TransactionTable
        SET Completed=1,
            DateOfCompletion = GETDATE()
        WHERE Ref_ID=@Ref_ID AND CustomerCode=@CustomerCode

    END

    I can update the linked server's table outside the stored procedure. As soon as I place the query into stored procedure, I can run it once, then I do now work again. Not sure why.

    Hope this could help you find locate what is my problem.

    Thanks in advance.
    2008年7月1日 上午 08:37
  • So you link to another sql server. Ever tried with openquery instead of four party name?

    2008年7月1日 下午 02:33
  • Hi Rmiao

    Thanks for your reply. Yeah, I linked to another SQL Server.

    I have not try OpenQuery before.

    Will try it out later.

    Thanks for your information

    2008年7月1日 下午 02:44
  • Hi Rmiao,

    Thanks for your information. I think it should work =).

    Just found a document about OpenQuery, to do the update query.
    http://support.microsoft.com/kb/270119

    Thanks a lots.
    2008年7月2日 上午 09:00