Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1162
  • Last Modified:

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
0
bibi92
Asked:
bibi92
  • 3
  • 3
1 Solution
 
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
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
adamsjsCommented:
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
 
bibi92Author Commented:
Thanks a lot, it's help me bibi
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now