Post reply

Updating columns in a remote server

  • October 14, 2005 at 12:00 pm

    #89484

    Hi All,

    I'm trying to update a column in a remote server with a value from another server.   Here is the t-sql I have:

    select * from openquery(RemoteServer, 'IF NOT EXISTS (SELECT name FROM MyDatabase.dbo.sysindexes

    WHERE name = ''ix_temp_dop'')

    CREATE UNIQUE INDEX ix_temp_dop ON MyDatabase.dbo.MyTable (MyIndexColumn)

    select @@error')

     

    update [RemoteServer].[MyDatabase].dbo.MyTable set MyColumn = d.MyColumn

    from [MyServer].[MyDatabase].dbo.MyTable d, [RemoteServer].[MyDatabase].dbo.MyTable l

    where l.MyIndexColumn = d.MyIndexColumn and l.MyIndexColumn = 'My Criteria'

     

    select * from openquery(RemoteServer, 'USE MyDatabase

    IF EXISTS (SELECT name FROM MyDatabase.dbo.sysindexes WHERE name = ''ix_temp_dop'')

    DROP INDEX MyDatabase.dbo.MyTable.ix_temp_dop

    select @@error')

     

    The update statement gives me the following error, evethough I'm creating an index in the table:

    Could not open table '"MyDatabase"."dbo"."MyTable"' 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:penRowset returned 0x80040e21:  [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_UPDATABILITY VAL...

    Does anyone have experience updating columns (possibly TEXT columns) in a remote server on a table that has no indexes?

    Thanks,

    Margarita

     

  • October 15, 2005 at 7:12 am

    #597505

    In order to update on a linked server there should be a primary key on the table to function as recordidentifier.

  • October 17, 2005 at 11:13 am

    #597691

    Yes.  Thanks for the reply.  I'm creating a temporary index for the table in the first statement (see the openquery).  However, it seems like once out of the openquery, the index does no longer exist.   I could maybe add the update statement inside the same openquery.   However, this requires [MyRemoteServer] to be linked to [MySever].

    Any other suggestions on how to do this remote update?

    Thanks!

  • October 17, 2005 at 11:35 am

    #597697

    Personally I prefer to create an StoredProc on the remote server and simply call the Proc from the local!!!

     


    * Noel

Viewing 4 posts - 1 through 4 (of 4 total)

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