Copying data from a foreign database to SQL Server 20000

[tfstom]

I am going to be setting up some Stored procedures that will be copying data from a foreign database that will have an ODBC connection.

This task will need to be executed at night or from a .NET web page and I was wondering what would the best way to copy the data from this procedure.

It won't be necessarily be copying all the data from the database, but could copy selected tables at different times.

Is this easily done from a Stored Procedure?

Thanks,

Tom.

 

[RonMcNatt]

Why don't you use a DTS Package to copy the data into SQL Server, then you can schedule the package to run nightly.

Look in the Data Transormation Services Folder in SQL to create the package, then look in the Management Folder for SQL Server Agent to create a job that will execute the package at your requested time(s).

Ron

 

[tfstom]

But they want to be able to run this from the web, using .NET.

Is there a way to run this from a stored procedure?

The problem they are having is that this is that the source is some foreign database that has an ODBC driver written for it. The have been having it move data from the database into Access using this driver and have been having problems with some of the data. Don't know if it is an ODBC driver problem or not. Could be Access. If I have the same problem going to Sql Server 2000, I assume that it would be the driver.

Tom.