|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Sunday, May 05, 2013 7:55 PM
Points: 961,
Visits: 1,530
|
|
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.
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Sunday, May 05, 2013 7:55 PM
Points: 961,
Visits: 1,530
|
|
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?
|
|
|
|