[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

creating a view of certain tables from AS400 in SQL

I need to be able to create a view (uptodate) of certain tables on an AS400.

I can link into the tables using access, ie creating a DSN ODBC connection and then using either a sql passthrough query or simply linking the table.

I am running SQL 7.0 and have created a new database called Product_Codes. How can create a link (view) into the as400, ie corrcet syntax. I know what the library's are and what the tables are but am struggling setting this up

Regards,

Stewart

0
Stewart_HendersonNO1
Asked:
Stewart_HendersonNO1
  • 5
  • 4
1 Solution
 
danblakeCommented:
You probably want to setup a linked server or openquery to be able to perform this action, refer to the following:
(How to setup a linked server for oracle)
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

Okay, the article is refering to Oracle but the key steps are still the same, create the linked server, test, debug, fix... use.
Normally I have found that the AS400 systems do not always work unless you are using the latest service packs on both sql-server and the AS400 host and don't forget the client drivers on the sql server.
0
 
Stewart_HendersonNO1Author Commented:
I could do with some more help

Regards,

Stewart
0
 
danblakeCommented:
Okey, ...
We can do this over the next few hours, I've got some time if you can stay on-line:

Have you got the latest ODBC drivers to AS400 system installed on the SQL Server ?
Have you got the latest patchs in places on your AS400 ?

Also refer to: http://www.databasejournal.com/features/mssql/article.php/3085211

In the linked server dialog box, give it a suitable name,
the ODBC AS/400 data source driver,
login/pwd credentials,

and probs at any stage - -buzz.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Stewart_HendersonNO1Author Commented:
I have a blank database open in SQL

Where is the linked server dialog box located ( Do you mean the ODBC Data Source connection ? ) If the latter which drive should I select

Regards,

Stewart
0
 
Stewart_HendersonNO1Author Commented:
Reference previous mail - it should have said which 'driver'

Regards,

Stewart
0
 
danblakeCommented:
Drivers on Win Intel :

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0006416.htm

Upgrades required to MTS 2.0 for the AS/400 supporT:
http://support.microsoft.com/kb/192681/EN-US/

The linked server connection box, is provided under the server -> security -> linked servers and is not present within the database itself.
First you need to create a server -> server link before the database can access the AS/400 systems.
0
 
Stewart_HendersonNO1Author Commented:
I have the client acces driver listed

In terms of creating a server do you mean withn SQL ?

I have seen the link server box configuration

Regards,

Stewart
0
 
Stewart_HendersonNO1Author Commented:
Buzz,

I have created a linked server called Testing and selected the microsoft ODBC OLE driver, put in a name and put the source in ie AS400 Access and it has dropped in all the tables from the library I specified in the ODBC source, ie AS400 Access.

I tried double clicking on one of these tables but nothing happened. Can I open them in another view or should I do a query through access ?

Regards,

Stewart
0
 
danblakeCommented:
Try doing a query from within Query Analyser, this way we should see if we get any error messages.
The fact that we have got a listing of all the tables is a good sign.

Have a look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;222937
for some example querys to run.

You can create views to access the tables, sps can also be used.
Take your pick, if the tables do not return any rows, I would check for error msgs -- post some form of the error for further diags or check the security accounts have access to the approapriate tables in question using the linked server account you have specified.

If the linked server does not work, also try the OPENROWSET mechanisms -- to check conectivity results.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now