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;Defa
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.
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;Defa
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.
Last Comment
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.
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',
;UID=xxxx;PWD=xxxx;DBQ=QGP
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)".
SELECT * FROM
OPENDATASOURCE('IBMDA400',
;UID=xxxx;PWD=xxxx;DBQ=QGP
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
My comment summarises the solution in simple terms.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
