faile copy database on same sql server (2005) error out on user or role even with CopyAllRoles set false

Question

I am running this code as script task in ssis 2005
here is my code:
-----------
Public Sub Main() 

 

Dim dbSourceName As String = "YSCMII"

 

Dim dbDestName As String = "YSCMII_COPY" 

 

Dim conn As New ServerConnection

conn.LoginSecure =

False

conn.Login =

"user"

conn.Password =

"password"

conn.ServerInstance =

"slcsqltest1"

 

'Connect to the remote SQL Server.

 

Dim srv As Server

srv =

New Server(conn)

 

'The actual connection is made when a property is retrieved.

Console.WriteLine(srv.Information.Version) 

 

'Reference the source database

 

Dim db As Database

db = srv.Databases(dbSourceName)

 

'Create a new database that is to be destination database.

 

Dim dbCopy As Database

dbCopy =

New Database(srv, dbDestName)

 dbCopy.Create() 

 

'Define a Transfer object and set the required options.

 

Dim xfr As Transfer

xfr =

New Transfer(db)

xfr.CopyAllTables =

True

xfr.Options.WithDependencies =

True

xfr.Options.ContinueScriptingOnError =

True

xfr.DestinationDatabase = dbCopy.Name

xfr.DestinationServer = srv.Name

xfr.DestinationLoginSecure =

True

xfr.CopyAllRoles =

False

xfr.CopySchema =

True

 

'Include data

xfr.CopyData =

False

 

 

'Execute the transfer

xfr.TransferData() 

Dts.TaskResult = Dts.Results.Success

 

End Sub

 

 

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

Error I got as following:

script Task: The script threw an exception: ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @loginame = N'ppenzance', @name_in_db = N'ppenzance'

" failed with the following error: "User or role 'ppenzance' 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}

Task failed: Script Task

Thanks!

Answer 1

Hello,

I would suggest you to use a method very little known of the class Transfer
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.scripttransfer.aspx

For that, you coul have a look at these 2 links :

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.sqlexecutionmodes.aspx
and

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.sqlexecutionmodes.aspx

in the first link , you will find an example of code
You will retrieve the the T-SQL statements created by your code with the CaptureSql property of your ServerConnection . You should have a look on this link :
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.connectionmanager.capturedsql.aspx

If you display the generated T-SQL statements, you should find the "bad" code.
You could verify whether ppenzance is already defined in the destination database

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Thanks for replay. I was able to stream out script code and get around error of
 User or role 'ppenzance' already exists in the current database
by switching using window authetication.
But still I could not copy database due to the script it generated did not respect denpendency at all. I guess this is problem with 2000 server? Does anyone have samiliar problem with copy sql 2000 server database?


Thanks again!

Answer 3

Hello,

I am not sure that i have understood your question ( i am not good at all in English )
Please , could you have a look on this link ? ( it is related especially with the separation user-schema introduced with SQL Server 2005 )
http://msdn.microsoft.com/en-us/library/ms170800(SQL.90).aspx
I am not sure , but i think that the notion of roles is unknown in SQL Server 2000

Please, could you explain the aim of your script ? ( what are the versions of the 2 instances ? I think i have understood that the "to instance" is 2005 and the "from instance " is 2000 . If i have misunderstood you, i hope you will not be angry )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Thanks for the reply!
Sorry I have mislead you. I am trying copy a database on the 2000 to a new database on the same server. I have seen a couple of issue from this simple operation
1> when I am using sql authentication, even I set copyAllUsers and copyAllRolls false. It still failed on error: failed with the following error: "User or role 'ppenzance' already exists in the current database
2> By switching using window authentication to connect the sql server and transfer data, it pass previous error, but failed again due to the script it generated did not respect dependency of the object. For example, it try to create view which reference a table that has not been generated yet.

- the database I was tried to copy on SQL Server Standard Editon 8.00.2039(SP4)
- the version of my SMO ddl is on 9.0.242.0

I have post similar question on the other theads: 
http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/b71430cb-05f4-4aaf-a4eb-b150b7cd3146

Thanks!