Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Radiantwave
Starting Member
2 Posts |
 Posted - 2002-06-07 : 18:14:52
|
| I do not even know if this is possible so I have come to the experts... I have a Web site running under Windows 2000 that has access to a SQL 2000 server. The Currently we use extracts from an AS400 that are imported into SQL 2000 every night using DTS. this data is then provided via Sql calls to the web server. We have recently web enabled the AS400. With this capability I can access the same data from the web enabled AS400 in an XML format. Our web site cannot make calls the AS400 for security reasons. I have read things on how SQL 2000 can access XML data. What I would like to do is use SQL 2000 as a conduit for this data by retreiving the XML data on a transactional basis from the AS400 and route this information to our Web site via a stored proc. Is this possible? If so are there any resources on how to do this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-07 : 18:27:26
|
| Well, I have to ask this: why did you web-enable the AS/400 machine if the web server cannot access it for security reasons?Also, if the data is being DTS'd from AS/400 to SQL Server, why query the AS/400 machine afterwards? Unless the data is updated frequently, it's far easier to get it from SQL Server directly. You could even set up a scheduled DTS job, or replication, to keep the SQL Server data up to date. Or set up a linked server on your SQL Server to link to the AS/400 machine, which can then be queried as if it were a regular SQL Server database.To be honest, an XML bridge between web server-SQL Server-AS/400 would be the absolute LAST way I would do something like this. It's not that it won't work, it's just that it's the least efficient of the methods that are available. In fact, I don't see any benefit to having SQL Server in the process at all, if the AS/400 can generate XML, the web server should just query it directly. There are many ways to ensure security between the two machines.Take a look in SQL Server Books Online for "DTS", "replication", "linked servers" and "XML" and familiarize yourself with each of their functionalities. If you have to incorporate SQL Server into this process, review all of these options before you go the XML route. |
 |
|
|
Radiantwave
Starting Member
2 Posts |
Posted - 2002-06-07 : 18:47:11
|
| The SQL server and the AS400 sit about 20 feet from each other on the same network. There is a firewall between the web server and this network. The As400 contains medical information that is EXTREMELY sensitive. Direct access to the As400 from the web server is somthing that corporate security starts going into convuslions and foams at the mouth about. It is an ugly sight... We currently do DTS loads nightly but are dealing with an imense number of records, multi multi millons. We have at most 100,000 members who use the website in any given monthly period. The information changes constantly. You mentioned using a linked server. I did not even know that this was possible. I will research these other topics. Thanks for the wave off of on the XML idea. |
|
|
|
|
|
|
|
|
Sql 2000 accessing Enterprise XML Data