SMO could not be used copy sql 2000 database?

Question

Does any one know if SMO api should or should not be used to copy sql 2000 database?

If not what I should use.

I tried use it in ssis script task, and it did not work for me. I don't know if it is supported or just something I am doing wrong.

 

Thanks!

Answer 1

Hello,

More generally, SMO can be used versus databases 2000 ( version 8 ).
But some features of SMO cannot be used because they are specific to SQL Server 2005 or 2008.

Please, could you give us more informations ?
- the version of your SQL Server instance ( including SP ) and the compatibiltylevel of your databases used for the copy
- the version of your SMO ddl ( 2005/ 2008 including SP )
- an example of your SMO code and the generated error ( the complete text of the error would be helpful )

We are waiting for your feedback to try to help you more efficiently

Have a nice day

---

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

Answer 2

Sorry, I was busy on other issue for a while.
 here is my information:

- 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
- 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 = "ppenzance"
        conn.Password = "pet*nias"
        conn.ServerInstance = "slcsqltest1"

        'Connect to the remote SQL Server.
        Dim srv As Server
        'srv = New Server("slcsqltest1")
        srv = New Server(conn)      

        '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.Drop()
        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.DropDestinationObjectsFirst = True

        xfr.CopyAllRoles = False
        xfr.CopyAllUsers = False
        xfr.CopyAllLogins = False
        xfr.CopySchema = True
        'Include data
        xfr.CopyData = False

        Dim sCollection As Collections.Specialized.StringCollection = xfr.ScriptTransfer()
        MsgBox("Start to write script...")
        PrintValues(srv, sCollection)
        MsgBox("Done with script! collection size : " + sCollection.Count.ToString)

        'Execute the transfer
        xfr.TransferData()
        Dts.TaskResult = Dts.Results.Success
    End Sub

 

The problem I have seen that it did not respect dependency at all. It is trying to create view without referencing table created at all. I have script printed out.


ERROR : errorCode=0 description=Invalid object name 'dbo.Client'. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

Thanks!