Well, just got out of a gig where I accessed the AS/400, DB2, and JD Edwards almost exclusively, only to get assigned…to another AS/400, DB2, and JD Edwards gig. Wow. So I haven’t used this stuff in about ten years, and all of a sudden I get two in a row. May I throw out the proverbial and customary “WTF” at this time? Actually it’s not all that bad. I like working with the 400 guys and gals; seems almost like a group of throwbacks from the days of Cream, Credence Clearwater Revival, or maybe Three Dog Night. Some remind me, for some reason, of the slob lab “helper” who I cursed while at the University of Oklahoma, who sat in the corner reading a book and not helping a soul while I was over at the terminal dying a slow death trying to get my COBOL and SAS programs to compile and run. Or, maybe they remind me of some fishing buddies that I used to have. Those guys were crusty mainframe programmers, tape operators, etc., the types that would always “light up” on the way to the lake for a day of fishing. And I ain’t talking everyday run of the mill ciggies here, bra. (No, I didn’t light up but Bill Clinton did, and he inhaled too for the record).
Anyway, enough of this tomfoolery – this is extremely important stuff here. As I was saying, I have been using SSIS a lot over the past couple of years or so, and, connecting to the AS/400, I’ve always seen the OLE drivers available but hadn’t paid too much attention to them. Come to think of it, I’ve seen these things in SQL Server back forever now, but have never really investigated their use. I’ve just always created a DSN, set some properties, and used that to access the data. (Just like you do, right?). Well, this blog is going to show how to actually use those OLE providers that ship with SQL Server. And if you’ve had problems, yours are no more. It’s easy!
First, create a new OLE connection (not .Net in SQL Server 2008 or DataReader source in SQL Server 2005) but rather plane-jane OLE source connection. Below is what the list might look like when you hit the NEW button. Notice that there are three of them mothers in there for your contemplation and consideration. Use whichever you want:
Create a connection, set the login and password correctly, make sure that you have the right library, and that your table name is qualified to the particular library where your table resides. Then send a query to your handy-dandy AS/400. You might get something like so:
Now I don’t know about you, but System.Byte[] doesn’t look right to me. Might be, but probably not. Actually what’s happening is that it’s trying to convert the binary or hex data to text; we don’t want that. What we want is to set the Force Translation to zero, so open up your connection and click on the All button. Then, go to the Force Translate property and put a zero in there. (You’ll overwrite 65535, the default for their binary or hex values). Try your query now and see how it works out for ya. Mine worked!
Thanks for reading!
Lee
-----------------------------------------
“Can I buy some Pot from you?”
