Post reply

Internal versioning of stored procedures possible?

  • July 4, 2006 at 10:38 pm

    #61414

    Hello,

    I remotely update hundreds of machines running sql server and have found a need for being able to tell if particular stored procedures are updated. For example when 400 machines are updated with a stored procedure fix how do I tell if all the machines got the update when the machines are done a bit here and a bit there (beyond my contol)?

    Is there a method of putting a version number in a stored procedure? If there is I can just check the version number through a query done remotely that runs through the machine list?

    Any suggestion would be welcome. Are there better ways of doing this?

    Thank you!

  • July 5, 2006 at 9:35 am

    #647536

    CREATE PROC [ EDURE ] procedure_name [ ; number ]

        [ { @parameter data_type }

            [ VARYING ] [ = default ] [ OUTPUT ]

        ] [ ,...n ]

    [ WITH

        { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

    [ FOR REPLICATION ]

    AS sql_statement [ ...n ]

    =====> I think you are looking for ;number option

    ;number

    Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

  • July 5, 2006 at 10:07 am

    #647548

    Robert,

    We currently use SQL 2000, Visual Source Safe 6.0 for versioning, along with Visual Studio 2003 to do object versioning on the servers.

    See here for info on VSS keywords:

    http://www.mgbrown.com/PermaLink17.aspx

    Basically I make a database project in VisStudio, and place all the procs into the project, along with the keywords.

    Once I check the file in, VSS will enter the info into the keyword.

    Then I run the proc (from within VisStudio) and this creates it on the server with the comments in it.

    I currently don't need to track changes (as all changes occur thru VisStudio) but if I was worried about direct alterations on the server, I would probably look into a trigger that watches for changes to the procs (via system tables/views?).

    Let me know if you have any questions, I'd be happy to help.

    -Don

  • July 5, 2006 at 9:36 pm

    #647665

    Thank you both for your replies. I will look into both suggestions. Currently the company doesn't use VSS but rather wincvs. I will have to look into the abilities of that software to do what you are suggesting.

    I notice that the sysobjects table has a version number in it but it is for future use. Wouldn't it be nice if every update to a stored proc updated a version number there?

    Thanks again,

    Rob

  • July 6, 2006 at 7:09 am

    #647740

    Robert - I subscribe to another site that had this new tool on their homepage today...sounds like exactly what you (& all of us) need...

    sqldbcontrol

    If you get the chance to check it out (I can't for a couple of weeks), please post feedback...







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply