Linked Server to AS400 Error Null Value

Question

Linked Server to AS400 Error Null Value

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 26, 2014 at 2:48 pm

#299687

I have a Stored Procedure that uses an Open Query to load a SQL Server Table.
This is the Open Query part:
SELECT *
FROM OPENQUERY(lsctlr40dat, 'SELECT B1AACD,
COALESCE(B1ABCD,00),
--B1ABCD,
B1ARTX,
B1ASTX,
B1ADNB,
B1ARTX ||B1ASTX AS POLICY_NO,
B1AGTX,
COALESCE(B1BRNB,00),
--B1BRNB,
B1EGNB,
B1ANTX,
B1AENB,
B1AOTX,
B1C0NB,
B1PBTX,
B1ITEM,
B1AZCD,
B1FGTX,
B1A1CD,
B1TRANSEQ,
B1J4TX,
B1BSNB,
B1CINB,
B1CBTX,
B1CZST,
B1AKCD,
B1AEPC,
B1AGVA,
B1ALRIND,
B1PSTX,
B1ALRUS1,
B1ALRUS2,
B1ALRUS3 FROM ASB1CPP');
I get the following error and I can not determine what column is causing the problem.
Msg 7342, Level 16, State 1, Procedure usp_Insert_Statistical_Xref, Line 4
An unexpected NULL value was returned for column "[MSDASQL].00008" from OLE DB provider "MSDASQL" for linked server "lsctlr40dat". This column cannot be NULL.
Any ideas? 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Post the full and complete code you are running, including source definition, source query, insert statement and the destination table definition. Don't leave anything out!

😎

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Welsh Corgi (9/26/2014)
I have a Stored Procedure that uses an Open Query to load a SQL Server Table.
This is the Open Query part:
SELECT *
FROM OPENQUERY(lsctlr40dat, 'SELECT B1AACD,
COALESCE(B1ABCD,00),
--B1ABCD,
B1ARTX,
B1ASTX,
B1ADNB,
B1ARTX ||B1ASTX AS POLICY_NO,
B1AGTX,
COALESCE(B1BRNB,00),
--B1BRNB,
B1EGNB,
B1ANTX,
B1AENB,
B1AOTX,
B1C0NB,
B1PBTX,
B1ITEM,
B1AZCD,
B1FGTX,
B1A1CD,
B1TRANSEQ,
B1J4TX,
B1BSNB,
B1CINB,
B1CBTX,
B1CZST,
B1AKCD,
B1AEPC,
B1AGVA,
B1ALRIND,
B1PSTX,
B1ALRUS1,
B1ALRUS2,
B1ALRUS3 FROM ASB1CPP');
I get the following error and I can not determine what column is causing the problem.
Msg 7342, Level 16, State 1, Procedure usp_Insert_Statistical_Xref, Line 4
An unexpected NULL value was returned for column "[MSDASQL].00008" from OLE DB provider "MSDASQL" for linked server "lsctlr40dat". This column cannot be NULL.
Any ideas? 🙂

You need column alias names for the columns that have formulas in them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I have views that use aliases to load the tables about 200 tables not in this case.

I wrote the package two years ago and it has been working fine.

It is a data issue.

I created a table in Development that allowed nulls on all columns and it failed.

Thank you sir.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

We're using OPENQUERY & linked server to run ad-hoc queries against a DB2 database. We discovered quite early on that DB2 (or possibly the driver) will balk at almost anything except a simple string containing nothing more than DB2 SQL. That includes TSQL comments marked with "--". Sometimes you get away with it, sometimes you don't.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

ChrisM@Work (9/29/2014)
We're using OPENQUERY & linked server to run ad-hoc queries against a DB2 database. We discovered quite early on that DB2 (or possibly the driver) will balk at almost anything except a simple string containing nothing more than DB2 SQL. That includes TSQL comments marked with "--". Sometimes you get away with it, sometimes you don't.

I get the error in development but not in production.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Welsh Corgi (9/29/2014)
ChrisM@Work (9/29/2014)
We're using OPENQUERY & linked server to run ad-hoc queries against a DB2 database. We discovered quite early on that DB2 (or possibly the driver) will balk at almost anything except a simple string containing nothing more than DB2 SQL. That includes TSQL comments marked with "--". Sometimes you get away with it, sometimes you don't.
Thanks for the feedback.
I get the error in development but not in production.

I'm a one man band.

I have been using OPENQUERY against AS400 & Oracle for 3 years as of tomorrow.

I try to avoid at all cost and I used OLE Source and OLE Destination for it is much faster.

In some cases I have had to use it because the Data Transformation would error out on me on a few records for what I belive there were some garbage characters in a column(s).

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/