script to generate sp sp_scriptpublicationcustomprocs

Hello,

Is it possible to do a script for generate transactional replication procedures with sp_scriptpublicationcustomprocs  for modify them ?

Thanks

Regards

bibi
bibi92Asked:
Who is Participating?
 
adamsjsConnect With a Mentor Commented:
I'm still not sure that I fully understand what you're wanting to do.  But, I'll give some thoughts and you can clarify where I'm not understanding.

If column ROWID is a column in O1.TEST, you won't be able to use its value to set the value of ZROWID_0 as part of the insert.  Since you have not included ROWID in the column list for the insert, I'm going to guess that it is an identity column (perhaps the primary key for the O1.TEST table?).  In that case, you could create a FOR INSERT trigger on the table which would then update the inserted row to set ZROWID_0 to same value as ROWID.

As for "add the condition AND ZORIDOS_0 = ‘BI2’" ... You can't base the insert conditionally on a column value since you are inserting the record, not updating an existing record.  But, if the value you are setting ZORIDOS_0 to is being passed in a variable/parameter, you could do it.
INSERT INTO 
	[O1].[TEST] (
		  [CCE_7]
		, [CCE_8]
		, [AMTCUR_0]
		, [AMTLOC_0]
		, [AMTRPT_0]
		, [QTY_0]
		, [UOM_0]
		, [ACCNUM_0]
		, [ACCDAT_0]
		, [ZORIREP_0]
--		, [ZROWID_0]	-- commented out, update with ROWID using trigger
		, [ZORIDOS_0]
	)
VALUES (
	  @c1
	, @c2
	, @c3
	, @c4
	, @c5
	, @c6
	, @c7
	, @c8
	, @c9
	, @c10
	, @c11	--- @c11 is variable or parameter with value for ZORIDOS_0
)
WHERE
	@c11 = 'BI2'

Open in new window

I think your delete statement is pretty much good to go.  You just need to add in the condition that you had commented out, giving you this:
DELETE 
	[O1].[TEST]
WHERE 
	ZROWID_0 = @pkc1 
	AND ROWID = @pkc1
	AND ZORIDOS_0 = 'BI2'

Open in new window

Let me know where I have missed what you're wanting to do.
0
 
adamsjsCommented:
I believe I know what you are asking.  If not, please clarify where I misinterpreted.

The sys.sp_scriptpublicationcustomprocs procedure will generate the script for custom insert, update, and delete statements for all tables involved in the publication you specify when the procedure is called.  You could then modify the script it outputs if you have need.  Details can be found here:  http://msdn.microsoft.com/en-us/library/ms187946.aspx.
0
 
bibi92Author Commented:
Hello, thanks, I have to modify the procedures like this :

insert into [O1].[TEST](
 [CCE_7]
,[CCE_8]
,[AMTCUR_0]
,[AMTLOC_0]
,[AMTRPT_0]
,[QTY_0]
,[UOM_0]
,[ACCNUM_0]
,[ACCDAT_0]
,[ZORIREP_0]
,[ZROWID_0]      ¿--------- Action : modify the column ROWID by ZROWID_0
, [ZORIDOS_0]   ¿------- Action : add a column [ZORIDOS_0]
 )
values (
 @c1
,@c2
,@c3
,@c4
,@c5
,@c6
,@c7
,@c8
,@c9
,@c10
,’BFIN00A’ ¿----- Ajout nom du dossier en correspondance avec ZORIDOS_0
 )

/* How can I replace dynamically in this insert statement ROWID by ZROWID_0 and add the condition AND ZORIDOS_0 = ‘BI2’




begin
delete [O1].[TEST]
where ZROWID_0 = @pkc1 AND ZORIDOS_0 = ‘BI2’
/* where [ROWID] = @pkc1 */

/* How can I replace dynamically in this delete statement ROWID by ZROWID_0 and add the condition AND ZORIDOS_0 = ‘BI2’

Thanks

Regards

bibi
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
adamsjsCommented:
I'm not clear on what you are trying to do.  Let me ask some questions so I can get the details:

You want the value of ZROWID_0 to be set to the value of ROWID, correct?

Is ROWID a column in another table?  If so, what is the table name?  Can that table be joined to table O1.TEST, and if so, on what column(s)?  

If ROWID is not a column in another table, is it a column in table O1.TEST?

Do you want the value of ZORIDOS_0 to always be set to 'BI2', or is its value dynamic as well?

If the value for ZORDIS_0 is dynamic, where does the value originate?  Is it from another table?  If so, what table and column, and how is that table joined or related to O1.TEST?
0
 
bibi92Author Commented:
You want the value of ZROWID_0 to be set to the value of ROWID, correct?
Yes
If the value for ZORDIS_0 is dynamic, where does the value originate?  Is it from another table?  If so, what table and column, and how is that table joined or related to O1.TEST?
ZORDIS_0 i a new column

Thanks

bibi
0
 
bibi92Author Commented:
Thanks a lot, it's help me bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.