DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

[K.Milligan]

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Need to create an ALIAS - that statement does support a member name to be included.

CREATE ALIAS LIB1.ALIAS1 FOR lib.file(mbr)

[Guest.Visitor]

Multiple Member file in DB2

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
I have been using the 4 part identifier to get AS400/iSeries data into my data warehouse. My newest challenge is to get the data from a distinct member within the table and I simply can not figure out the syntax.

Select * from AS400.SYSTEMNAME.LIBRARY.FILENAME fails with a fatal error and I am told to disregard my results.

I have tried Select * from AS400.SYSTEMNAME.LIBRARY.FILENAME (MEMBER) and it yells at me about functions. I have tried multiple variations of adding the member name: quotes, no spaces, using the EXEC ('statement') at AS400. I am at loss.

Is there a way to do this?

Thanks

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Hi, 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. To fix: Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account Under Security - Access permissions: Allow System: Local Access and Remote Access. After that everything worked ok

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Hi, 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: EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @srvproduct=N'IBMDASQL', @provider=N'IBMDASQL', @datasrc=N'iSeriesName', @catalog=N'LibraryName' and then I add the security info to the link server. When I run my following select statement Select * From OPENQUERY(LinkServerName, 'select * from LibraryName.FileName') I have following error message: Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "IBMDASQL" for linked server "LinkServerName". Did I missed anything? Can any one help? Thanks

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
To see if a port is blocked go to GRC.com's Shields up at https://www.grc.com/x/ne.dll?bh0bkyd2. "tinoq" wrote in message news:6b349fe0.13@WebX.WawyahGHajS... > How do you check if a port is blocked or not? And how do you unblock > it? Thanks.

[tinoq@yahoo.com]

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
How do you check if a port is blocked or not? And how do you unblock it? Thanks.

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
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. Tom.

[tinoq@yahoo.com]

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

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

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
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. Cliffe

[tinoq@yahoo.com]

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

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

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
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. I ONLY COULD GET MY LINKED SERVERS TO WORK IF I USED UPPERCASE.

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Hi, 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 Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed. My linked server is using this secruity settings "Be made using this security context" AS400UserID, AS400Password on the security tab on the linked server. I have even tried to run this job (Run AS ) my user but still the same error coming. Please help.

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Thanks again for your help. I still got the same error, even using "FORCE TRANSLATE=37". Strange. I'm on CA/400 V5R2. However, I used an ODBC DSN, and was able to make it work, so that's the main thing. Thanks again, and I look forward to Part II of this series and beyond. Cliffe

[M.Sansoterra]

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
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: FORCE TRANSLATE=37 However, you need to be at V5R1 or higher. I tried it on a V5R4 system (using iSeries Access V5R4) and it worked fine.

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
Thanks for the tip, but I'm still getting an error: Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "MYISERIES" The statement to create the linked server (using Client Access' IBMDA400 OLEDB driver) was this: EXEC master.dbo.sp_addlinkedserver @server = N'MYISERIES', @srvproduct=N'IBMDA400', @provider=N'IBMDA400', @datasrc=N'MYISERIES', @catalog=N'S7809323', @provstr='FORCETRANSLATE=1' If I don't put the @provstr argument in, it works, but I get gibberish from queries. Thanks again, Cliffe