September 16, 2009 at 1:57 pm
Is there a nice way to embed a version number in a stored procedure and then retrieve that value to see if you should execute an ALTER against it or not?
We have an app that will update the DB with scripts needed for a specific version of our main application. Currently we drop and recreate updated stored procedures.
The problem is if a customers gets a hot fix for a stored proc from our support team and then runs the db update app that does not have the updated proc, the updated proc gets overwritten.
I would like to be able to see what version of the proc is in the DB and then our app could then identify that the version in the DB is greater then it's own version and not update that stored procedure.
September 16, 2009 at 3:09 pm
I've used extended properties for this purpose; then i can test if the extended property has the desired value before decinding to allow a script to run or not.
[here's an example of creating a proc, adding extended properties, and returning the extended properties back:
CREATE procedure [dbo].[sp_find]
@findcolumn varchar(50)
as
begin
set nocount on
select sysobjects.name as TableFound,syscolumns.name as ColumnFound
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and (syscolumns.name like '%' + @findcolumn +'%'
or sysobjects.name like '%' + @findcolumn +'%' )
order by TableFound,ColumnFound
end
GO
EXEC sys.sp_addextendedproperty
@name = N'Version',
@value = N'9.0.154.90',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'sp_find';
EXEC sys.sp_addextendedproperty
@name = N'Purpose',
@value = N'simple tool to find column or table names that are LIKE the inputed value',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'sp_find';
--show all extended properties
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);
--get just the "Version" that i created:
SELECT objtype, objname, name, value
FROM fn_listextendedproperty ('Version', 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);
then it's simple to test it;
something like:
declare @Version varchar(30)
SELECT @Version=CONVERT(varchar(30),[value] )
FROM fn_listextendedproperty ('Version', 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);
If CONVERT(int,parsename(@Version ,4)) = 9 --major version
and CONVERT(int,parsename(@Version ,3)) = 0 --minor version
and CONVERT(int,parsename(@Version ,2)) < 155 --build version
--and CONVERT(int,parsename(value ,1)) = 90 --revision version do you go that deep?
BEGIN
--drop previous extended property, or update it to the new value
--create/alter the proc
PRINT 'Altering Procedure!'
END
ELSE
BEGIN
--print statement, no changes made?
PRINT 'No Changes Applied, equal or higher version exists!'
END
Lowell
September 16, 2009 at 3:45 pm
We're using a VersionControlTable to verify if a proc or table has been altered / added or deleted.
From my point of view both concepts have their advantage and disadvantage.
With Lowells concept it's possible to check against a specific version number, but it depends on the update of extended properties when a proc will be modified, which cannot be forced as to my knowledge.
The concept we're using will detect ALTER, CREATE and DROP statements. But it will also mark a proc as modified, if someone is using the "Modify" button in SSMS and hit "Execute" without any changes to the proc.
Also, if you're copying a database all objects will be marked as modified. And some more scenarios where this scenario will result in "false alarm". We're not using the table to verify a valid proc during runtime but do a regular check (three times a day) with change alert.
So, as with most cases, the answer is "it depends"...
I wouldn't consider our solution as being "right" but it does what we need: detect changes.
If the request would have been "ensure correct version number" we most probably would have used something similar to Lowells concept.
Here's the basic concept we're using:
DECLARE @VersionControl TABLE (
[name] [sysname] NOT NULL,
[object_id] [int] NOT NULL,
[type] [char](3) NOT NULL,
[create_date] [datetime] NOT NULL,
[modify_date] [datetime] NOT NULL,
[drop_date] [datetime] NULL,
[valid_object_id] [int] NULL,
[valid_create_date] [datetime] NULL,
[valid_modify_date] [datetime] NULL,
[status] AS (case when [create_date]=[valid_create_date] AND [modify_date]=[valid_modify_date] AND [object_id]=[valid_object_id] AND [valid_object_id] IS NOT NULL AND [drop_date] IS NULL then 'valid' when [drop_date] IS NOT NULL then 'deleted' when [valid_object_id] IS NULL then 'new' else 'modified' end)
)
INSERT INTO @VersionControl (
name,
object_id,
type,
create_date,
modify_date)
SELECT
obj.name,
obj.object_id,
obj.type,
obj.create_date,
obj.modify_date
FROM sys.objects AS obj
LEFT OUTER JOIN @VersionControl AS ver
ON ver.name = obj.name
WHERE obj.is_ms_shipped = 0
AND obj.parent_object_id = 0
AND ver.name IS NULL
ORDER BY type
UPDATE@VersionControl
SET
object_id = obj.object_id,
create_date = obj.create_date,
modify_date = obj.modify_date
FROM sys.objects AS obj
INNER JOIN @VersionControl Ver
ON Ver.name = obj.name
UPDATE @VersionControl
SET drop_date=getdate()
FROM sys.objects as obj
RIGHT OUTER JOIN @VersionControl as ver
ON ver.name = obj.name
WHERE obj.name IS NULL
September 16, 2009 at 3:52 pm
You could probably build a database DDL trigger that captures the version and stores it.. Just a thought..
CEWII
September 17, 2009 at 10:19 am
Thank you all for your replies. I do need to track more by version number then by date changed. So I will take a look at Lowell's suggestion.
Thanks again,
Kevin
September 17, 2009 at 10:33 am
yeah with my method, it requires some disipline to create an extended property for all objects subject to versioning; it's a bit of a pain sometimes. if there's no current version, we assume it is subject to replacement;
let us know how you will do yours, it'll be interesting to see how others do it.
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply