Don,
We use a little VFP utility I wrote a few years ago. This is shipped with our .NET product. Basically, we store all the T-SQL scripts to change the database schema in a VFP table ... each row in the VFP table has a column that tells which "version" the schema change is for, along with the T-SQL in a memo column. There is a table in the SQL database that contains a column with the version that the SQL datasbase currently is at. The VFP utility reads the SQL database's version and, if it's out-of-date based on the VFP table's version, it will apply all the schema changes after that version to the SQL database.
Here's a little blurb I wrote a few years ago showing how to do some of this using SQL-DMO. It's not the full utility ... some is left as an exercise for the reader <g> ... but I think it's enough to show you how to use SQL-DMO along with T-SQL schema change scripts, to maintain the SQL database.
********************************************************************
* 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.
*
*
* is of course, just create an instance and play with it with
* Intellisense:
********************************************************************
oSqlDMO AS SQLDMO.SqlServer
oSqlDMO = CREATEOBJECT("SQLDMO.SQLServer")
********************************************************************
*
********************************************************************
oSqlDMO.Connect("MyServer", "MyUserName", "MyPassword")
*
* 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.
********************************************************************
*
********************************************************************
oSqlDMO.Disconnect()
********************************************************************
*
* 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()
*
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