Unanswered: Issue with Transfer method of DMO database object
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:
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
RaiseEvent UpdateStatus("Create New SQLServer Database")
oSQLSVR2.Connect(JobSpecs.SQLServerName, "ADMINUID", "ADMINPWD")
nDatabase.Name = .DatabaseName
nDBFile.Name = .DatabaseName*
nDBFile.PhysicalName = .DatabaseName