|
Pete, et al. I too am glad for the info on EM - want to see that work on my PC at work. But--- <blatant verndor response>For easy connectivity from iSeries to other DB's you might consider our RPG2SQL Integrator product for read/write capability. It looks more like SQL CLI than embedded SQL in RPG but is very easy to use. It works with any DB for which an ODBC driver or OLEDB provider exists. I just recently had it working with XML files. Also have used it against PostgreSQL and MYSQL. It also contains functions that communicate directly to Excel via OLE automation, not SQL. It's not DRDA but, unlike DRDA, it is possible to maintain multiple open connections, although not joining across them.
Check out www.rjssoftware.com or call me at 952.898.3038 </blatant verndor response> P.S. Blatant misspelling was on purpose - sic! At 05:34 PM 9/29/2005, you wrote:
Thanks Jim. Great info...Not sure what Mike was trying to do with the iSeries connection in SQL EM but looks like you covered all the bases.Wish it were as easy going the other way (iSeries connecting to to other DB's)...Pete Reinardy, James wrote:Pete, Just to expand on your other post below asking about uses for the linked server beyond DTS, we use the link all the time to add DB2 data to SQL queries. We have a SQL data warehouse of data from our production ERP on the iSeries, but we sometimes need data that is not warehoused. Within a single SQL statement, you can submit a DB2 SQL statement and use it like a table to join in SQL Server data. The primary limitation is that the DB2 data has to come over to the SQL Server to be joined, so you need to be careful about the size of the result set from DB2 that you use. Also, I believe the SQL query is submitted verbatim, which will take advantage of indexes on the iSeries to pull the data, but the result set will have no index on the SQL Server side. Still, it is great for pulling data from small code tables, or when criteria can be used to limit the result set. I also agree with you about DTS! Jim Reinardy -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren Sent: Thursday, September 29, 2005 3:54 PM To: Midrange Systems Technical Discussion Subject: Re: Connecting to AS400 from MS SQL Enterprise Manager Going way back to Mike's original post (if, Mike, you wanted to link to the iSeries Data) In Control Panel, using ODBC Administrator, create a System Data Source selecting the iSeries Access driver and setting the name,system, library and other parameters as you wish. Using MS SQL EM, Expand the Security branch and then right click on "Linked Servers" and select "New Linked Server". In the dialog box, give the Server any name you want and then select the "Microsoft OLE DB Provider for ODBC Drivers" from the drop down list of provider names. In the Data Source field, type the name of the data source you defined in the ODBC Administrator. That's all you need to do. You can also specify a library (collection/schema) in the Catalog field. I also clicked on the Security tab and defined a permanent User ID and password for the context but I think the ODBC driver will prompt you. That was all I needed to do and it connected just fine. Pete Helgren Pete Helgren wrote:Jim, I had forgotten about the Linked Server option.... I'll give it a try and post what I come up with. Pete Pete Helgren wrote:The File/User/System DSN issue really only affects how the DSN is locally stored and how it is accessible. The actual implementation is the same in all cases.I have used ODBC quite a bit in SQL EM to import data using DTS.Beyond that particular application I am not sure how else SQL EM would use iSeries data (e.g. there is no "connector" and it can't, tomy knowledge, manage iSeries tables). DTS works well though. A heckofalot better than the DB2 tools. Pete Helgren-- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.-- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
