This is a most interesting development. <p>It should be interesting to see what happens when a DB2 feature is being used that is not supported by MS. <p>For instance would this still work if the file contained an embedded dot in the file name, or if the file contained more than a billion records? <p>Dave

Thanks a lot for a good article! I've seen the Linked Servers option before, but hadn't been able to make it work. <p>One question for you - do you know how to return data that's CCSID 65537? Normally, in my Connection String, I'll add "Force Translate=37", but I'm not sure how to do that here. I tried adding in @provStr='Force Translate=37;', but no good. Any suggestions? <p>Cliffe

Hi David <p>You are correct -- there are some problems between DB2 and SQL Server that you have to watch for. If you exceed the max rows allows in SQL Server then there's nothing you can do about that. However, DB2 on the iSeries allows you to work around some special character problems (such as an embedded dot) by using double quotes. So the following query will work from SQL Server: <p>Select * <BR>
&nbsp;&nbsp;From OpenQuery(DB2400,'Select * From DATALIB."DOT.TEST"') A <p>Also, if you're using four part table names, this will work as well: <p>Select * From DB2400.S107X248.DATALIB."DOT.TEST" <p>Another issue I've come across is the date data type -- DB2 can hold a much broader range of dates in its ISO data type (starting from year 1, whereas SQL Server starts in the 1700s if I remember right.) For these types of issues you just have to program around them in your query.

Hi Cliffe <p>You're on the right track. In your provider string, simply specify: <p>TRANSLATE=1 <p>or <p>FORCETRANSLATATION=1 <p>This switch is either turns translation on or off, you can't specify a from CCSID.

Thanks for the tip, but I'm still getting an error: <p>Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "MYISERIES" <p>The statement to create the linked server (using Client Access' IBMDA400 OLEDB driver) was this: <p>EXEC master.dbo.sp_addlinkedserver @server = N'MYISERIES', @srvproduct=N'IBMDA400', @provider=N'IBMDA400', @datasrc=N'MYISERIES', @catalog=N'S7809323', @provstr='FORCETRANSLATE=1' <p>If I don't put the @provstr argument in, it works, but I get gibberish from queries. <p>Thanks again, <BR>
Cliffe

Sorry - I gave you a bum steer (I just presumed you were using the ODBC driver). When using the OLE DB provider IBMDA400, you should specify: <p>FORCE TRANSLATE=37 <p>However, you need to be at V5R1 or higher. I tried it on a V5R4 system (using iSeries Access V5R4) and it worked fine.

Thanks again for your help. I still got the same error, even using "FORCE TRANSLATE=37". Strange. I'm on CA/400 V5R2. <p>However, I used an ODBC DSN, and was able to make it work, so that's the main thing. <p>Thanks again, and I look forward to Part II of this series and beyond. <p>Cliffe

Hi, <p>I read your article regarding linked server in SQL 2005. It worked perfect when I am running myself, but under the job it failed with the following error <p>Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed. <p>My linked server is using this secruity settings <p>"Be made using this security context" AS400UserID, AS400Password on the security tab on the linked server. <p>I have even tried to run this job (Run AS ) my user but still the same error coming. <p>Please help.

Thank you for your article! but I found when I tried to create my linked server using the odbc that the 4 part name was case sensitive. <BR>
I ONLY COULD GET MY LINKED SERVERS TO WORK IF I USED UPPERCASE.

Thank you for the article, but I could get it to work using ODBC DSN <BR>
when executing this SQL: <BR>
&nbsp;&nbsp;SELECT * From OpenQuery(DB2400, 'SELECT * FROM QIWS.QCUSTCDT') <p>I'm getting this error: <BR>
&nbsp;&nbsp;"[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=11004 - CWBCO1011 - Remote port could not be resolved". <p>Msg 7303, Level 16, State 1, Line 1 <BR>
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DB2400". <p>PLEASE HELP.

Just a thought, but it sounds like there's a problem connecting to your iSeries. Try using "CWBPING servername" from a command-line, using the same servername you put in your ODBC DSN. <p>Cliffe

I tried CWBPING and I got this: <p>C:>CWBPING 192.168.118.5 <p>IBM iSeries Access for Windows <BR>
Version 5 Release 3 Level 0 <BR>
Connection Verification Program <BR>
(C) Copyright IBM Corporation and Others 1984, 2003. All rights reserved. <BR>
U.S. Government Users Restricted Rights - Use, duplication or disclosure <BR>
&nbsp;&nbsp;restricted by GSA ADP Schedule Contract with IBM Corp. <BR>
Licensed Materials - Property of IBM <p>To cancel the CWBPING request, press CTRL-C or CTRL-BREAK <BR>
I - Verifying connection to system 192.168.118.5... <BR>
I - Successfully connected to server application: Central Client <BR>
I - Successfully connected to server application: Network File <BR>
I - Successfully connected to server application: Network Print <BR>
I - Successfully connected to server application: Data Access <BR>
I - Successfully connected to server application: Data Queues <BR>
I - Successfully connected to server application: Remote Command <BR>
I - Successfully connected to server application: Security <BR>
I - Successfully connected to server application: DDM <BR>
I - Successfully connected to server application: Telnet <BR>
I - Connection verified to system 192.168.118.5

Could it be that the iseries port for odbc is closed or blocked for security reasons? A number that comes to mind for the odbc port for iseries is 50000. <p>Tom.

How do you check if a port is blocked or not? And how do you unblock <BR>
it? Thanks.

To see if a port is blocked go to GRC.com's Shields up at <BR>
<a href="https://www.grc.com/x/ne.dll?bh0bkyd2">https://www.grc.com/x/ne.dll?bh0bkyd2</a>.<BR>
<P>
<P>
"tinoq" < This e-mail address is being protected from spam bots, you need JavaScript enabled to view it > wrote in message <BR>
news: This e-mail address is being protected from spam bots, you need JavaScript enabled to view it <BR>
> How do you check if a port is blocked or not? And how do you unblock<BR>
> it? Thanks. <BR>
<P>
<P>

Hi, <p>I tried to add a link server in SQL 2005 to access iSeries (V5R3) based on the section "OLE DB Provider IBMDASQL" of the article. The following is my T-SQL: <p>EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', <BR>
&nbsp;@srvproduct=N'IBMDASQL', @provider=N'IBMDASQL', <BR>
&nbsp;@datasrc=N'iSeriesName', @catalog=N'LibraryName' <BR>
and then I add the security info to the link server. When I run my following select statement <p>Select * From OPENQUERY(LinkServerName, 'select * from LibraryName.FileName') <p>I have following error message: <BR>
Msg 7302, Level 16, State 1, Line 1 <BR>
Cannot create an instance of OLE DB provider "IBMDASQL" for linked server "LinkServerName". <p>Did I missed anything? Can any one help? Thanks

This is a discussion about <B>DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements</b>.<p align='center'><a href=http://www.mcpressonline.com/mc? This e-mail address is being protected from spam bots, you need JavaScript enabled to view it @.6b344718>Click here for the article</a>.</p>

Hi, <p>I had the same error and it was because I was running SQL 2005 under a service account I'd created rather than a Local system account. <p>To fix: <p>Expand Component Services - Computers - My Computer - DCOM Config <BR>
Select MSDAINITIALIZE Right Click properties then security <p>Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account <p>Under Security - Access permissions: Allow System: Local Access and Remote Access. <p>After that everything worked ok