I have successfully setup a linked server from my SQL Server to our AS400 that I can use for SELECT's and UPDATE's. However I can not get an INSERT or DELETE to work.
First what syntax should I be using. For the insert if I use:
INSERT INTO OPENQUERY([R2D2-Test], 'SELECT ABAN8,ABALPH FROM CRPDTA.F0101')
(ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.].
If I use:
INSERT INTO [R2D2-Test]..CRPDTA.F0101 (ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].
I'm not sure if this indicates a config. issue on either the ODBC conn. or the linked server or if I have a syntax error.
For the DELETE, if I use
Delete from [R2D2-Test]..CRPDTA.F0101DAU where aban8 = 100
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].
If I use:
Select * from openquery([R2D2-Test],'Delete from CRPDTA.F0101 where aban8 = 100')
I get:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Delete from CRPDTA.F0101 where aban8 = 100'. The OLE DB provider 'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='IBMDA400', Query=Delete from CRPDTA.F0101 where aban8 = 100'].
Can someone help figure out the correct configuration and/or syntax.
Help!!
First what syntax should I be using. For the insert if I use:
INSERT INTO OPENQUERY([R2D2-Test], 'SELECT ABAN8,ABALPH FROM CRPDTA.F0101')
(ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method.
OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.].
If I use:
INSERT INTO [R2D2-Test]..CRPDTA.F0101 (ABAN8,ABALPH) VALUES (999999,'SQL TEST')
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].
I'm not sure if this indicates a config. issue on either the ODBC conn. or the linked server or if I have a syntax error.
For the DELETE, if I use
Delete from [R2D2-Test]..CRPDTA.F0101DAU where aban8 = 100
I get:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'R2D2-Test' does not contain table 'CRPDTA.F0101'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='R2D2-Test', TableName='CRPDTA.F0101'].
If I use:
Select * from openquery([R2D2-Test],'Delete from CRPDTA.F0101 where aban8 = 100')
I get:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Delete from CRPDTA.F0101 where aban8 = 100'. The OLE DB provider 'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='IBMDA400', Query=Delete from CRPDTA.F0101 where aban8 = 100'].
Can someone help figure out the correct configuration and/or syntax.
Help!!