?
Solved

script to generate sp sp_scriptpublicationcustomprocs

Posted on 2010-11-26
6
Medium Priority
?
1,140 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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