x

Linked Server Update Error

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]".

more ▼

asked Dec 12 '09 at 01:44 PM in Default

Todd Reddinger gravatar image

Todd Reddinger
1 1 1 1

On the surface it appears the user doesn't have update rights. Can you do the update with the same user on the AS400?
Dec 12 '09 at 03:02 PM Blackhawk-17
I can run a sql update direct from the AS400 without any problems using the user login that is being used for the linked server.
Dec 14 '09 at 10:34 AM Todd Reddinger
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

"Security: Specific login and password set up on iSeries(AS400)"

My guess is that the account used does not have update permission to perform the update. Having no direct knowledge of the drivers involved, I would say it might be possible that the driver (or some part of it) is configured for read-only access.

more ▼

answered Dec 12 '09 at 05:24 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Sorry, Blackhawk. I've been having intermittent internet connectivity issues, so I didn't see your very-similar comment until after I answered. I gave it a vote up since I clearly had the same train of thought.
Dec 12 '09 at 05:45 PM Tom Staab
I can run a sql update direct from the AS400 without any problems using the user login that is being used for the linked server
Dec 14 '09 at 11:17 AM Todd Reddinger
(comments are locked)
10|1200 characters needed characters left

I ended up switching to "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider" for the linked server instead of "Microsoft OLE DB Provider for ODBC Drivers" this fixed the problem. Just make sure the Allow inprocess is checked on the provider.

more ▼

answered Dec 21 '09 at 02:19 PM

Todd Reddinger gravatar image

Todd Reddinger
1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x127
x101

asked: Dec 12 '09 at 01:44 PM

Seen: 2653 times

Last Updated: Dec 12 '09 at 01:44 PM