Solved

script to generate sp sp_scriptpublicationcustomprocs

Posted on 2010-11-26
6
1,055 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now