I am using SQL 2012.

The solution was to use turn on journaling. It's an unnecessary overhead for this purpose, but the only option. MS needs to be able to roll back it appears.


However, I will try your options below.

Thank you.
Darryl.

Sent from my iPad

On Aug 13, 2015, at 1:09 AM, Gad Miron <gadmiron@xxxxxxxxx> wrote:

Darryl

1. What SQL server version do you use?
2. The following settings work fine for me on SQL Server 2008 R2
a. Provider: IBM DB2 for i IBMDASQL OLE DB Provider
b. Server options:
Collation Compatible False
Data Access True
RPC True
RPC Out True
Use Remote collation True
Collation Name
Connection Timeout 0
Query Timeout 0
Distributor False
Publisher False
Subscriber False
Lazy Schema Validation False
Enable Promotion of Distributed Tra False


I believe the important settings are RPC and RPC Out


The code looks like

insert into as400_prod.proddb.MMSLIBD.BOAK01P

select [trans_no],[trans_internal_no],[trans_creation_date],

[trans_creation_time],[trans_cnfrm_date],[trans_confirm_time],

[trans_code],[trans_error_code],[trans_mont_acc],[trans_acc],

[trans_bo_cust_no],[trans_bo_inv_no],

Case when len([trans_line_no]) < 3 then '0'+[trans_line_no] else
[trans_line_no] end,

[free_fld],

[trans_credit_flag],[trans_no_id_tagmul],[trans_src],[trans_start_mm],

[trans_end_mm],[trans_yyyy],[trans_discount_code],[trans_discount_amnt],

[trans_base_dbt_amnt],Ltrim(Rtrim([trans_rem1])),Ltrim(Rtrim([trans_rem2])),

' ', ' ',

[trans_monit_emp],

' ', ' ', ' '

from localdb1.dbo.Bo2monit a

where trans_cnfrm_date = '00000000'


HTH
Gad




date: Tue, 11 Aug 2015 09:56:59 -0400
from: Darryl Freinkel <dhfreinkel@xxxxxxxxx>
subject: Re: Writing from ms SQL to IBM i database

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp, 'Extract', 'STD COST', 'Number of STD COST
records extracted',
(select count(*) from dbo.DMFINVENTJOURNALENTITY ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp,
'Extract',
'OSV',
'Duplicates found',
(SELECT COUNT(*) FROM ( select count(*) from
dbo.DMFINVENTJOURNALENTITY group by inventlocationID, wmslocationid,
ITEMID, CONFIGID having count(*) > 1 )) )
;
Msg 7306, Level 16, State 2, Line 2
Cannot open the table ""C205105V"."VD_GPL"."AX__METRICS"" from OLE DB
provider "DB2OLEDB" for linked server "INDY2". Unknown provider error.


Here are the 2 statements.



Sent from my iPad

On Aug 11, 2015, at 9:37 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Can you post the statement?

On Tue, Aug 11, 2015 at 9:33 AM, Darryl Freinkel <dhfreinkel@xxxxxxxxx>
wrote:

I need to write some data from a Microsoft database to the IBM i
database.

The problem I have is I have a linked server that I can read from all
day.
However, I cannot write back to the linked server which is my IBM i. The
script is basically rejecting the 4 part file name, that is, [linked
server
name].[catalog name].[library name].[file name]

Has anyone experienced this issue and how did you work around the issue?


TIA.

Darryl Freinkel.

Sent from my iPad
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].