Hi Keith and Bonnie.

I see what you mean. After reading your answer, I then wrote to the StrataFrame support questioning them on the subject according to your ideas. So, just for your information, this is what I got:

"That is the beauty of StrataFrame’s Database Deployment Toolkit. It not only supports source control, it also supports SQL Server backups and can even be re-imported from the package file that gets produced for deployment purposes. Secondly, it is truly in a single location and does not require you to go more than one place to get to all of the meta-data. Also, comments can be made on every field if the need arises. Third, you won’t have any sync issues because it uses SMO (SQL Server Management Objects) which is the same approach Microsoft themselves take. SQL Server Management Studio itself uses SMO, not scripts. Fourth, you can upgrade a database without worrying that scripts are executed in a proper sequence. Just give it the meta-data package file and step back. Last, you can include deployment data, such as postal codes, as well. This too is stored in the same package file, making data schema deployment an extremely easy process from stem-to-stern."

Best.

Ivan



>Hi Ivan,
>
>Personally I tend to stay away from database management kits that perform schema changes because I like to keep the scripts in files that can be checked into source control, shadow-copied, and backed up. And since all changes start from hand-written scripts, I don't have to worry about the difference analyzer picking up any extraneous objects that are scattered around the development environment. It also lets me put lots of comments inside the scripts for myself and anyone that may come in behind me to work on the project.
>
>I used to feel that maintaining scripts was outdated and a terrible waste of effort when there were so many good tools around to do it automatically. But I've become a convert in the last year or so after seeing all of the different sorts of problems that pop up when working in a multi-developer, multi-project environment and relying on tools to deploy database changes.
>
>>Hya.
>>
>>Just to add my new experience in deploying and maintaining SQL structures ... I'm using the Database Deployment Toolkit from the guys of StrataFrame (www.strataframe.net) and I'm enjoying it a lot.
>>
>>Cheers.
>>
>>Ivan
>>
>>
>>>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.