Post reply

Delete from table using linked server

  • May 13, 2002 at 9:55 am

    #101147

    I have two SQL Servers (7.0 SP3). One of them is a linked server to the other. On the first server I have a view which is defined as:

    SELECT * FROM LinkedServer.DB.dbo.SomeTable from the other server. I can insert and select data using this view but I cannot find a way to delete or update records in the view. I get the following error:

    "The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements. "

    Has anyone solved such a problem?

    In the Security tab of the linked server definition I mapped all users to 'sa' in order to avoid permissions problems.

    TIA

  • May 13, 2002 at 2:45 pm

    #429044

    Sounds like the issue related to a unique index requirement mentioned here http://support.microsoft.com/default.aspx?scid=kb;EN-US;q270119.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • May 16, 2002 at 4:47 am

    #429239

    Thanks James,

    Your answer was correct. This was a problem of missing unique index on the table from linked server. It means that the article from Microsoft applies not only to OpenQuery but also to views when using linked servers (or maybe it's the same in this case). Since I cannot create an unique index on this table (because it is used for data import and can contain duplicates), I have to add an artificial identity column (and then UPDATE and DELETE works) but this causes another problems related to BULK INSERT command and Format Files. I will post another question about it because it looks like a bug in SQL Server 7.0.

    Thanks again

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply