transfer database task error
hi, I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error: Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter. Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB? thanks, Derek
May 11th, 2006 10:28pm

sure enough, when I run profiler against the destination sql server after running create database and then a few alter database to set it's option it immedielty tries Create Role without first considering the database it belongs to in the source.
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2006 10:52pm

and i am using the online action/mode of XFerDB Task.
May 11th, 2006 10:56pm

Derek - I've been having the same problem - and have an euivalent post from back in March - If you find out what's going on (or a workaround, etc.) - please post an update - thanks!
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2006 11:44pm

This issue has been identified as a bug and we are working on it. Hopefully the fix will be made available in the next release.
June 14th, 2006 1:22am

Kaarthik, Who identified it as a bug? Can you point me to some Microsoft info. Is some workaround available? Thanks
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2006 7:17am

I was able to work around this by usinga Backup Database Taskpaired with a Execute SQL Task. You use the Backup Database task to backup your entire database into a .bak file on a specific disk location and name. You would then use the Execute SQL Task to drop and then restore the database that would be your destination. Below is the SQL forjust that... EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'destinationDB'GO USE [master]GO /****** Object: Database [destinationDB] Script Date: 08/30/2006 20:05:15 ******/DROP DATABASE [destinationDB]GO RESTORE DATABASE [destinationDB] FROM DISK = N'C:\DatabaseBackups\destinationDB.bak' WITH FILE = 1, MOVE N'destinationDB_dat' TO N'E:\MSSQL2005\destinationDB.mdf', MOVE N'whd_log' TO N'E:\MSSQL2005\destinationDB.ldf', MOVE N'sysft_whdCatalog' TO N'E:\MSSQL2005\destinationDB', NOUNLOAD, REPLACE, STATS = 10GO
August 31st, 2006 6:17am

I'm having the same problem, even after SP1. Jarret
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2006 10:27pm

Hello Microsoft, I still have the problem that has been described by other messages in this forum: that if the transfer property "DatabaseOnline" is "True" and the Source and Destination Databases are the same, then an error is thrown by SSIS because it attempts to create database instance user accounts that already exist. I've applied SQL Server 2005 Service Pack 1 + the post Service Pack 1 hotfixes: Cumulative hotfix package (build 2153) for SQL Server 2005 (http://support.microsoft.com/kb/918222). The problem still exists after the hotfix package has been installed. An alternative workaround is to transfer the database whilst it is offline - but this is a problem because SQL Server detaches the database, copies the MDF and LDF files, then reattaches the database and attaches the new destination database. This process requires that nobody can be connected to the Source database. Is there an estimate as to when Microsoft will fix the issue? Kind regards, Jonathan.
November 16th, 2006 4:25pm

We are testing the fix and planning to release it in SP2. It should be available in SP2 CTP2.
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2006 8:34pm

Here I am, one year later, 11-9-2007, running SP2, 9.0.3042, and still unable to copy database due to this very same error of user already exists in the database. It's incredible.Hello MS! Are there anybody home?
November 10th, 2007 2:23am

I take it back. My server was at SP2 level, but my client desktop components was at SP1 level. Once I upgrade the client side componentsto SP2, the problem is resolved. I wish it was made clear that this is a client sideproblem. Anyway, my apology.
Free Windows Admin Tool Kit Click here and download it now
November 20th, 2007 6:27pm

Hi I'm having the same problem. How did you update the client components? I created the transfer DB SSIS package and I am running it on the actual SQL server. The version is up to SP3.ThanksTKE402
September 12th, 2009 12:16am

I'm getting the same issue with sql2008 r2 Has there been a solution yet ?? fatherosam
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 7:31am

I tried this with SQL2008 R2 and get the same result. Please has anyone fixed this???
October 25th, 2010 11:54pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics