One thing most database change management tools use is SQL Server Managment Objects (SMO). Most do that because ADO.NET doesn’t allow the batch statement separator keyword GO in sql statements. So most people write off the ability to use a database change tool for sql server without actually having the sql server installed on the machine that is running the tool. This is not a reality in some organizations, especially when licenses for SQL server are limited.
Split the GO
Recently, a few guys have really been helping me get RoundhousE (RH) to the point of not using SMO anymore.I’d like to thank Jochen and Pascal for all of your hard work recently on this and bearing with me while we worked out getting the regex ironed out for splitting sql statements on GO when it isn’t in a comment section.
There only two requirements with GO statements when using RH. The first one (a good practice IMHO) is that GO has to be the last statement on a line. The second is that using GO in between tick marks (‘), like for inserting it into a table, be sure it isn’t the last statement on a given line. This second one is a low possibility, but worth mentioning for those that might structure string insert/updates with GO in them in such a format.
So to recap:
sql statements GO
some more statement action go
Use some database
--something something GO
Not Gonna Work
do something GO do something else
insert into sometable (id, description)
' yep yep. uh huh...ya ya ya ya ya. GO
Favor ADO.NET over SMO
Those that have been using RH know that they can use it with SMO for 2005 and 2008. Being able to switch out either one of these has been a lifesaver for some organizations that have not yet upgraded to sql server 2008. Right now RH is in a transition state where SMO is still accessible while ADO.NET is really feeling it’s way out. So if you are using sql2005 or sql2008 now, you can continue doing so. Keep in mind that those will be deprecated in favor of ado.net at some point.
Go check it out! To use the new ado.net version, just change the databaseType (/dt in console) to “sqlserver”. Please let me know if you find any problems with it.
For those that use Oracle, be on the lookout for coming support. Also, Fluent NHibernate lovers, be ready for some schema script generation!
02-23-2010 9:43 PM