Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replace a substring using Powershell Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 6:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
Hi,

I've got a job, one step of which adds some articles into a publication:

$DBScriptFile = "C:\Scripts\RO_replication.sql" 
#SCRIPT
Invoke-Sqlcmd -ServerInstance localhost -database master -InputFile $DBScriptFile

RO_replication.sql looks this way:

USE MyDB{CountryCode}
GO
PRINT 'Adding articles to MyDB{CountryCode}_RO'
GO
exec sp_addarticle @publication = MyDB{CountryCode}_RO', @article = N'AdChannel', ...
GO
...

I need to change the Powershell code above, so it replaces {CountryCode} with the result of this query:

Select CountryCode From [DBUtilities].[dbo].[EnvBuild] 
Where Id = (select MAX(Id) from [DBUtilities].[dbo].[EnvBuild])

The original TSQL script should stay generic (keeping {CountryCode}) so the changes made by replacement should not be saved.

Any ideas?

Thanks.



Post #1390227
Posted Sunday, December 2, 2012 4:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
This is where I got so far:

$DBScriptFile = "C:\Scripts\test.sql"
$replacement = invoke-sqlcmd -query "Select CountryCode From [DBUtilities].[dbo].[EnvBuild] Where Id = (select MAX(Id) from [DBUtilities].[dbo].[EnvBuild])"
$Query = $DBScriptFile -replace "{CountryCode}",[string]$replacement[0]
Invoke -Sqlcmd -ServerInstance localhost -database master -InputFile $Query

The last command does not work, because $Query holds the path to the file:
C:\Scripts\test.sql

I need it to hold the actual text of the script instead. C:\Scripts\test.sql still has {CountryCode} in it. I could output the result of the replacement back into the file, but I don't want to save the file. I don't want to make numerous copies of the file either. I just want to be able to put the script into a variable and run it.

Any ideas?



Post #1391752
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse