sql server connect: Linking AS/400 with SQL -- sql server connect

Re: Linking AS/400 with SQL

jpo NO[at]SPAM bigpond.net.au
3/29/2004 5:05:46 PM

Hi there Lina,

Here are my experiences and tips from the field....

1) On your SQL server create a System DSN for the AS400 you are trying
to connect to. It MUST be a System DSN, I couldn't work out why, and
quite frankly I don't care, just accept it, its the way the world is.
The DSN for this example will be called "MYAS"

2) When you set up your System DSN I changed the following:-

Under TAB Server, I changed the Default Libraries to the name of the
library I was working on.
Under TAB Package(s) I changed the Default Package Library to the one
I was working on.
Under TAB Other, set Scrollable Cursor to Allways scrollable.

3) On your SQL server, create a linked server. Use the Microsoft OLE
DB for ODBC Provider. Under Provider Options, set the following to
True:-

Allow InProcess
Non transacted Updates

Linked Server Name is "MYAS" (this is what you will refer to it as in
code)
Product Name is "MYAS" (from Above)
Data Source is "MYAS" (from Above)

Under Server Options set the RPC and RPC Out ticks to ON

5) Set your security to how you want it.

That is it for the Setup

From ISQLW query analyzer:-

select * from
OPENQUERY(MYAS, 'Select * from library.file')

insert into
OPENQUERY (MYAS, 'Select * from library.file')
select '1', '2', 3, 'Test123', 1, '', '', 23

delete from
OPENQUERY(MYAS, 'SELECT * FROM LIBRARY.FILE')
where REQ = 23

Tips for above... If you want to Insert/Update/Delete you MUST turn on
Journalling. There is an option in the ODBC driver setup COMMIT =
*NONE which is meant to override this, but it doesn't work.

The delete statement is case sensative, and the insert/select is not -
go figure!!! So you will note above that I have used uppercase in the
delete statement.

I hope this helps you out, I didn't run an update, and god willing I
won't have to. This has been THE MOST PAINFUL experience in my life,
and with any luck I will not have anything to do with it again from
this point on.

[quoted text, click to view]

RE: Linking AS/400 with SQL

cgross NO[at]SPAM online.microsoft.com
3/29/2004 10:09:27 PM

BOL has some information such as the topic "OLE DB Provider for DB2". You
can also find lots of general information on DTS, linked servers, and
replication (DB2 can be a subscriber to SQL2K).

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Re: Linking AS/400 with SQL

db2team NO[at]SPAM hotmail.com
3/30/2004 11:01:28 PM

Hello,

StarQuest has a product called StarQuest Data Replicator, quite easy
to setup and very fast.

Bob

[quoted text, click to view]

Re: Linking AS/400 with SQL

Fabio Benedini
4/7/2004 11:32:32 AM

Hi,

I followed your guideline on how to create the linked server, but when I try
to execute the openrowset in ISQLW, it tells me "Executing query batch" and
seems to hang-on. I waited for over 30 minutes but it did not receive any
response from AS and I could not cancel the execution of the query: I had to
stop ISQLW with Task Manager.

Where did I go wrong?

Thanks

[quoted text, click to view]

Re: Linking AS/400 with SQL

jpo NO[at]SPAM bigpond.net.au
4/12/2004 3:23:32 AM

[quoted text, click to view]

Follow ups from our Production migration.....

1) the Account that SQL Server is running MUST be a user account. The
AS400 ODBC configuration is by user, even though it is a system DSN -
you can't argue, its the way it is.

2) Log on as the Account you have configured to use as your SQL
account and set up your DSN settings while logged on as that user.

3) Go and make these changes to the registry as per the URL bellow, it
is so that you don't have to logon every time the client interface
starts up.

http://www.faqts.com/knowledge_base/view.phtml/aid/7054/fid/14

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server connect group.