RE: Connecting to AS400 from MS SQL Enterprise Manager

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, to

>> my 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.