Post reply

Replace a substring using Powershell

  • November 28, 2012 at 6:52 pm

    #265269

    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

    #1564714

    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