Hey Keith,

Never tried it with an XML document, but you may be right ... and if so, that might be the way to go when (if) I ever convert this to a .NET utility instead of using VFP ... not gonna happen any time soon though, too much other stuff that needs to get done. <g>

~~Bonnie



>>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
>
>Hey Bonnie, that looks like it would work really well with XML too. An XML document where each schema change element has an attribute with the version # and the actual script as the contents of the element. Just have to make sure that the document is set to preserve whitespace.