Inserting info from one DB to another... 1

[dcusick]

What's up all? I have a question for all of you out there.. I don't see this being possible, but I figured I'd throw it out there in hopes that it might be... I currently have two connections open to two different DB's and want to transfer info from one to the other... I have a connection to the AS/400 and to a SQL Server 2000 DB. Currently, I'm just grabbing the recordset from the AS/400. I then run through that recordset and insert each record into the SQL Server DB. This serves its purpose, but is relatively slow. I know that I could use DTS packages to do this, but I want to be able to update these numbers from an ASP page. Is there any way I can just do a mass insert from one connection to another? Thanks in advance.

 

[MDXer]

have the ASP Page execute a sp to run the dts package.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dcusick]

wow, very valid point! Completely forgot about that... Now this might be better suited in the SQL Server forum, but I'll throw it out there...

Now one quick question on that.. If I setup a DTS package use a DSN to grab the AS/400 data, would I have to have the DSN set up on the DB Server? the Web Server? or the Client Machine? THanks again...

 

[MDXer]

DB Server

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dcusick]

yeah, that's what I was afraid of.... Unfortunately, we don't have the AS/400 ODBC driver installed on the DB server machine, and the admins here refuse to put it on. They claim that it could lock the machine, which would cause downtime.... Well this seems to be starting to become a SQL Server specific question, so I'm gonna head over to that forum. Thanks MDX for the responses... Greatly appreciated.