Getting error during INSERT records into AS400 table from SQL server 2005 table

Question

Post reply

Getting error during INSERT records into AS400 table from SQL server 2005 table

dsachu

SSCrazy

Points: 2242
More actions
June 8, 2012 at 2:56 am

#262071

Hi All,
SQL Server 2005 connecting to AS400.
I have a linked server set up from SQL Server to AS400.
I can use openquery() to select data from the remote server:
Like:
select * from openquery(InterfaceClaimData,'select * from jdidpssc.F0311Z1') -- works fine
If I try to INSERT I run into difficulty.
INSERT OPENQUERY (InterfaceClaimData, 'SELECT * FROM jdidpssc.F0311Z1')
SELECT * from ar -- fails
Structure and fields of the both tables are same
The error message i am getting like :
OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Syntax error or access violation. SQLSTATE = 55019I : Table is in invalid state for operation. SQLCODE = -7008 : F0311Z1 in JDIDPSSC not valid for operation.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" could not INSERT INTO table "[MSDASQL]".
please help me.
Regards,
Sachin
9891573054

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

F0311Z1 in JDIDPSSC not valid for operation

This suggests the target table is not writable.

I would check:

1) permissions

2) journaling (check this with your DB2 DBA)

3) isolation level (you usually set this on the provider itself)

Hope this helps

Gianluca

-- Gianluca Sartori

dsachu SSCrazy Points: 2242 More actions · Answer 2

Gianluca Sartori (6/8/2012)
F0311Z1 in JDIDPSSC not valid for operation
This suggests the target table is not writable.
I would check:
1) permissions
2) journaling (check this with your DB2 DBA)
3) isolation level (you usually set this on the provider itself)
Hope this helps
Gianluca

Hi,

Now it's working. Now data going to inserting...........but sometime data inserting and sometime fail.......i don't know why

when insertion fail then error message coming like :

OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "Neither the isolation level nor a strengthening of it is supported.".

OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]General error.".

OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Driver not capable. SQLGetInfo".

OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Driver not capable. Attributes not valid.".

Msg 7392, Level 16, State 2, Line 1

Cannot start a transaction for OLE DB provider "MSDASQL" for linked server "InterfaceClaimData".

Regards,

Sachin.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Seems to be a provider specific error.

I'm afraid I can't offer more help on this subject. Which provider are you using?

-- Gianluca Sartori