Solved

add NOT FOR REPLICATION in a txt file

Posted on 2016-08-01
6
14 Views
Last Modified: 2016-08-02
Hello,

I search how I can modify a txt file containing :

ALTER TABLE [dbo].[T_tbl]  WITH CHECK ADD  CONSTRAINT [FK_T_tbl_t_test] FOREIGN KEY([proc])
REFERENCES [T_test] ([Id])
GO

Open in new window


by

ALTER TABLE [dbo].[T_tbl]  WITH CHECK ADD  CONSTRAINT [FK_T_tbl_t_test] FOREIGN KEY([proc])
REFERENCES [T_test] ([Id])
NOT FOR REPLICATION
GO

Open in new window


Thanks

Regards
0
Comment
Question by:bibi92
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:oBdA
ID: 41737582
This should do the trick, including keeping the EOL characters currently used:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace "(?s)($([regex]::Escape('REFERENCES [T_test] ([Id])')))(\r?\n)(GO)", '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
 

Author Comment

by:bibi92
ID: 41737621
Ok thanks sorry my question is not clear. I search to add not for replication for any alter table.
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41737636
Even shorter:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)(ALTER TABLE .*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:bibi92
ID: 41738757
Thanks but syntax error is generated for alter table check constraint.
I add option NOT FOR REPLICATION only for ALTER TABLE ... FOREIGN KEY ...
I try :
-replace '(?s)(ALTER TABLE .*? FOREIGN KEY .*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'
But the replace isn't done.
0
 
LVL 83

Accepted Solution

by:
oBdA earned 500 total points
ID: 41738843
That didn't work because you have a space between "KEY" and ".*?", while the script is "KEY([proc])", so there's no match.
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)(ALTER TABLE .*? FOREIGN KEY.*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 41738861
thanks regards
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

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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