I need help using DTS Import/Export tool.

I am trying to use MS SQL Server 2000's Export/Import DTS tool to transfer an existing database on my pc to a remote web server. I am able to connect to the remote database that my web hosting package provided (and thanks to all of you who helped with with the connection issue). Now when I selected the option "Copy Tables and Views From Data Source" to do my transfer, the process completed successfully, but when I tried to do some queries, I get incorrect results. It seems like only the "table values" in my source database was copied. The data that describes how these tables are related (ie. constraints, primary keys, foreign keys, etc) seemed to have NOT been copied to the web database.

So I went back to do the transfer again, this time choosing the option "Copy Objects and Data Between SQL Servers". Now the transfer process proceeds smoothly for the first half (the first half seems to be the same as described above -- just copying the table values). But as it started to copy object data (around 59% done or so), it errored out with the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Only members of the sysadmin role or the database owner may set database options"

Then I went back to the options tab, and tried deselecting some of them. I tested a lot of various combinations, and finally I unchecked them ALL. But still I get the same error. Now, I dont know what "database options" it's referring to.

However, the odd thing is that even though the transfer process failed halfway through, my queries seem to be getting correct results now. I havent tested it much but the few that I tried out returned expected results.

My concern now is that why doesnt this tranfer process complete without errors? I'm worried that since there are still problems with the data transfer process, then there must still be (possibly hidden) problems with the "incomplete" copy of the database on the web server.

Can anyone pinpoint the cause of this?in my experience, when transferring multiple tables with DRI from one server to another, there are sometimes problems related to precedence. For example, when you tranfer relationships, keys, etc... the job might fail. What happens is that the process attempts to create the table with FK reference before the primary table. I havent seen the precise error that you are experiencing... but, would make sure that this is not happening to you...

Sometimes permissions are applied before the user has been created, resulting in an error...

you can also script the relationshipes, keys, etc... and apply them manually after all data has been copied.