Post reply

Linked Server problem

  • June 24, 2010 at 3:01 am

    #222540

    Hi,

    There is a problem with linked server.

    On the same server, we are creating the LinkedServers to point to some of the databases under the same instance.

    This setup is used since the prod and qa layers are different. Prod has different servers fro load balancing but QA has all

    under the same server.

    Locally, i have setup the Linked servers and performing an INSERT operation using a SQL script.It is working fine. And observation is

    am getting the number of rows affected while am trying to perform any DML operations through LNKED SERVERS.

    But , the same thing i am trying to INSERT using linked server on QA layer, it is giving me an error syaing that

    insert into dcdbserver2.dcdbset2.dbo.test

    select 2

    /*

    Server: Msg 7343, Level 16, State 2, Line 1

    OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[server1].[db1].[dbo].[test]'. Unknown provider error.

    [OLE/DB provider returned message: Cursor operation conflict]

    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned 0x80040e23: Unknown provider error.].

    */

    other observations on QA,

    1.Insert is the only one failing, but delete and update is working fine on QA.

    2. eventhough the DELETE and UPDATE are working but i am getting 0 rows affected. This setcount is making

    any difference over here.

    3. Also, i checked the DTC is also working or not. It is working fine.

    4. Also, the db user is having the db_owner on the respective catalog but don't know why it is gving that error.

    Can anybody figure out what the problem is ?? Any immediate fix for this.

    In my local all DML operations are working fine.

    Thanks in Advance

  • June 26, 2010 at 1:16 am

    #1185329

    Found it myself. Hope, this helps others!!

    I figured out why it is happening. At server level/instance level , the NOCOUNT option is checked.

    Now linked server is working for all the DML operations.

    And also, am getting number of rows affected.

    Small clarification, why NOCOUNT is stopping only INSERT operation and remaining other options are working fine.... Why is it so????

    Thanks!

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

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