SQL DMO Install new database

[zemp]

I have two questions. first how can I tell that the SQL Server client (SQL DMO) is installed on the target machine and that the servers are properly listed within a server group?

Second, how would I use SQL DMO to create a new database? I can find information on how to create tables but not a new database.

I am using VB 6.0 EE SP5 and SQL Server 2000. Developing on Win 2000 Pro.
Thanks and Good Luck!

zemp

 

[zemp]

After much searching I finally got what I was looking for. Thanks to all who had tried to help or were going to help.

I'll post it below.

Private Sub Create_Database(DatabaseName As String, ServerName As String, Logon As String, Password As String, Delete As Boolean, Secure As Boolean)
'// Create the database.
   Dim sngTime As Single
   Dim sngPauseTime As Single
   Dim objServer As SQLDMO.SQLServer
   Dim objDataBase As SQLDMO.Database
   Dim objDBFile As SQLDMO.DBFile
   Dim objLogFile As SQLDMO.LogFile
    
   On Error GoTo ERR_CreateDatabase
    
   Set objServer = New SQLDMO.SQLServer
   objServer.Name = ServerName
   If objServer.Status = SQLDMOSvc_Stopped Then
      objServer.Start True, ServerName, Logon, Password
      DoEvents
      sngTime = Timer
      sngPauseTime = (60 * 3)
      Do
         If Timer > sngTime + sngPauseTime Then Exit Do
      Loop Until objServer.Status = SQLDMOSvc_Running
   Else
      objServer.LoginSecure = Secure
      objServer.Connect ServerName, Logon, Password
   End If
    
   For Each objDataBase In objServer.Databases
      If UCase(objDataBase.Name) = UCase(DatabaseName) Then
         If Delete Then
            Set objDataBase = Nothing
            objServer.Databases(DatabaseName).Remove
            objServer.Databases.Refresh
         Else
            MsgBox "Database Already Exists"
            GoTo CreateDatabaseExit
         End If
         Exit For
      End If
   Next objDataBase
    
   '// Create the file object for the new database
   Set objDBFile = New SQLDMO.DBFile
   objDBFile.Name = DatabaseName & "_Data"
   objDBFile.PrimaryFile = True
   objDBFile.PhysicalName = objServer.Registry.SQLDataRoot & "\Data\" & objDBFile.Name & ".mdf"
   objDBFile.FileGrowthType = SQLDMOGrowth_MB
   objDBFile.FileGrowth = 1
   objDBFile.Size = 2
    
   '// Define the database transaction log.
   Set objLogFile = New SQLDMO.LogFile
   objLogFile.Name = DatabaseName & "_Log"
   objLogFile.PhysicalName = objServer.Registry.SQLDataRoot & "\DATA\" & objLogFile.Name & ".ldf"
   objLogFile.FileGrowthType = SQLDMOGrowth_MB
   objLogFile.FileGrowth = 1
   objLogFile.MaximumSize = 10
   objLogFile.Size = 2
   
   '// Create the actual database.
   Set objDataBase = New SQLDMO.Database
   objDataBase.Name = DatabaseName
   objDataBase.FileGroups("Primary").DBFiles.Add objDBFile
   objDataBase.TransactionLog.LogFiles.Add objLogFile
   objServer.Databases.Refresh
   objServer.Databases.Add objDataBase
    
CreateDatabaseExit:
   Set objDBFile = Nothing
   Set objLogFile = Nothing
   Set objDataBase = Nothing
   objServer.DisConnect
   Set objServer = Nothing
   Exit Sub
    
ERR_CreateDatabase:
   If Err <> 0 Then
      Dim FileNum As Integer
      FileNum = FreeFile
      Open App.Path & &quot;\SQLLog.txt&quot; For Append As #FileNum
      Print #FileNum, vbCrLf & Err.Description
      Close FileNum
   End If
   If Err.Number = -2147203048 Then
      MsgBox &quot;Login Failed&quot;
   Else
      MsgBox Err.Number & &quot;: &quot; & Err.Description
      Resume Next
   End If
   Resume CreateDatabaseExit
    
End Sub

Thanks and Good Luck!

zemp