>>>******************************************************************** >>>* 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