Solved

script to generate sp sp_scriptpublicationcustomprocs

Posted on 2010-11-26
6
1,089 Views
Last Modified: 2012-05-10
Hello,

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

Thanks

Regards

bibi
0
Comment
Question by:bibi92
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:adamsjs
ID: 34219767
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
 

Author Comment

by:bibi92
ID: 34219938
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
 
LVL 5

Expert Comment

by:adamsjs
ID: 34220798
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:bibi92
ID: 34221504
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
 
LVL 5

Accepted Solution

by:
adamsjs earned 500 total points
ID: 34222945
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
 

Author Closing Comment

by:bibi92
ID: 34225292
Thanks a lot, it's help me bibi
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question