SQL Server Performance

Linked Server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by entela, Feb 2, 2007.

  1. entela New Member

    I'm using SQL Server 2005 and I'm trying to create a linked server to SQL Server 2000.
    Using the wizard everythink seems ok.
    When I use the link server through a query I got the message.

    Error:-2147217900: [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI" for linked server "Server_Name"
    returned message "Row handle referred to a deleted row or a row marked for deletion.".

    Does any body face this problem?
    I'll appreciate your help

    Thanks a lot
    Entela

  2. satya Moderator

    Are you using SET XACT_ABORT and DISTRIBUTED TRANSACTION statement before querying sql statements between the linked servers.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. entela New Member

    HI,

    I'm not using these statements, should I?

    Thanks
    Entela
  4. entela New Member

    Hi everyone,

    I got the following message:

    {"Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "GIS-GPSDB1". A nested transaction was required because the XACT_ABORT option was set to OFF.
    OLE DB provider "SQLNCLI" for linked server "GIS-GPSDB1" returned message "Cannot start more transactions on this session."." }

    Using a store procedure I want to make an insert into a table on linked server.
    I'm not using transactions, so I couldn;t set the option XACT_ABORT ON.
    Can anybaody help me with this issue?

    Is really emergent.

    Best Regards
    Entela

  5. entela New Member

    Hi,

    Actually I changed my store procdure and I'm setting XACT_Abort ON, but I'm getting the following message:

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


    Please help me out with this issue.

    Thanks Entela
  6. ThakuSa New Member

    I am running a query on SQL 2005 server to update a table on SQL 2000 using linked server technique. And I am getting following message:
    OLE DB provider "SQLNCLI" for linked server "Server_Name" returned message. "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
    Msg 16955, Level 16, State 2, Line 1 Could not create an acceptable cursor.
    Anyone knows what is the issue?
    Thanks,
    ThakuSa
  7. MohammedU New Member

    What Service Pack (build) sql 2000 running on?

    Check the following article...

    FIX: Cursor Created on Linked Server That Requests Meta-Data May Cause Handled Access Violation
    http://support.microsoft.com/kb/302477


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  8. alzdba Member

  9. entela New Member

    Hi,

    I'm using SQL Server 2000 with SP4 , and there is the same problem, I can't insert, update or delete from SQL Server 2005 64 bit into SQL Server 2000.

    Please I need help.

    Regards
    Entela
  10. alzdba Member

    It works fine overhere ...
    SQL2005 EE X64 -> SQL2000 Std 32-bit

    SET XACT_ABORT ON

    BEGIN DISTRIBUTED transaction


    INSERT INTO [mylinkedserver].[DDBAServerPing].[dbo].[T_Server_Ping]
    ([TsCrea]
    ,[UserCrea]
    ,[ServerInfo_ElapsTime_ms])
    VALUES
    ('2006-04-05'
    ,'alzdba'
    ,5000 )


    commit transaction


    SELECT * FROM [mylinkedserver].[DDBAServerPing].[dbo].[T_Server_Ping]
  11. entela New Member

    Hi,

    Thanks for the replay and I'm sorry i'm sending the mail too late.

    I tried the same you told me and I got the same error message:

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

    I couldn't understand were the problem is.

    Everybody sad should work, but i'm not getting any luck with it.

    Can it be somethink else that I haven't try?

    Thanks a lot
    Entela
  12. MohammedU New Member

  13. alzdba Member

  14. entela New Member

    Hi,

    Yes I run the instcat.sql after I installed the SP4.

    Thanks a lot
    Entela
  15. kiasu81 New Member

    quote:Originally posted by ThakuSa

    I am running a query on SQL 2005 server to update a table on SQL 2000 using linked server technique. And I am getting following message:
    OLE DB provider "SQLNCLI" for linked server "Server_Name" returned message. "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
    Msg 16955, Level 16, State 2, Line 1 Could not create an acceptable cursor.
    Anyone knows what is the issue?
    Thanks,
    ThakuSa

    create unique index at the table on sql2k
  16. satya Moderator

    That depends on the transaction handling from ADO (whatever you use) and I suggeste having primary key(PK) should be your answer, all tables should have one unless a specific reason not to.




    quote:Originally posted by entela

    HI,

    I'm not using these statements, should I?

    Thanks
    Entela

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page