January 18, 2021 at 1:23 am
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)
January 18, 2021 at 3:21 pm
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.
January 18, 2021 at 5:06 pm
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.
January 18, 2021 at 8:54 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply