Avatar of Michael Purdham
Michael Purdham
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Query syntax for Linked Table from SQL Server to AS400

I have reposted this question as I seem to have categorised it incorrectly.
Old one is deleted.

I have SQL Server 2008 R2 where I have created a linked server to my AS400 using the following
Linked Server=TRDATA
Provider=IBM DB2 UDB for iSeries IBMDA400 OLE Provider
Data source= Hostname of server
Provider string = User Id=uuuu;Password=pppp;Default Collection=LibraryName
Location = Blank
Catalog = Blank

If I right click on the Linked Server and select Test Connection this is successful.

I want now to just select data from a table XXXX on the AS400 in library YYYY

Given the above what is the query to pull records where field CCCC = 'FRED'

I have searched for a definitive statement that works and have tried many permutations but all fail.

Can anyone give the precise statement?
Thanks.
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Michael Purdham

8/22/2022 - Mon
ramrom

Since no AS400 experts have responded - you can continue to wait - or humor me by telling me what you have tried. I googled and found some ideas - but want to see what you've tried.
Michael Purdham

ASKER
From what I've read I need to use something like this but don't know where commas, semi-columns, quotes etc should be
SELECT * FROM
OPENDATASOURCE('IBMDA400','Provider=IBMDA400;Driver={Client Access ODBC Driver (32-bit)};SYSTEM=Hostname of server
;UID=xxxx;PWD=xxxx;DBQ=QGPL;CMT=0;FORCE TRANSLATE=37;SEARCHPATTERN=0 ;PREFETCH=1').QGPL.TRDATA.NEWORDSHIP

This gives me this error
Msg 7303, Level 16, State 1, Line 16
Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "(null)".
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ramrom

According to http://msdn.microsoft.com/en-us/library/ms179856.aspx OPENDATASOURCE takes 2 arguments, a provider name (character string) and an init_string  (character string like
 is 'kwd1=value2;kwd2=value2').

Your OPENDATASOURCE meets these requirements. So commas, semi-columns, quotes seem OK.

The error refers to linked server "(null)". I would think it should be "TRDATA". How to get there is another question!

I will keep digging.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Michael Purdham

ASKER
Thanks, that returns data now without error message however much of the data is scrambled. For example these are supposed to be dates.
0xC6F0F0F1F0F0F0      0xF0F0F0F1F0F0F1
0xC6F0F0F1F0F0F1      0xF0F0F0F1F0F0F0
0xC6F0F0F1F0F0F2      0xF0F0F0F1F0F0F2
0xF3F0F0F8F7F1F3      0xF3F0F0F6F5F7F4
0xF3F0F0F8F7F1F4      0xF3F0F0F6F5F6F6

ODBC queries over DB400 need 'Convert binary data (CCSID 65335) to text' to be set in the ODBC Data Source. I suspect that this is similar to the FORCE TRANSLATE=37 that was in my original question.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Michael Purdham

ASKER
My comment summarises the solution in simple terms.