I'm fairly new to SQL Server and come from a COBOL background, but have been given this to deal with. I'm not sure if it is Microsoft or IBM so I'm going to ask both places.
This was working before we updated iSeries Personal Communication on Server to try to fix problem where SQL Server Linked Server would lose communication with iSeries and the only solution that reconnected the link was to reboot the SQL server. I've search for an answer but nothing has worked.
Linked Server Setup: Provider: Microsoft OLE DB Provider for ODBC Drivers Product Name: AS400 DataSource: AS400 Provider String: AVI Location: 192.168.1.1 Catalog: AVI
Security: Specific login and password set up on iSeries(AS400)
Collation Compatible: False DataAccess: True Rpc: True Rpc Out: True Use Remote Collation: False Collation Name: [Blank] Connection Timeout: 0 Query Timeout: 0
Query iSeries without error. select icateg, icode, idescr, curcost, effdt, prevcost, gpcode from AS400.AVI.QS36F.COMINGR where ICATEG = 8 and ICODE = 5 and GPCODE = 'C00120'
yields: icateg icode idescr curcost effdt prevcost gpcode 8 5 MOZZARELLA-PRESHREDED 1.82 80206 1.78 C00120
Query using iSeries and join to SQL table without error. select a.PREVCOST as A_PREVCOST,a.CURCOST as A_CURCOST,a.EFFDT as A_EFFDT, b.CURCOST as B_CURCOST, b.CurrPurCost as B_CurrPurCost, b.NewEff as B_NewEff from AVIData.dbo.COMINGR_UPD b join AS400.AVI.QS36F.COMINGR a on b.GPCODE = a.GPCODE and b.ICATEG = a.ICATEG and b.ICODE = a.ICODE
yields: dates are integer YYMMDD values. A_PREVCOST A_CURCOST A_EFFDT B_CURCOST B_CurrPurCost B_NewEff 1.78 1.82 80206 1.82 1.830000000 80206 1.77 1.81 80206 1.81 1.820000000 80206 1.66 1.70 80206 1.70 1.714990000 80206 1.61 1.66 80206 1.66 1.715000000 80206 1.66 1.70 80206 1.70 1.714990000 80206 467.87 459.76 80206 459.76 467.874000000 80206 519.46 510.45 80206 510.45 519.456000000 80206 28.89 28.34 80206 28.34 28.890000000 80206 32.40 33.21 80206 33.21 32.400000000 80206 20.80 19.80 80206 19.80 20.799960000 80206 3.22 3.34 80206 3.34 3.220010000 80206 2.91 2.96 80206 2.96 2.910000000 80206 2.67 2.81 80206 2.81 2.670010000 80206 51.15 49.81 80206 49.81 1285.200400000 80206 1.78 1.82 80206 1.82 1.830000000 80206 42.45 42.46 80206 42.46 42.450000000 80206 33.84 31.09 101 31.09 33.750000000 91212 44.87 45.90 80206 45.90 45.920000000 80206 33.84 31.09 101 31.09 33.750000000 91212 36.57 36.58 80206 36.58 36.570000000 80206 10.84 11.47 101 11.47 11.240000000 91212 82.04 82.03 101 82.03 82.040000000 91212 24.80 27.29 101 27.29 24.800100000 91212
Try to update iSeries table and get error. UPDATE AS400.AVI.QS36F.COMINGR SET a.PREVCOST = b.CURCOST , a.CURCOST = b.CurrPurCost , a.EFFDT = b.NewEff from AVIData.dbo.COMINGR_UPD b join AS400.AVI.QS36F.COMINGR a on b.GPCODE = a.GPCODE and b.ICATEG = a.ICATEG and b.ICODE = a.ICODE
yields: OLE DB provider "MSDASQL" for linked server "AS400" returned message "Data provider or other service returned an E_FAIL status.". Msg 7343, Level 16, State 4, Line 1 The OLE DB provider "MSDASQL" for linked server "AS400" could not UPDATE table "[AS400].[AVI].[QS36F].[COMINGR]".
