>>>>******************************************************************** >>>>* We've primarily used SQLDMO to do things that were easier to do >>>>* with it, then with VFP. Executing scripts in particular is much >>>>* easier with SQLDMO. Things like creating a Database we continued >>>>* to do with VFP. At some point, our utility needs to be transported >>>>* to .NET, and I expect that I'll be having to figure out how to do >>>>* more with SQLDMO, but for now, here's the few things I've learned >>>>* to do. >>>>* >>>>*-- The easiest thing that you can do to explore the object model >>>>* is of course, just create an instance and play with it with >>>>* Intellisense: >>>>******************************************************************** >>>> >>>>oSqlDMO AS SQLDMO.SqlServer >>>>oSqlDMO = CREATEOBJECT("SQLDMO.SQLServer") >>>> >>>>******************************************************************** >>>>*-- First, this is how you connect: >>>>******************************************************************** >>>> >>>>oSqlDMO.Connect("MyServer", "MyUserName", "MyPassword") >>>> >>>>*-- I haven't figured out how to verify this connection. I think it >>>>* throws an exception if it doesn't get a good connection, but I >>>>* haven't tested that. If so, you can trap for errors. >>>>* >>>>* I've just used the old method of connecting with VFP's >>>>* SqlStringConnect() at least let's you know if your connection >>>>* was successful, so until I get around to figuring it out >>>>* I've been connecting with SqlStringConnect first, then using >>>>* SqlDMO. >>>>* >>>>* Also, haven't figure out how to suppress messages, as the >>>>* SqlSetProp() does. Need to figure this stuff out eventually. >>>> >>>>******************************************************************** >>>>*-- And of course, don't forget disconnecting: >>>>******************************************************************** >>>> >>>>oSqlDMO.Disconnect() >>>> >>>> >>>>******************************************************************** >>>>*-- Run a script against an existing Database. In our case, we >>>>* have a VFP table containing all the scripts we need to run against >>>>* our SQL database in order to update it to our current version: >>>>******************************************************************** >>>> >>>>oDatabase AS SQLDMO.Databases >>>> >>>>oDatabase = oSqlDMO.Databases("MyDataBase") >>>>oSqlDMO.BeginTransaction() >>>> >>>>*-- need to catch errors gracefully, so that we can rollback transaction >>>>oops = .F. >>>>ON ERROR oops = .T. >>>> >>>>SELECT sqlscript >>>>SCAN >>>> WAIT WINDOW 'Running script ' + scriptkey NOWAIT >>>> oDatabase.ExecuteImmediate(sqlscript.scripttext) >>>> IF oops >>>> AERROR(aa) >>>> EXIT >>>> ENDIF >>>>ENDSCAN >>>> >>>>ON ERROR >>>>WAIT CLEAR >>>> >>>>IF oops >>>> oSqlDMO.RollbackTransaction() >>>> lcMsg = "Script " + sqlscript.scriptkey + " had the following problems: " + ; >>>> CHR(13) + CHR(13) + aa[2] + CHR(13) + CHR(13) + ; >>>> "The database has been rolled back to it's previous condition, " >>>> MESSAGEBOX(lcMsg, MB_OK + MB_ICONSTOP) >>>>ELSE >>>> oSqlDMO.CommitTransaction() >>>>ENDIF >>>> >>>> >>>>******************************************************************** >>>>* Removing a database from SqlServer is a permanent and irreversible >>>>* action, so be sure you really mean it!! >>>>******************************************************************** >>>>oDatabase = oSqlDMO.Databases("MyDataBase") >>>>oDatabase.Remove() >>>> >>>> >>>>******************************************************************** >>>>* Good enough for now to get you started. Have fun playing!! >>>>******************************************************************** >>>>>>>>~~Bonnie