November 28, 2012 at 6:52 pm
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.
December 2, 2012 at 4:58 pm
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?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply