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