Problems With DTS Migration of Database

I am trying to migrate a database from one server to another. The existing database is running under MS SQL Server 7.0, and the new one is running under MS SQL Server 2000.

I'm using SQL Server 7.0's Enterprise manager to transfer the database using DTS.

Both databases are on large SQL servers, shared by many users at the ISP, meaning that my SQL login is not the dbo, and has full permissions to the specific database, but not the the SQL server itself ( ie. I can't add logins, databases, etc. )

When I try to copy the tables from the existing database, to the new one running 2000 I get the following error:

::Error::
In file in statement on line 0
Transfer Status: Beginning Transfer of Data
[Microsoft][ODBC SQL Server Driver][SQL Server]Only members of the sysadmin role or the database owner may set database options.


I've told DTS to "transfer objects and data between sql server 7.0 databases". Further, under "options" I deselected "Transfer database users and database roles", "Transfer SQL Server Logins", and "Transfer object-level permissions"

I get the same error to matter what tables/views/stored procedures I try to transfer, and no matter which options I seem to have selected..

What is causing this error? Is it simply that I'm using SQL 7.0 DTS ( w/ SP4 ) to do the transfering from a SQL 7.0 to a SQL 2000 database?

Thanks!

BriceBriceB,

You can`t transfer objects, because your are not a member of sysadmins.

The DTS try to keep some properties, that only this members can create!

------------------------

You may try this too:

Generate all Scripts (tables, SP, triggers, Contraints, etc) and execute then in your new SQL Server.

Then, use DTS only to transfer DATA to the new server.|||Diogo,
Thanks for the response. As you probably know, I am a complete newbie to MS SQL & DTS alltogether.

I don't understand the difference between "objects" and tables/sps/etc..

Also, how do you have DTS only transfer "data" to the tables/sps/etc. I've created using scripts? Under my understanding, DTS offers 3 options:

1) Copy Table(s) from source database
2) Use Query
3) Copy Objects and Data

I guess under #3 I should deselect create destination objects, and transfer all objects, then go into options and deselect all the security options??

Thanks,

Brice|||Make this:
In the SQL 7.0
- Right-Click on the Database, "All Tasks", "Generate SQL Script"
- Then, click on the button "SHOW ALL"
- Select "Script All Objects"
- Then, go to the OPTIONS TAB
- Mark, "Script Indexes", "Script full-text indexes", "Script Triggers", "Script PRIMARY, keys, FOREIGN keys..."
- Now, click OK
- Save this file.

Now, open SQL Query Analyzer and connect to your new Database in SQL Server 2000
- Open the saved File (script from database objects above)
- execute it! (Is should create all tables, SP, etc.;)

ufa!

Now,
Go to DTS, and when he ask for you:
1) Copy Table(s) from source database <--
2) Use Query
3) Copy Objects and Data

Your answer will be 1)!!!!

He will copy all the data from the source tables to the destination tables.

------------------------

You can do this choosing 3) too! But you shouldn`t execute the created script;

Your answer should work too! But I like to create the scripts and execute them. Therefore, bring Data!