Linked Server Insert error

Question

Post reply

Linked Server Insert error

natigsqlserver

SSC-Addicted

Points: 488

January 18, 2021 at 1:23 am

#3834423

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)

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

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.

eduardoalmeidabr Old Hand Points: 350 More actions · Answer 3

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?

This reply was modified 3 years, 10 months ago by eduardoalmeidabr.
This reply was modified 3 years, 10 months ago by eduardoalmeidabr.