Stewart_HendersonNO1
asked on
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
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
ASKER
I could do with some more help
Regards,
Stewart
Regards,
Stewart
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.
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.
ASKER
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
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
ASKER
Reference previous mail - it should have said which 'driver'
Regards,
Stewart
Regards,
Stewart
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.
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.
ASKER
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
In terms of creating a server do you mean withn SQL ?
I have seen the link server box configuration
Regards,
Stewart
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(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.