VB6, SQL 2005 & DMO

• • Reply

  11 years ago

  by elajaunie3

  Emmet LaJaunie

  Jefferson, United States

  Joined 11 years ago

  I have a TimeClock program that works on a SQL 2000 Server, in which I use DMO to create a database and set user roles and things like that, so that the user doesn't have to go through any SQL server management programs. I'm trying to convert this program over to SQL 2005, but my setup DMO commands just aren't working.

  The way this part works is that it creates the database in the server and then restores it from a backup. IIRC, it wouldn't create a database directly from the restore, I had to create the database initially.

  This code all works on SQL 2000, but doesn't work on SQL 2005. How can I do this in SQL 2005?

          On Error GoTo SetupError
              Dim oServer As SQLDMO.SQLServer2
              Set oServer = New SQLDMO.SQLServer2
              oServer.Connect ServerName, UserName, Password
              oServer.ExecuteImmediate "CREATE DATABASE timeclock"
              oServer.ExecuteImmediate "GO"
              oServer.ExecuteImmediate "RESTORE DATABASE timeclock FROM DISK = '" & App.Path & "\tc_dist.dat' WITH REPLACE"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addlogin 'timeclockuser', 'clock', timeclock"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_grantdbaccess 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_owner', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_accessadmin', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_securityadmin', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_ddladmin', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_backupoperator', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_datareader', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_datawriter', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.Databases("timeclock").ExecuteImmediate "EXEC sp_addrolemember 'db_TimeClockUser', 'timeclockuser'"
              oServer.ExecuteImmediate "GO"
              oServer.DisConnect
              MsgBox "Database successfully installed!", vbOKOnly, "Success"
  

  Report abuse
• • Reply

  11 years ago

  by elajaunie3

  Emmet LaJaunie

  Jefferson, United States

  Joined 11 years ago

  I figured it out. I'm developing with SQL 2005 Express, so I had to change: "oServer.Connect ServerName, UserName, Password" to "oServer.Connect ServerName & "\SQLEXPRESS", UserName, Password"

  That got everything running how it's supposed to.

  Report abuse