Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2007-01-07 : 07:35:38
|
I have for a while been thinking about a way to embeed a version number in a database, so that an application can act accordingly.I really would like it to be embeeded in the DDL, mostly because I use a tool (RedGate) to syncronize database structure, so it would be conviniet if the version number was 'automatically' updated in this process.The best solution I have come at this far is making a view (could be a sp too, or even an UDF), like:CREATE VIEW [dbo].[vVersion]ASSELECT 20070105 AS Version Of course this method isnt bomb proof, but it is better than nothing, and solves my requirements.Im sorry if this seems to be a trvial question, but I have thought about this for quite a while, and would like to know how you guys handle this problem.-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime. |
|
Kristen
Test
22859 Posts |
Posted - 2007-01-07 : 10:50:30
|
I reckon you should have more than just the "current version of the whole thing"We have a table called something like "ScriptRunLog" and that contains the name of the script, run-date/time and the version number.Each of our "scripts" (initial build, each "patch" script for new version, etc.) inserts a row into this table. That tells use the Current version (SELECT MAX(VersionNumber) WHERE ScriptName = 'MAIN_VERSION') but also when the various scripts were run, AND what order they were run in.Every Sproc script (i.e. a script to create an Sproc) has the SProc name and version number (version number is in the format yyyymmddxxx - where "xxx" is any subdivider - such as indicating a bespoke modification for a specific customer).We have a routine which will delete entries in this table that are, say, > 6 months old AND there is a newer entry for that named-script - so we can purge the stale stuff after a while.We have an Sproc that creates a row in the logging table; for example all Sproc scripts start something like:PRINT 'Create procedure usp_MySproc'GOEXEC dbo.usp_MyLogScriptRun 'usp_MySproc', '070107'GOIF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[usp_MySproc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.usp_MySprocGOCREATE PROCEDURE dbo.usp_MySproc... usp_MyLogScriptRun takes parameters for the Sproc name and an optional "Version" number. Leave the version number off and it displays the log history (TOP 100 ORDER BY CreateDate) - so I can just highlight theEXEC dbo.usp_MyLogScriptRun 'usp_MySproc'bit and execute it to see when it was last run etc. Include the version number and it inserts a new row.Some discipline needed to edit the VersionNumber parameter each time the Sproc script is saved, but that becomes second nature after a bit.We have a Query that will compare two given databases - we use that to compare a newly updated database against a "known-good" master database to see if we forgot anything!Kristen |
|
|
|
|
|
|
|