 |
| SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
February 23rd, 2005, 05:04 PM
|
|
Registered User
|
|
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
SQL Server connection to IBM AS/400
Hi. I'm a beginner with SQL Server.
I want to create a stored procedure (sp) on our SQL server that will access our IBM AS/400 and retrieve data from it. I donât want to copy the data and store it on the server. I will be using a .Net (C#) application to call this sp to get the data and return it to the .Net app. Any ideas? Thanks, Robert
|
|
February 24th, 2005, 12:01 AM
|
|
Friend of Wrox
|
|
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
You can create a linked server to the AS/400 and use open query to select or move your data.
Jaime E. Maccou
|
|
February 24th, 2005, 04:30 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
|
|
February 24th, 2005, 05:52 PM
|
|
Registered User
|
|
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thank you for your responses. 26411 was helpful but I'm not sure how to create a System ODBC connection. Go into Client Access, ODBC Data Source Administrator, under General tab - Data source name: AS400, Description: Client Access Express ODBC data source, AS/400 system: (our IP address). Is this correct? Then on the SQL server, linked server properties, I don't know what to put in Product name, data source, provider string, location, and catalog. I user AS400 as the data source name but got the error: error 7399: OLE DB provider 'MSDASQL' reported an error. Data source name not found and no default driver specified] OLE DB error trace [OLE/DB Provider 'MSADSQL' IDBInitialize::Initialize returned 0x80004005: ]. What next?
|
|
February 25th, 2005, 05:09 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
DSN setup:
General Tab, System drop-down - I just get a list of AS400 names in this drop-down and I choose one of those. Also click the Connection Options to make sure it isn't sending a windows id by default (use "None" or specify a specific AS400 id)
Server Tab - make sure the naming convention is SQL not system. I also change the default library to be the one that contains the files I want to access, but that's not essential.
Performance Tab - I always have "lazy close" and "compression" checked
Linked Server setup:
All you need is the name of the DSN in the "Data source". Product name, provider string, location, and catalog are just left blank.
hth
Phil
|
|
February 25th, 2005, 05:54 PM
|
|
Registered User
|
|
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks, Phil. I'm getting the same error message. What I am forgetting?
|
|
February 28th, 2005, 06:32 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Are you trying to set this up on a remote or local SQL Server? On which server did you set up the DSN?
|
|
March 1st, 2005, 03:38 PM
|
|
Registered User
|
|
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi Phil. I found out that our sql server doesn't have Client Access/ODBC Admin on it. So I tried it from my local version to the as400 and it worked. Thanks for your help.
|
|
|
