August 7, 2003 at 1:56 am
Hi,
I'm developing a web app using <b>JSP</b>, <b>DB2 AS/400</b> database link into MS SQL Server. Right now we are in the report phase of the application. My problem now was how to insert or update data using openquery. Is there a way i can do it using openquery?
thx,
August 7, 2003 at 6:41 am
August 7, 2003 at 8:04 am
As Allen's post alludes to, openquery must have a resultset returned. You can fool sql server by appending a dummy select to the query. In sql server I would do the following
SELECT * FROM OPENQUERY(linked_server, 'INSERT INTO tablea (col1,col2) VALUES (value1,value2) SELECT null')
Don't know about DB2 AS/400 though.
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2003 at 7:01 pm
i have tried the example refered to me by allen.
begin tran
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500)
Select @cmd = 'Update openquery(LEAVESBOX,''Select SNER,SNBHNO,SNEN,SNLC,SNASDT,SNAEDT,SNUPD From LVSNP'')
Set SNUPD = 123456
Where SNER = ''080'' And
SNBHNO = ''200305000004'' And
SNEN = ''000021504'' And
SNLC = ''SLECC'' And
SNASDT = 11022003 And
SNAEDT = 11052003
'
exec ( @cmd )
commit tran
but it returned error:
Server: Msg 8501, Level 16, State 3, Line 1
MSDTC on server 'OVSACLOT' is unavailable.
and when i removed the some lines :
--begin tran
SET QUOTED_IDENTIFIER OFF
--SET XACT_ABORT ON
declare @cmd varchar(2500)
Select @cmd = 'Update openquery(LEAVESBOX,''Select SNER,SNBHNO,SNEN,SNLC,SNASDT,SNAEDT,SNUPD From LVSNP'')
Set SNUPD = 123456
Where SNER = ''080'' And
SNBHNO = ''200305000004'' And
SNEN = ''000021504'' And
SNLC = ''SLECC'' And
SNASDT = 11022003 And
SNAEDT = 11052003
'
exec ( @cmd )
--commit tran
the error msg was :
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 7 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Data provider or other service returned an E_FAIL status.]
Anyway, the MS SQL Server i was using was version 7. Do I need to install MS DTC on our server?
August 7, 2003 at 8:29 pm
quote:
Anyway, the MS SQL Server i was using was version 7. Do I need to install MS DTC on our server?
MSDTC service should be there. Make sure it is started.
August 7, 2003 at 9:08 pm
Mr. Allen Cui,
as you requested, i have installed and run the MS DTC (Distributed Transaction Coordinator ) service of MS SQL and run the script below:
begin tran
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500)
Select @cmd = 'Update openquery(LEAVESBOX,''Select SNER,SNBHNO,SNEN,SNLC,SNASDT,SNAEDT,SNUPD From LVSNP'')
Set SNUPD = 123456
Where SNER = ''080'' And
SNBHNO = ''200305000004'' And
SNEN = ''000021504'' And
SNLC = ''SLECC'' And
SNASDT = 11022003 And
SNAEDT = 11052003'
exec ( @cmd )
commit tran
but i still get an error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Data provider or other service returned an E_FAIL status.]
sir,what should i do next?
August 7, 2003 at 10:43 pm
Since you already have the linked server have you tried just using the fully qualified table name with a normal Insert or Update query? IE:
UPDATE LEAVESBOX.YourDB..YourTable
SET Field = 'blah'
WHERE Foo = 1
OR
INSERT INTO LEAVESBOX.YourDB..FOO
VALUES(...)
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
August 8, 2003 at 9:19 pm
Sir, i tried using fully qualified table name like this :
select * from LEAVESBOX.S102CCCM.LVSDEVLIB.LVSNP
select statements worked but when i tried the insert or update statements :
update LEAVESBOX.S102CCCM.LVSDEVLIB.LVSNP
set SNUPD = 8082003
Where SNER = '080' And
SNBHNO = '200305000004' And
SNEN = '000021504' And
SNLC = 'SLECC' And
SNASDT = 11022003 And
SNAEDT = 11052003
An error msg occurs :
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 18 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Data provider or other service returned an E_FAIL status.]
Do i need to re-configure my link server so that it can accomodate this kind of transaction?
August 8, 2003 at 9:50 pm
It looks to me the problem may come from the driver you used to setup ODBC/OLE DB connection to AS/400.
Can you make select with openquery successfully? Could you provide more information how did you creatd the linked server to AS/400.
August 8, 2003 at 10:05 pm
Yes Sir, i can succesfully make a select statement using openquery or using the fully qualified table name. When i added a link server on our SQL, i used an ODBC driver which is connected to our AS 400 machine. In addition to this, i also a have another third party query tool which is "Query Tool(using ODBC) 4.1.0.4" which is also connected to the same ODBC i have used in my link server. Using that tool, i can make a select,insert,update or delete to our AS 400 machine seamlessly. I really don't know why i can't make it using MS SQL server.
I also tried this:
Update OpenQuery(LEAVESBOX,'Select * From LVSNP Where 1=2')
set SNUPD = 8082003
Where SNER = '080' And
SNBHNO = '200305000004' And
SNEN = '000021504' And
SNLC = 'SLECC' And
SNASDT = 11022003 And
SNAEDT = 11052003
I added the where clause 1=2 in order to fool SQL server. It did not returned an err msg but it did not affect the table.
Edited by - xnor on 08/08/2003 10:17:05 PM
August 8, 2003 at 10:14 pm
Which ODBC driver do you use?
August 8, 2003 at 10:20 pm
Client Access ODBC Driver (32-bit)!
Sir, can you recommend me some other driver(s)?
August 8, 2003 at 10:32 pm
http://www.datadirect-technologies.com/products/odbc/matrix/connectodbc.htm
http://www.hitsw.com/products_services/index.html
Is there unique key column on the target tables?
August 8, 2003 at 11:04 pm
Sir, all those fields in the where clause are the indexes. I think indexes in AS 400 are just the same with keys in SQL. Sir, to tell you honestly i'm not an AS 400 programmer and i don't much about this machine(AS 400).
I will try to visit those link you stated. But for the meantime, please excuse me Sir, I will just have my lunch first coz it's already 12 noon here in the Phil. You to Sir, have your lunch now. Is it also noon in your place now? 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply