Synchronize a stored procedure between two servers

Question

Synchronize a stored procedure between two servers

dachimoto

SSC Veteran

Points: 220
More actions
November 30, 2009 at 11:01 am

#228276

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

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

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

jswong05 Hall of Fame Points: 3553 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 23, 2016 at 7:08 am

#1880740

Thanks for the script.