SQL Server DTS >> Unexpected NULL value - Linked Server AS400 DB2

by mike » Fri, 19 Sep 2003 01:02:05 GMT

Greetings,

We have been Selecting data from A DB2 AS400 via Linked
Servers in SQL 7.

We began to pull data from new intstance/tables that
contain some fields with foreign characters. (double byte,
unicode) Some data will pull fine, then we will get an
error:

Unexpected NULL value returned for
column '[MYLINKEDSERVER].[DB2NAME1].[DB2NAME2].
[MYTABLENAME].DESC2' from the OLE DB provider 'MSDASQL'.
This column cannot be NULL.

The DBA from the other country tells me that the field is
never null.

KB article Q239458 seemed related but doesn't help. We are
running SP4, and will be trying on SP3 soon.

Is there any way to turn off this checking for NULL? My
guess is that it interprets the unicode as a NULL
incorrectly. I've tried to explicitly cast into varchar
(30.)

Sometimes this fields is readable in English and it
contains data that is critical when we can see it.

Thanks In Advance


SQL Server DTS >> Unexpected NULL value - Linked Server AS400 DB2

by Mike » Fri, 19 Sep 2003 22:36:13 GMT


Our work-around was to use a pass-thru query to DB2 that
used NULLIF functions on the fields that failed.

That prevented the OLE-DB from dying because of a
perceived NULL / NOT NULL compatibility issue.

Mike



Similar Threads

1. SQL Server Linked Server problem (with AS400/DB2) - SQL Server

2. Linked Server w/ AS400 (DB2 and/or Phisycal File) - Multi-Posted

Have a look at IBM Website, it's a known problem with Client Express,
they've an upgrade or a workaround for it.

Sastien Mouren,
CAMBOS Consulting



3. DB2 - Linked Server - AS400 - iseries - SQL Server

4. Calling stored procedures on an AS400/DB2 using linked server - PRETTY DESPERATE

I'm trying to call a stored procedure on our AS400(v5.2) via a linked
server in SQL server 2000.

trying "execute" with 4 part naming, like so :

execute myLinkedServer.MEBDEV.FAWTEMP.FETSTSP

gives me the following error : 

Could not process object '{CALL FAWTEMP/FETSTSP()}'. The OLE DB
provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider MSDASQL
returned an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should
be either DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [Non-interface error:  OLE DB provider unable to
process object, since the object has no columnsProviderName='MSDASQL',
Query={CALL FAWTEMP/FETSTSP()}'].

trying the openquery version, like so:


select * from openquery (myLinkedServer,'{CALL FAWTEMP.FETSTSP()}') 

gives the following error :
Could not process object '{CALL FAWTEMP.FETSTSP()}'. The OLE DB
provider 'IBMDA400' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider IBMDA400
returned DBPROP_IStream without DBPROPVAL_SS_ISTREAM being supported].
OLE DB error trace [Non-interface error:  OLE DB provider IBMDA400
returned DBPROP_ILockBytes without DBPROPVAL_SS_ILOCKBYTES being
supported].
OLE DB error trace [Non-interface error:  OLE DB provider IBMDA400
returned an incorrect value for literal length which should be 1].
OLE DB error trace [Non-interface error:  OLE DB provider unable to
process object, since the object has no
columnsProviderName='IBMDA400', Query={CALL FAWTEMP.FETSTSP()}'].

I'm using the IBM DB2 UDB for iSeries OLE DB Provider

I've been struggling with this for a week now, any help will be
greatly appreciated.

5. How do I execute an insert or update to a linked iSeries AS400 db2 - SQL Server

6. DB2/AS400 release and SQL Links

Could some one tell me till what versione or release of Client Access ODBC Driver [ie DB2/AS400] are the sql links of BDE supported?
Thanks in advance
Roger Venad

7. SQL 64bit SP4 - Server: Msg 7342 - Unexpected NULL value returned

8. How doI Link AS400 to SQL Server 2000 using Link Server