Re: Writing from ms SQL to IBM i database (Darryl Freinkel)

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.