run DTS for SQL2005 2

[Cirrus9]

I have a DTS running on a local server. It converts an mdb to SQL by droppping all tables, recreating them and loading the data. I load these tables onto a SQL2000 hosting server for a website.

I have about 250MB of data in the mdb and it peaks the transaction file on my hosting server at 875MB. I am only allowed 500 so I have to find an alternate method.

I asked if I could turn off transction log services but I found out that in SQL2000, It turns on or off for the entire server. The hosting company won't let me do that but they tell me that in SQL2005 you can designate transcation log services at the db level and they can move to 2005.

My DTS is written on a SQL2000 machine and I need to know if I can connect to SQL2005 server and run a DTS package from a SQL2000 server.

I can use any suggestions or ideas that anyone may have. I am in a serious time crunch. Thanks for all of your help in advance

 

[onpnt]

Temporarily change the recovery model to simple

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[Cirrus9]

I don't have an interface for the SQL server that will allow me to do it. I have to call them every time I want to go to simple mode and then they set it back when I am finished. The DTS is set to run every night.

 

[onpnt]

You can set the recovery mode with T-SQL using a SQL Taks

ALTER DATABASE [databasename] 
SET RECOVERY SIMPLE 
GO

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[onpnt]

That depends on your security level of course

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[SQLDenis]

you can execute a 2000 dts package in 2005

create a new SSIS project and add a Execute DTS 2000 Package Task

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Cirrus9]

I read about the SSIS project to run a DTS package and I asked the hosting company but I won't have control over the 2005 server. I am trying to execute the package to run from 2000 and insert the records into a 2005 server. Will that work?

 

[onpnt]

yes. in reality it is just another data store at that point

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[Cirrus9]

Great I wanted to be sure before I made a change over to a new server. I appreciate your help guys!