Updating SQL7 data from AS400

[CC]

I want to update a stock-column i a SQL7 table based on data from DB2/AS400.

Can this be done by DTS?
I'm also having trouble setting up the AS400 as a linked server.
Any tips on this?

CC

[Euan Garden]

Yes it can be done, DYS can connect to DB2 via ODBC.
CC <cars...@online.no> wrote in message news:ulqxYPY5#GA.265@cppssbbsa05...

[Trevor Dwyer]

Hi,

You did not mention how you were connecting to the AS400, so I have listed a
few ways to do this. I have had problems configuring the linked server in
the past so its good to see a number of ways.

My personal choice is option 1.

1. Using OLE DB drivers from Hit Software (www.hit.com)

Within SQL Enterprise Manager, select New Linked Server, enter the following
details, remember to change uid & password

Linked Server: AS400LINK
Provider Name :Hit Software OLEDB Provider for DB2/400
Data Source: [ip_address]
Provider String: Libraries=[YourLibraries];Connect Timeout=90;Host Code
Page=037;

2. Uses the Star ODBC driver for the AS400

EXEC sp_addlinkedserver @server = 'DB2', @provider = 'MSDASQL', @srvproduct
= 'StarSQL 32', @location = 'DBT1', @datasrc = 'DB2IBM'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'DB2', @locallogin = 'sa', @useself
= 'false', @rmtuser = 'HDRUSER' ,@rmtpassword = 'SQL7'
GO

-- Test above
sp_tables_ex N'DB2'
GO
SELECT * FROM DB2..T1ADM007.V007MUNI
GO

3. Example shows use of a datalink file (below) and then creating a linked
server using the UDL file.

Save this as C:\MyDataSource.UDL

[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleDb400.1;Extended Properties="";Mask
Password=True;Password=XXXXX;Persist Security Info=True;User ID=XXXXX;Data
Source="";Locale Identifier=0;Location="";Mode=Read|Write;Connect
Timeout=90;Network Connection=0 - TCP/IP Native;TP
Name=<Default>;Libraries=XXX;APPC LU Alias="";Host System
Name=XXX.XXX.XXX.XXX;APPC Mode Name=QSERVER;Host Code Page=037 -
US/Canada/Australia/N.Z.;Isolation Level=0 - None;APPC Buffer Size=32767;Max
Rows=0;Binary Chars=False;Network Name=XXXXX;Port Number=8471;HiT
Hourglass=True;Use Packages=True;Allow Package Update=True;Package
Name=<Default>;Package Library=<Default>;Fetch Block Size=32;NL Sort
Sequence=0 - Sort based on hex values;LangID or Table=ENU - US English;Sort
Weight=1 - Shared Weight;List All Libraries=False

-- Then issue the sp_addlinked server statement.
EXEC sp_addlinkedserver @server = 'MyAS400', @datasrc =
'C:\MyDataSource.UDL'

-- Finally issue the OPENQUERY statement.
INSERT INTO control_file -- SQL
Server table
SELECT * FROM OPENQUERY(MyAS400, 'SELECT * FROM myTable') -- AS400 table
GO

Hope this helps.

Best Regards

Trevor Dwyer - SQL Server MVP
tdw...@email.msn.com

[Tim Sullivan]

Trevor,

I'm also trying to establish a link to the AS/400 using Client Access
Express. I can't get the OLE provider to work but I can do it with ODBC. I
can execute:
SELECT * FROM SERVER.RDBNAME.LIBRARY.TABLE WHERE custno = 100
or
SELECT * FROM OPENQUERY('SERVER',SELECT * FROM RDBNAME.LIBRARY.TABLE WHERE
custno = 100)

I can even insert
INSERT INTO SERVER.RDBNAME.LIBRARY.TABLE SELECT * FROM SQLTABLE where custno
= 100

But I can't delete
DELETE FROM SERVER.RDBNAME.LIBRARY.TABLE WHERE custno = 100

This fails with "The provider could not support a row lookup position"

I can execute the same query through the same ODBC connection using MSQRY32
successfully.

I've changed every option and setting I can think of. Do you have any
ideas?

Tim Sullivan
Access Paths

Trevor Dwyer <tdw...@email.msn.com> wrote in message
news:eGL90yi5#GA.252@cppssbbsa05...

[Trevor Dwyer]

Hi Tim,

Obvious question really, but I have a suspiscion that the driver may be
enabled as readonly until configured otherwise. I cannot verify this until I
get to my other machine.

--
Best Regards

Trevor Dwyer - SQL Server MVP
tdw...@email.msn.com

Tim Sullivan <t...@accessp.com> wrote in message
news:u7mpoMn6#GA.235@cppssbbsa03...