DTS Fails

[lawre016]

I have 2 SQL 7.0 Servers, I am trying to copy a DB from one to the other using DTS Export Wizard, I choice the 'Transfer Objects and Data...' option so I get all the stored procedures etc... During the transfer I receive an error "Login already has an account under a different user name". I think all my usernames and pw are the same on both servers.

Am I missing something? Any help would be great.

Thanks.

 

[MaxZak]

I think that there is a Windows login that has 2 different SQL accounts in the servers. Right?

 

[JUDGE27]

Simple Soloution,

Dettach the database using the sp_detach_db stored procedure.

Copy the database files to the Destination server and
reattach using sp_attach_db stored procedure.

Using sp_addalias to link the current login on the destination server with the user in the database.

This method will also tranfer objects assigned with the database excluding the logins from master as the DTS package has done

 

[tlbroadbent]

Avoid sp_addalias. Aliases are obsolete in SQL 7 and higher. When using sp_detachdb and sp_attachdb to copy databases, use sp_change_users_login to resynch users with logins.

Does the database you are copying already exist on the destination server? If so, check for a login that maps to a different user name on the destination database than it maps to on the source database. For example, if there is a login joe that is user1 in the source database but is user2 in the destination database you would get the error.

You may be able to run the DTS transfer without copying users and logins if the database already exists and all you wnat to do is refresh objects. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.