RE: Severe error occurred when transferring DB2 data to SQL Server

For the archives:

After I sent the email I reviewed the linked PDF again, and this query
works:
delete from dbnxcpp_import;
insert into dbnxcpp_import
select * from OpenQuery(DB2400, 'select * from vngdbdta.dbnxcpp where
nxyfnb>1624500') as remote;

(1 row(s) affected)

(311 row(s) affected)

Dbnxcpp_import and DB2 file dbnxcpp have the same number and similar
kind of fields.


Wrap-up:

Select * from DB2400.S11111EC.VNGDBDTA.DBNXCPP
Fails with "A severe error occurred on the current command. The results,
if any, should be discarded.".

Exec('select * from vngdbdta.dbnxcpp') at db2400
Works.

Insert into dbnxcpp_import exec('select * from vngdbdta.dbnxcpp') at
db2400
Fails (never completes).

Select * from OpenQuery(DB2400, 'select * From vngdbdta.dbnxcpp')
Works.

Insert into dbnxcpp_import select * from OpenQuery(DB2400, 'select *
from vngdbdta.dbnxcpp')
Works.



Thanks,
Loyd


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
lgoodbar@xxxxxxxxxxxxxx
Sent: Tuesday, March 10, 2009 10:12 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Severe error occurred when transferring DB2 data to SQL Server

We are periodically transferring some i5/OS DB2 data to SQL Server, but
some files cannot be queried. We installed iSeries Access on a SQL
Server 2005 box, following this article (PDF):
http://www.mcpressonline.com/index2.php?option=com_content&do_pdf=1&id=1
541. We are using linked servers back to DB2.



Most files can be queried and copied using the syntax: select * from
db2400.S11111EC.MYLIB.MYFILE. (This means linked server name . system
name . schema (library) name . table (file) name.)



However, when querying some files, we get the error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any,
should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any,
should be discarded.





I can run a query with the exec at syntax:

Exec('select * from MYLIB.MYFILE') at db2400



But a data transfer never completes.

Insert into somefile exec('select * from MYLIB.MYFILE') at db2400





I've reviewed the files in question, but have not found any significant
differences. All the files are vendor-supplied, DDS, defined with CCSID
37 (EN-US). All of the queries are run from Management Studio's query
window, and will become stored procedures.



I saw two unrelated posts in the archives, and Google isn't my friend
today (nothing jumps out at me).



TIA,

Loyd



--

Loyd Goodbar

Business Systems

BorgWarner Shared Services

662-473-5713