Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AS400 table linked to Sql Server table for import

Posted on 2004-04-27
24
Medium Priority
?
649 Views
Last Modified: 2012-08-14
Hi
I have a table on the AS400 that I wish to import into my sql server database. However, I wish  to restrict the number of records I import using one of my existing sql server db tables.

Can I create a DTS routine that will import records from the AS400, but will be restricted by the sql server table?

Can this be done & if so what is the syntax, can it be completed via a DTS

Thanks in advance
Keith
0
Comment
Question by:keithjones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 5
  • 3
24 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10930035
Yes, but it might be very slow--you would just have to try it and see.....

Once you setup the linked server, you can base your DTS input on the query, or use a simple insert query (in the execute sql task).


Select * from as400linked.dbname.owner.table as400
where not exist (select 1 from sqltablehere where  sqltablehere.key=as400.key)


The above will get all the records from the as400 where they don't exist in the sql table....
0
 

Author Comment

by:keithjones
ID: 10930155
Arbert

Right thanks for the coming back so quickly – the problem is the syntax for linking the two or writing the query.

Example

My sqlserver db is on a server called ‘srvbid02’, db called ‘court’, table ‘po_lookup’

AS400 library is ‘mmcrtlib’, table ‘new_recs’, AS400 ODBC connection ‘AS400’

Thanks
Keith
0
 

Author Comment

by:keithjones
ID: 10930189
Arbert

Right thanks for the coming back so quickly – the problem is the syntax for linking the two or writing the query.

Example

My sqlserver db is on a server called  srvbid02,  db called  court ,  table po_lookup

AS400 library is  mmcrtlib ,   table new_recs , AS400 ODBC connection  AS400

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:keithjones
ID: 10930216
maybe my question should be, how do I link AS400 tables into my sql server db
0
 

Author Comment

by:keithjones
ID: 10930228
so that I can write queries or import data based across the two systems
0
 
LVL 34

Expert Comment

by:arbert
ID: 10930282
Create a linked server to the as400.

Pull up SQL Server enterprise manager, go to Security, right click on linked server, create a new linked server.

You just have to  make sure that you have Client Access (or whatever flavor of emulator you use) loaded on the sql server box....
0
 

Author Comment

by:keithjones
ID: 10930366
Arbert

Right I've found the screen(brill) - tried creating a connection although I get an error

Error 7302 Could not create an instance of OLE DB provider IBMDA400
0
 

Author Comment

by:keithjones
ID: 10930421
Arbert

Thanks so far, I do normally use an ODBC connection to get onto the AS400 machine (called AS400)

Keith
0
 

Author Comment

by:keithjones
ID: 10930838
What is the process for setting up the link, which boxes need ticking with what....sorry for all the questions

Thanks
Keith
0
 
LVL 4

Accepted Solution

by:
rehand earned 800 total points
ID: 10932883
0
 
LVL 34

Expert Comment

by:arbert
ID: 10935624
If you normally use the ODBC driver for the 400, you will choose the "OLEDB driver for ODBC " when you create your connection.
0
 

Author Comment

by:keithjones
ID: 10936508
I have tried using the "OLEDB driver for ODBC " but it still doesn't seem to work
0
 

Author Comment

by:keithjones
ID: 10939564
rehand

Thanks for your comments/documents links
0
 

Author Comment

by:keithjones
ID: 10939666
arbert

Right I've managed to get the linked server part working & can retrieve data(see all tables on the AS400).

If I may, going back to your initial query

Select * from as400linked.dbname.owner.table as400
where not exist (select 1 from sqltablehere where  sqltablehere.key=as400.key)

Which part equates to what, currently I have a library and the AS400 connection & the table, therefore would the query just to select data from the AS400 be

Select * from AS400.mmcrtlib.pomhdr

doesn't work...if I get this bit working I'm there..

Thanks
Keith
0
 
LVL 4

Expert Comment

by:rehand
ID: 10939736
You forgot a part
 
-->Select * from AS400.mmcrtlib.pomhdr

SELECT * FROM [ServerName].[DataBaseName].[Owner].[Table]

ie. SELECT * FROM AS400.mmcrtlib.dbo.pomhdr  or AS400.mmcrtlib..pomhdr
0
 

Author Comment

by:keithjones
ID: 10940087
rehand

I tried

Select * from
AS400.MMCRTLIB.dbo.pomhdr

came back with the following error

OLE DB provider 'MSDASQL' does not contain table '"MMCRTLIB.dbo.pomhdr"'

The AS400 - I have linked onto it & named the the link AS400, the same as the odbc connection.....the actual AS400 server is called COURTSUK

Thanks
0
 
LVL 4

Expert Comment

by:rehand
ID: 10940111
did you set the default db on the linked server?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10940551
dbo is SQL Server syntax for database owner--this won't work for the as400.
0
 

Author Comment

by:keithjones
ID: 10940614
rehand

default db ?

arbert
I assume the dbo bit I leave out then ?

Thanks to both
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 800 total points
ID: 10940671
Ya, I believe the owner equates to the catalog on the 400
0
 

Author Comment

by:keithjones
ID: 10941345
arbert

sorry - tell I know zip, do you mean the catalog is the library?
0
 

Author Comment

by:keithjones
ID: 11556570
Thanks to both - they recommend spliting the points
Kind regards
Keith
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question