November 28, 2012 at 9:25 am
Hi, all!
Over the past year, we've been migrating our reporting databases from 2000 to 2008R2. We've got many stored procedures that import data from a DB2 system using a linked server. During the migration, we've had to switch them from using a four-part identifier to using OPENQUERY (we were getting errors using the four-part identifier, but I no longer remember what they were).
Well, TPTB have decided that we're moving our production servers to a colo, which means migrating everything to a completely different server.
The new server is set up the same way as the old one, same version of the OS, same version of SQL Server, linked servers are set up the same, and as far as I can tell the DSNs to the DB2 server are set up the same.
We tested some of the import sprocs on the new server and started getting errors:
An unexpected NULL value was returned for column "[MSDASQL].column" from OLE DB provider "MSDASQL" for linked server "server". This column cannot be NULL.
We're getting the same error when we're doing a simple select (so it's not an issue with the destination column being set to not allow NULLs), as well as whether we specify the columns or do a SELECT *. We're able to get the data without errors if we use the four-part identifier instead of OPENQUERY, but we don't want to have to re-rewrite all of those import sprocs to move them to this new server.
I'm not seeing anything in the linked server setup or in the properties for the DSN regarding NULL translation. Does anyone have any idea where we should look or how we can work around this so we don't have to rewrite all the sprocs prior to the cut-over to the colo?
Thanks in advance!
--Jennifer
Jennifer Levy (@iffermonster)
November 28, 2012 at 11:00 am
Are you 100% certain the schema is identical from ServerA - ServerB - this also means, there are no computed columns on this new server that's generating the errors?
It would also help others on this site if you could 1) post the table definitions from the target and host server, 2) the exact SELECT statement that is generating the error.
I've had this happen often enough when there's a column that has a defaulted value assigned to it...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 28, 2012 at 12:02 pm
With regards to the SQL Server, we backed up the database on ServerA and restored it as-is on to ServerB. I've been the only one working on the SQL database and I haven't made any changes to it.
The DB2 definitions change based on which library we're taking the data from, but the first one we encountered the issue with is as follows (names changed to protect the innocent ;-)):
TableKey1 char(3)
TableKey2 char(12)
TableKey3 char(3)
Column4 packed(8,0)
Column5 char(1)
Column6 char(10)
Column7 char(10)
Column8 packed(14,0)
Column9 zoned(10,0)
Column10 zoned(10,0)
The SQL table that we're inserting the data into is as follows:
[TableKey1] [char](3) NOT NULL,
[TableKey2] [char](12) NOT NULL,
[TableKey3] [char](3) NOT NULL,
[Column4] [numeric](8, 0) NOT NULL,
[Column5] [char](1) NOT NULL,
[Column6] [char](10) NOT NULL,
[Column7] [char](10) NOT NULL,
[Column8] [numeric](14, 0) NOT NULL,
[Column9] [numeric](10, 0) NOT NULL,
[Column10] [numeric](10, 0) NOT NULL,
[DBSpecificColumn11] [char](6) NOT NULL,
[DBSpecificColumn12] [char](30) NOT NULL,
[DBSpecificColumn13] [char](40) NOT NULL,
[DBSpecificColumn14] [char](20) NOT NULL,
[DBSpecificColumn15] [char](20) NOT NULL,
[DBSpecificColumn16] [char](10) NOT NULL,
[DBSpecificColumn17] [char](15) NOT NULL,
[DBSpecificColumn18] [char](40) NOT NULL,
[DBSpecificColumn19] [char](10) NOT NULL,
[DBSpecificColumn20] [char](10) NOT NULL,
[DBSpecificColumn21] [numeric](10, 0) NOT NULL,
[DBSpecificColumn22] [char](10) NOT NULL
The INSERT statement that we're doing to pull the data down is:
INSERT INTO table
SELECT [TableKey1],[TableKey2],[TableKey3],[Column4],[Column5],[Column6],[Column7],[Column8],[Column9],[Column10]
,'','','','','','','','','','',0,''
FROM OPENQUERY(server, 'SELECT * FROM providerstring.library.table')
The error was coming from Column9. The NOT NULL specifications on the SQL table led me to believe that that was the issue when I first got the error. I tried just doing the SELECT at that point, using both:
SELECT [TableKey1],[TableKey2],[TableKey3],[Column4],[Column5],[Column6],[Column7],[Column8],[Column9],[Column10]
FROM OPENQUERY(server, 'SELECT * FROM providerstring.library.table')
and
SELECT * FROM OPENQUERY(server, 'SELECT * FROM providerstring.library.table')
and got the same error.
Doing the following:
SELECT * FROM server.providerstring.library.table
brings back the data as requested, no errors.
I haven't seen anything in BOL that really explains what the difference is between using OPENQUERY and just using the four-part identifier, and that's what is confusing me the most, that it would work one way and not the other.
Jennifer Levy (@iffermonster)
November 28, 2012 at 12:08 pm
You mention that the 4-part SELECT still works (without error), but does it still work in conjunction with the INSERT?
INSERT INTO table
SELECT * FROM server.providerstring.library.table
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 28, 2012 at 2:53 pm
I had to change the numeric columns on the SQL Server side to allow NULLs, but it ran just fine once I'd done that.
Jennifer Levy (@iffermonster)
November 28, 2012 at 4:49 pm
Jennifer Levy (11/28/2012)
I had to change the numeric columns on the SQL Server side to allow NULLs, but it ran just fine once I'd done that.
No kidding? Thanks for posting that, Jennifer. The folks I work with have been having a similar problem and put the ball in my court lately. You might have just saved me from a wad of investigating.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2012 at 5:14 pm
For kicks and giggles...what happens when you actually include all the columns in the OPENQUERY:
Ex:
INSERT INTO table
SELECT [TableKey1],[TableKey2],[TableKey3],[Column4],[Column5],[Column6],[Column7],[Column8],[Column9],[Column10]
,'','','','','','','','','','',0,''
FROM OPENQUERY(server, 'SELECT TableKey1,TableKey2,TableKey3,Column4,Column5,Column6,Column7,Column8,Column9,Column10
FROM providerstring.library.table')
I also would wager that this would work as well?
SELECT * FROM OPENQUERY(server, 'SELECT TableKey1,TableKey2,TableKey3,Column4,Column5,Column6,Column7,Column8,Column9,Column10 FROM table')
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 29, 2012 at 7:29 am
Jeff, I hope it helps.
MyDoggieJessie, unfortunately it comes back with the same error whether I specify the columns in the inner select statement or not.
Jennifer Levy (@iffermonster)
August 28, 2014 at 12:16 pm
I had the same issue pulling data into SQL Server from an AS400 server using OPENQUERY via a linked server. Try the following Syntax... it solved my problem. My issue involved date fields hence the date string. The key is using the NULLIF AS400 function in the OPENQUERY SQL.
SELECT NULL_COLUMN AS ALIAS_NAME
FROM OPENQUERY([LINKED_SERVER_NAME],'SELECT NULLIF(NULL_COLUMN,''0001-01-01'') AS NULL_COLUMN
FROM CATALOG.LIBRARY.TABLE');
August 29, 2014 at 10:26 am
DataDeveloper (8/28/2014)
I had the same issue pulling data into SQL Server from an AS400 server using OPENQUERY via a linked server. Try the following Syntax... it solved my problem. My issue involved date fields hence the date string. The key is using the NULLIF AS400 function in the OPENQUERY SQL.SELECT NULL_COLUMN AS ALIAS_NAME
FROM OPENQUERY([LINKED_SERVER_NAME],'SELECT NULLIF(NULL_COLUMN,''0001-01-01'') AS NULL_COLUMN
FROM CATALOG.LIBRARY.TABLE');
<Face palm><Multiple head-desk> BWAAA-HAAA!!! Sometimes the simplest solutions are overlooked. Thanks, double D!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2018 at 4:59 pm
DataDeveloper - Thursday, August 28, 2014 12:16 PMI had the same issue pulling data into SQL Server from an AS400 server using OPENQUERY via a linked server. Try the following Syntax... it solved my problem. My issue involved date fields hence the date string. The key is using the NULLIF AS400 function in the OPENQUERY SQL. SELECT NULL_COLUMN AS ALIAS_NAME FROM OPENQUERY([LINKED_SERVER_NAME],'SELECT NULLIF(NULL_COLUMN,''0001-01-01'') AS NULL_COLUMN FROM CATALOG.LIBRARY.TABLE');
I had a similar problem with an old AS400 / iSeries DB2 database. In my case, certain columns were defined as not allowing NULLs, but nevertheless had NULLs in them (go figure). When I tried to do a "SELECT * INTO DestinationTable FROM OPENQUERY([DB2Server] , SELECT Field1, Field2, ... Fieldn FROM MyLib.SourceTable'), I got the OP's "An unexpected NULL value was returned ... " error.
In fact, when I tried a simple, straightforward selection "SELECT * FROM OPENQUERY(...)" I still got the OLE DB provider error!
This post helped me deal with that problem, although I needed to take a slightly different approach.
To begin with, one needs to be careful about the function names, as there is a NULLif() function, and an IFnull() function (notice the swapping of the two syllables that make up each of the two function names).
The first function, NULLif(), will return a null value if the two arguments are equal. (See https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_nullif.html)
The second function, IFnull(), will return a "default" value if a field is NULL. (See https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_ifnull.html)
In my case, I first tried using the IFnull() function. It didn't work -- I still got the same error.
I also tried using the NULLif(Fieldx, ''0''), but suddenly valid '0' values were being converted to NULL (uh duh). I did notice, however, that the NULLif() function caused the creation of a data schema in SQL server that did allow NULLs, (as opposed to what happened when the IFnull() function was used, which basically mirrored the "no NULLs" definition in the AS400).
The SOLUTION turned out to be to combine the two functions wherever there was a problematic field:
NULLif( IFnull(Fieldx, ''NULL''), ''NULL'' ) AS Fieldx <== note the use of two apostrophes to bracket either side of NULL, NOT a double quote.
I think this works because the OLE DB provider senses that NULLs are allowed due to the presence of the NULLif(), and so when it encounters a NULL value, it is willing to go along, rather than rejecting things outright. (Notice that the error comes from the OLE DB provider and not SQL Server.)
A few things to note:
Hope the foregoing is helpful for any future solution seekers (I myself found this post 6 years after the original posting).
April 6, 2018 at 4:45 am
A few things to consider
Ensure you are using the correct datasource provider - there are at least two common ones for connecting to an iSeries and they do have differences in how they handle things.
If you need to do anything beyond SELECTS then check how RPCs have been configured on the iSeries
Also remember that the query in the OPENQUERY is not SQL it needs to be valid DB2 query language - they are very similar but DB2 predates SQL and whilst the two languages are similar they do differ in several areas. I'd try to avoid using any functions in the query including standard SQL ones - they may not exist or may be implemented differently in DB2.
default values differ between the two environments particularly for date types
April 6, 2018 at 6:10 am
crmitchell - Friday, April 6, 2018 4:45 AMA few things to considerEnsure you are using the correct datasource provider - there are at least two common ones for connecting to an iSeries and they do have differences in how they handle things.
Any chance of you identifying the names, sources, and differences for those two common ones?
If you need to do anything beyond SELECTS then check how RPCs have been configured on the iSeries
How should they be configured and for what things beyond SELECTs?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2018 at 8:53 am
Jeff Moden - Friday, April 6, 2018 6:10 AMcrmitchell - Friday, April 6, 2018 4:45 AMA few things to considerEnsure you are using the correct datasource provider - there are at least two common ones for connecting to an iSeries and they do have differences in how they handle things.
Any chance of you identifying the names, sources, and differences for those two common ones?
If you need to do anything beyond SELECTS then check how RPCs have been configured on the iSeries
How should they be configured and for what things beyond SELECTs?
Its been a couple of years and I've moved companies so its not so easy to check but one is IBMDA400 and the other IBMDASQL - looks like there's a third as well IBMDARLA - these are the IBM supplied providers and are installed when Client Access is installed - check with your iSeries admins as to which version of Client Access you need. There are also the ones included with SQL server mentioned further up this thread. .
For the configuration I had found a reference in IBMs blue or red book - their equivalent to BOL - there's a setting to allow remote calls if you want to be able to call an iSeries program (RPG or Synon) - I think it may relate to running UPDATES as well.
Oh and don't forget the horrible meaningless 8 character all uppercase names for everything and also watch out for collation differences.
April 6, 2018 at 9:04 am
I understand the reference to DB2 SQL being different in a number of places. That's why the links I provided in my original post are to IBM DB2 documentation.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply