Solved

AS400 table linked to Sql Server table for import

Posted on 2004-04-27
24
614 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 200 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 200 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 60
SSRS 2013 - Creating a summarized report 19 36
insert wont work in SQL 14 22
SQL Server Error 21 8 25
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

810 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