Linked Server Insert error

  • Hello

    i have linked server

    one server in cloud one is local

    from local my select query in linked server success working

    but insert give me this error

    OLE DB provider "MSOLEDBSQL" for linked server "LINKEDSERVERNAME" returned message "Cursor operation conflict".

    Msg 7343, Level 16, State 2, Line 7

    The OLE DB provider "MSOLEDBSQL" for linked server "LINKEDSERVERNAME" could not INSERT INTO table "[MSOLEDBSQL]".

     

    query is that

    INSERT      OPENQUERY (
    [LINKEDSERVERNAME], 'SELECT [ACCNT_CODE]
    ,[PERIOD]
    ,[TRANS_DATETIME]
    ,[JRNAL_NO]
    ,[JRNAL_LINE]
    ,[AMOUNT]
    ,[D_C]
    ,[ALLOCATION]
    ,[JRNAL_TYPE]
    ,[JRNAL_SRCE]
    ,[TREFERENCE]
    ,[DESCRIPTN]
    ,[ENTRY_DATETIME]
    ,[ENTRY_PRD]
    ,[DUE_DATETIME]
    ,[ALLOC_REF]
    ,[ALLOC_DATETIME]
    ,[ALLOC_PERIOD]
    ,[ASSET_IND]
    ,[ASSET_CODE]
    ,[ASSET_SUB]
    ,[CONV_CODE]
    ,[CONV_RATE]
    ,[OTHER_AMT]
    ,[OTHER_DP]
    ,[CLEARDOWN]
    ,[REVERSAL]
    ,[LOSS_GAIN]
    ,[ROUGH_FLAG]
    ,[IN_USE_FLAG]
    ,[ANAL_T0]
    ,[ANAL_T1]
    ,[ANAL_T2]
    ,[ANAL_T3]
    ,[ANAL_T4]
    ,[ANAL_T5]
    ,[ANAL_T6]
    ,[ANAL_T7]
    ,[ANAL_T8]
    ,[ANAL_T9]
    ,[POSTING_DATETIME]
    ,[ALLOC_IN_PROGRESS]
    ,[HOLD_REF]
    ,[HOLD_OP_ID]
    ,[BASE_RATE]
    ,[BASE_OPERATOR]
    ,[CONV_OPERATOR]
    ,[REPORT_RATE]
    ,[REPORT_OPERATOR]
    ,[REPORT_AMT]
    ,[MEMO_AMT]
    ,[EXCLUDE_BAL]
    ,[LE_DETAILS_IND]
    ,[CONSUMED_BDGT_ID]
    ,[CV4_CONV_CODE]
    ,[CV4_AMT]
    ,[CV4_CONV_RATE]
    ,[CV4_OPERATOR]
    ,[CV4_DP]
    ,[CV5_CONV_CODE]
    ,[CV5_AMT]
    ,[CV5_CONV_RATE]
    ,[CV5_OPERATOR]
    ,[CV5_DP]
    ,[LINK_REF_1]
    ,[LINK_REF_2]
    ,[LINK_REF_3]
    ,[ALLOCN_CODE]
    ,[ALLOCN_STMNTS]
    ,[OPR_CODE]
    ,[SPLIT_ORIG_LINE]
    ,[VAL_DATETIME]
    ,[SIGNING_DETAILS]
    ,[INSTLMT_DATETIME]
    ,[PRINCIPAL_REQD]
    ,[BINDER_STATUS]
    ,[AGREED_STATUS]
    ,[SPLIT_LINK_REF]
    ,[PSTG_REF]
    ,[TRUE_RATED]
    ,[HOLD_DATETIME]
    ,[HOLD_TEXT]
    ,[INSTLMT_NUM]
    ,[SUPPLMNTRY_EXTSN]
    ,[APRVLS_EXTSN]
    ,[REVAL_LINK_REF]
    ,[SAVED_SET_NUM]
    ,[AUTHORISTN_SET_REF]
    ,[PYMT_AUTHORISTN_SET_REF]
    ,[MAN_PAY_OVER]
    ,[PYMT_STAMP]
    ,[AUTHORISTN_IN_PROGRESS]
    ,[SPLIT_IN_PROGRESS]
    ,[VCHR_NUM]
    ,[JNL_CLASS_CODE]
    ,[ORIGINATOR_ID]
    ,[ORIGINATED_DATETIME]
    ,[LAST_CHANGE_USER_ID]
    ,[LAST_CHANGE_DATETIME]
    ,[AFTER_PSTG_ID]
    ,[AFTER_PSTG_DATETIME]
    ,[POSTER_ID]
    ,[ALLOC_ID]
    ,[JNL_REVERSAL_TYPE]
    FROM [SunFinanceIntegration].[dbo].[NOC_A_SALFLDG]
    ')

    values (58009,2020012,'2002-02-02',12,1,-372.00,'D','a','sql','sql','payroll','a','2020-02-02',2021001,'2020-02-02',0,null,0,'','','','azn',1.70,-219.00,3,
    000,'','','y','','z','z','z','z',20500,'z','z','','z','z',null,'',0,'',0.00,'*','*',0.00,'*',0.00,0.00,'','',0,'',0.00,0.00,'*',2,'',0.00,0.00,'*',2,
    null,null,null,null,null,null,0,null,null,null,0,'',1,null,null,0,null,null,null,0,0,null,null,null,null,0,null,0,0,null,null,'sql','2020-02-02','sql','2020-02-02',null,
    '2020-02-02',null,null,0)









  • What happens if you do that insert on the other server without using the linked server?

    The error "Cursor operation conflict" makes me think that there MIGHT be a trigger on the remote table and it is causing the failure.

    I would test the insert with the exact same parameters on the remote server and see what happens.

    ALTERNATELY, a quick google makes it look like you need to SET NOCOUNT ON - https://www.sqlservercentral.com/forums/topic/cursor-operation-conflict

  • and be careful with inserting data onto remote server through a linked server . it is EXTREMELLY slow as it does it a record at the time.

    if there is any type of volume it is better to use SSIS/C# to do the data transfer or to execute a SP on the REMOTE server to retrieve the data from the local server and insert it there.

  • Is the linked server also SQL Server? If it is, you can do it without OPENQUERY(). You just need to  use the full name of the table "LINKED_SERVER"."DATABASE"."schema"."TABLE".

    And why don't you drop the empty columns and null columns from your query? Is there any constraint?

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

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