Internet 2005 db copy across data

Question

Internet 2005 db copy across data

Adam Sandler

SSCrazy

Points: 2697
More actions
February 4, 2008 at 8:49 am

#186191

Sorry if this is really dumb -
I have my 2000 db, and I want to get data from tables on a internet linked 2005 db. How do I do it.
I have SQL Server Enterprise manager - I can't seem to connect to the 2005 server with this. I also have SQL SErver Management Studio Express, which I can set up to see the db OK.
How do I write something for my 2000 db - to grab the data, by criteria, and append it to the correct place with 2000.
(This will have to work form multiple seperate instances of the 2000 db)
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Have you tried using DTS on the SQL 2000 instance to import from the SQL 2005 database or SSIS to export to SQL 2000? While you can't register 2005 instances in Enterprise Manager, you can connect to 2005 in either a DTS package or the Import/Export Wizard.

Greg

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Can you connect with Query Analyzer?

If not, you need to be sure that the connections work. The easier way is to transfer data from 2005->2000. You can select the data from QA and save it, then load it into 2000.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Thanks both of you - I originally tried DTS but couldn't get the connection, I assumed it was a 2005 problem like ep connecting to the 2005 server.

Anyhow thats sorted now - so I can use DTS in the 2000 to get data from 2005, and I can connect thru query analyzer.

I have no experience of this whatsoever - I have a couple of quesions.

1) Firstly , I will be providing a solution for multiple instances of the 2000 db (at differant sites) which will collect data form the web 2005 db. - I think this may mean that pulling the data may be easier than pushing. ( I have control of the 2000 dbs not the 2005 one)

2) I need to read an ID value in a table on the 2000 dbs - then pull the data with this ID assigned per record into the 2000 dbs - I asume DTS can do this.

Then manipulate data - maybe prosessing it dependeing on the data to be posted /existing data.

3) Over time some of the 2000 db may move to 2005 - will this make DTS unuseable - ie do I have to use SSIS? - Can I build something which works for both 2000 and 2005.

4) Can I schedule to happen - every 10 minutes say - maybe a job?

5) Wheres the best place to read up on the above/ etc

Thanks very much, any advice is very gladly received!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Adam,

1) I think you're right about pulling rather than pushing the data if you can't put a package and a job on the SQL 2005 instance.

2) You can select the ID in an Execute SQL task and use it's value to set an output global variable in the package. Then use the global variable as an input value for the Transform Data task that selects data from the web db.

3) DTS will still work in SQL 2005 as long as you install the SQL 2000 DTS runtime components on the server where SQL 2005 is installed. You can download it free from MS's download site.

4) You can schedule a package in a job by right-clicking the package in Enterprise Manager and following the instructions in the wizard. You can also add the execution of a package as a step in an existing job.

5) I recommend http://www.sqldts.com/ as a source of information. There are also several good books available. I use Professional SQL Server 2000 DTS.

Greg

Adam Sandler SSCrazy Points: 2697 More actions · Answer 5

Thanks very much Greg, I now have a bit of a clue what I'm doing, and a lot of reading up to do.

Thanks