Synchronize a stored procedure between two servers

  • dachimoto

    SSC Veteran

    Points: 220

    Comments posted to this topic are about the item Synchronize a stored procedure between two servers

  • jswong05

    Hall of Fame

    Points: 3503

    Interesting idea to copy objects. However, according to SOX, those who have full access to production and development are distinct two groups of people. You cannot push DEV object into PROD like so. (sorry being negative, SOX is good or bad, but if you are not small shop). PROD always refresh DEV so be careful what you push.

    I can see the use of any opportunity to push an object, say between two QA platforms, if the script can enhance to do a list of SPs one shot.

    Jason
    http://dbace.us
    😛

  • vasant_jagtap1-579381

    SSC Enthusiast

    Points: 154

    Nice stored procedure to synchronize but its not Synchronize GRANT permission. So can you tell me how to synchronize with GRANT permission.

  • Adam Gojdas

    SSCommitted

    Points: 1766

    Your declared sizes are possibly going to cause truncation:

    @ProcedureName VARCHAR(50) = NULL,

    @SourceServer VARCHAR(50) = NULL,

    @SourceDatabase VARCHAR(50) = NULL,

    @TargetServer VARCHAR(50) = NULL,

    @TargetDatabase VARCHAR(50) = NULL

    maybe declare them as this:

    @ProcedureName sysname = NULL,

    @SourceServer sysname = NULL,

    @SourceDatabase sysname = NULL,

    @TargetServer sysname = NULL,

    @TargetDatabase sysname = NULL

    info from this link: http://technet.microsoft.com/en-us/library/ms191240.aspx

    The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).

  • Phillip St. Luce-275144

    Valued Member

    Points: 62

    Nice post!

    I would suggest adding the ColId column, from the syscomments table to the #tblTmp table, to ensure reading back the rows from that table in the same order as they were in the syscomments table.

    Thanks for sharing your script!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

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