February 19, 2009 at 11:30 am
I've been unable to get a stored procedure to work unless it is in the DBO schema. Does anyone know if this is a known bug / undocumented feature?
Is there anyway to let Microsoft know about bugs other than paying them to tell them about them?
This is a very powerful tool for substituting custom handling of replication. We use it to replace the default INSERT procedures with ones that include TRY...CATCH so that if the INSERT fails due to a duplicate key value, we log the occurrence and then try to UPDATE the existing record with the values from the failed INSERT. By creating a single procedure that is registered and generates this custom code for each article, we've been able to extend the usefulness of replication to new areas.
Have Fun!
Ron
February 19, 2009 at 11:36 am
How are you calling the stored procedures?
February 19, 2009 at 11:50 am
February 20, 2009 at 7:59 am
"How are you calling..."
Not sure if this is of any use, but...
To register the custom proc, this snippet is part of a command script (hence the variable syntax) we use to setup replication. The %MySQL% is set to either ISQL or SQLCMD based on version of SQL:
REM 8b Register custom insert stored procedure
REM Create the query in the output Log file to be used as input by the subsequent step
%MySQL% -S%MyServerName% -d%MyDatabase% -U%MyUserID% -P%MyPassword% %MyOptionForISQL% -h-1 -w256 -Q "SET NOCOUNT ON; SELECT 'SET NOCOUNT ON; EXEC [' + tms.udf_GetStringParameter ('cad', 'active_server_name') + '].' + tms.udf_GetStringParameter ('cad', 'active_db_name') + '.dbo.SP_REGISTER_CUSTOM_SCRIPTING @publication = N''%MyActivePublicationName%'', @type = N''insert'', @value = N''csp_tms_replication_insert'''" -o..\log\replication_8b_register_custom_sp.sql
REM Use the output of the previous step as input in order to run the custom SP registry script.
%MySQL% -S%MyServerName% -dMaster -U%MyUserID% -P%MyPassword% -w256 -i..\log\replication_8b_register_custom_sp.sql -o..\log\replication_8b_register_custom_sp.log
February 20, 2009 at 8:15 am
Post snapshot scripts are not an option due to the fact we don't have snapshot initialization.
We were manually running the procedure to create the custom procs after deploying replication, but were having issues with determining how long to wait until SQL finished doing the default proc creation so we could be sure our versions alway overwrote the default ones. Plus of course anytime a schema change occurs, we have to run the creation process again for each article affected.
The registered method addresses both these problems, and so far is no more fragile than the human processes it replaces. Any specific things I should know about in regards to being fragile?
February 20, 2009 at 8:47 am
First of all not having snapshot initialization makes you lose some "features" that help ( a lot ) when you want to incrementally maintain replication, as you are already doing.
Don't get me wrong I do understand that not having initialization is a "must" in some cases (Been there done that)
Second I believe that sp_register_custom_scripting was meant to be used with initialization so that AFTER you call addsubscription on the article you just run snapshot to populate the subscriber ( correct me if I am wrong)
Third The "Fragile" part:
There are bugs reported: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321582
The code of "dynamic" SQL to build the text is cumbersome
The lack of information about what to do when you don't want to replicate all columns is problematic.
The complete absence of documented examples/behavior is disturbing
I could go on but hopefully you will pick up my animosity with it.
I wanted to implement data change audit (on a subscriber DB) using this feature at one point in time and was hit by many of these problems.
SQL 2008 CDC has solved my problems on that respect but that's another thread 😉
* Noel
July 7, 2010 at 2:55 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply