none
Multiple-step OLE DB operation generated errors

    Question

  • Hello....

    I have two linked server... (ServerB and ServerC) which reside on ServerA.  I am able to connect to the remote database using "Select" statements without any issues.

    When I run this query, It is successful:

    delete [SERVERB].MyDatabase.dbo.TableName
    from [SERVERB].MyDatabase.dbo.TableName t1
     Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and
         t1.EmployeeNumber = t2.EmployeeNumber and
         t1.AccountNumber = t2.AccountNumber)
    where t2.ID is null;

    However, when I change [SERVERB] to [SERVERC], I receive two errors:

    "Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

    And

    OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch"  operations, so I know the connection is valid.

    Any ideas?

    Forch

     

    Tuesday, May 16, 2006 3:05 PM

All replies

  • Sorry... I forgot to mention that all three servers are SQL Server 2005 x64 edition with SP1.

    Forch

    Tuesday, May 16, 2006 3:08 PM
  • HI,

    Did you actually find a solution for this. I actually ran into the same problem with servers at my work.
    Any help in this regard would be appreciated.

    Thanks
    Techiedella

    Friday, January 18, 2008 10:07 PM
  • Though I don't have a solution, have you tried simply dropping and then adding the linked server?

     

    Saturday, January 19, 2008 1:07 AM
  • Thanks for the response. I did try that too unforunately that didnt work either.
    So I might work around this problem by just deleting records directly on the remoteserver instead of keeping the connection open. I will keep you posted if I find a solution.

    Thanks
    Tuesday, January 22, 2008 8:26 PM
  •  

    Did any one find the solution for this ? I am trying to delete and insert records from one database to another using linked server.Some times it is working fine but when i am trying to insert data from multiple tables its throwing this error

     

    "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

     

    Please let me know asap

    Thursday, April 17, 2008 5:27 AM
  • Multi-step OLE DB error is very generic.  What you would need is to debug the SQL you are sending across to see what errored out, or attack SQL Profiler and make sure to enable user message logging.  Look for the error, and adjust your environment accordingly.  As you are trying to delete and insert:  for delete, make sure you have permissions; for insert, make sure you are not violating any constraints.

    Hope that helps,

    John
    Thursday, April 17, 2008 5:08 PM
  • First, run instcat.sql from the Install folder on that installation of Sql Server.

    Don't pull the instcat.sql from your local machine, or from anywhere else.  Pull the file from the server that you need to apply it to.  That file is updated from service pack to service pack and isn't necessarily applied.  There's a kbase article on that, not sure of the number right off hand.  You want to make ABSOLUTELY sure that you pull the correct version.

    If you are modifying a table on the remote server (insert, update, or delete) you must have a declared primary key on that table.  Not a unique index.  Not a unique clustered index.  A declared Primary Key constraint (which will make a unique index, but you still need to have a PK).

    chris.
    • Proposed as answer by Qasim2 Saturday, October 23, 2010 12:32 PM
    Monday, July 21, 2008 2:20 PM
  • First, run instcat.sql from the Install folder on that installation of Sql Server.

    Don't pull the instcat.sql from your local machine, or from anywhere else.  Pull the file from the server that you need to apply it to.  That file is updated from service pack to service pack and isn't necessarily applied.  There's a kbase article on that, not sure of the number right off hand.  You want to make ABSOLUTELY sure that you pull the correct version.

    If you are modifying a table on the remote server (insert, update, or delete) you must have a declared primary key on that table.  Not a unique index.  Not a unique clustered index.  A declared Primary Key constraint (which will make a unique index, but you still need to have a PK).

    chris.
    thanks for an idea!
    but it's working also with an unique nonclustered key
    • Proposed as answer by Qasim2 Saturday, October 23, 2010 12:32 PM
    Thursday, September 10, 2009 1:43 PM