Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: Issue with Transfer method of DMO database object

    Hello all,
    Hoping someone has an answer as I have pursued all avenues to no avail! Environment: SQLServer 2000 (SP3) with security patch applied. Problem: Prior to SP3 installation, was able to create new databases AND copy all objects(views,stored procs,etc) from a "master" database to newly created one. Post SP3 the code(which I will supply below) no longer works and the .NET application aborts without capturing/handling the error; SQL does NOT record any information to the logs. The new database is created, however, once I try to execute the statement to transfer/copy objects from the master to new, the application ends abnormally.Cross-database chaining is turned on; owner of "master database" is the same as the newly created one.

    I have localized the application abort to this statment:

    oDatabase.Transfer(sqlTransfer2)

    Let me further add that the code below works flawlessly and without incident from my development machines(WinXP and Win2000), both of which include the SQLServer personal edition WITHOUT SP3 installed. At this point, I cannot upgrade to SP3 on these machines as this is production code and MUST run at least once a week.

    Finally, using the "model" database to house the "master database" objects is not an option. If anyone can suggest a better method to copy all objects from one database to another without user intervention and programatically, please post back here. Thanks in advance!



    Here is the DMO code to copy from my "master database" to newly created one.
    Private Sub CreateNewDB()
    Dim oSQLSVR2 As New SQLServer2()
    Dim oDatabase As SQLDMO.Database
    Dim nDatabase As New SQLDMO.Database()
    Dim nDBFile As New SQLDMO.DBFile()
    Dim sqlTransfer2 As SQLDMO.Transfer2



    Try

    RaiseEvent UpdateStatus("Create New SQLServer Database")
    oSQLSVR2.Connect(JobSpecs.SQLServerName, "ADMINUID", "ADMINPWD")

    With JobSpecs
    nDatabase.Name = .DatabaseName
    nDBFile.Name = .DatabaseName*
    nDBFile.PhysicalName = .DatabaseName
    End With

    oSQLSVR2.Databases.Add(nDatabase)

    oDatabase = oSQLSVR2.Databases.Item("AV_CV_Master")

    sqlTransfer2 = New SQLDMO.Transfer2()

    With sqlTransfer2
    .DestServer = JobSpecs.SQLServerName
    .DestUseTrustedConnection = True
    .DestDatabase = JobSpecs.DatabaseName
    .CopyAllObjects = True
    .IncludeDependencies = True
    .IncludeLogins = False
    .IncludeUsers = True
    .DropDestObjectsFirst = True
    .CopySchema = True
    .CopyData = 1
    .SourceTranslateChar = True
    .DestTranslateChar = True
    .UseDestTransaction = False
    .UseCollation = False
    End With


    oDatabase.Transfer(sqlTransfer2)

    oSQLSVR2.Close()

    Catch AVException As Exception
    MessageBox.Show("Error ! " & AVException.Message, "CVDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
    Throw New Exception(AVException.Message)
    Finally

    sqlTransfer2 = Nothing
    oDatabase = Nothing
    nDatabase = Nothing
    nDBFile = Nothing
    oSQLSVR2 = Nothing
    End Try


    End Sub
    Last edited by ainkles; 10-27-04 at 16:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •