Powershell Scripts to replace "setvar" variable in SQL-CMD script file before running the SQL scripts

Consider having a following SQL file, to run it an SQL-CMD mode:

C:\PowershellTest\CreateDatabase.sql

:setvar ImagesLocation 'C:\ImagesStore\'
........
Update ImagesStore
SET ImagesLocation = '$(ImagesLocation)'
.......

Now, when you run this sql-cmd scripts file during the deployments, you would also want to change the value of ImagesLocation variable, as the location may vary for different environment.

This can be achieved through using regular expressions in Powershell scripts.
In order to do that, you can create a following function in your Powershell deployment or pre-deployment scripts:

#function to replace cmdlet variable in SQL-CMD scripts (i.e., the ones assigned by :setvar). For strange reasons, command line variable assignments has lower precendence than the Sqlcmd scripts setvar. 
function replaceCmdletParameterValueInFile( $file, $key, $value ) {
    $content = Get-Content $file
    if ( $content -match ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]" ) {
        $content -replace ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]", ":setvar $key $value" |
        Set-Content $file     
    } else {
        Add-Content $file "$key = $value"
    }
}

Call this function in a following manner:

$scriptfile = "C:\PowershellTest\UpdateImagesLocation.sql"

replacePatternMatchingValueInFile $scriptfile"SET @ImagesLocation" "'\\datashare\appImages'"
replaceCmdParameterValueInFile "C:\PowershellTest\CreateDatabase.sql" "ImagesLocation" "'\\datashare\ImagesStore'"

As a result, the variable assignment for ImagesLocation would be changed to a different value in the sql file, and then that file can be used in Invoke-Sqlcmd to run it.

CodeProject

Powershell Scripts to replace Key value pair in SQL script file before running the SQL scripts

Consider having a following SQL file:

C:\PowershellTest\UpdateImagesLocation.sql

DECLARE @ImagesLocation NVARCHAR(max)
SET @ImagesLocation = 'C:\ImagesStore\'

.....

Now, when you run this sql scripts file during the deployments, you would also want to change the value of @ImagesLocation, as the location may vary for different environment.

This can be achieved through using regular expressions in Powershell scripts.
In order to do that, you can create a following function in your Powershell deployment or pre-deployment scripts:

#if an SQL file contains 'SET @variable_name=value', then this function can be called to replace value by actual value.

function replacePatternMatchingValueInFile( $file, $key, $value ) {

    $content = Get-Content $file

    if ( $content -match "^$key\s*=" ) {

        $content -replace "^$key\s*=.*", "$key = $value" |

        Set-Content $file     

    } else {

        Add-Content $file "$key = $value"

    }

}

Call this function in a following manner:

$scriptfile = "C:\PowershellTest\UpdateImagesLocation.sql"

replacePatternMatchingValueInFile $scriptfile"SET @ImagesLocation" "'\\datashare\appImages'"

As a result, the variable assignment for @ImagesLocation would be changed to a different value in the sql file.

CodeProject

Development and Deployment - Troubleshooting, Investigations (Series of articles)

At times, we, developers or deployment people do come across some technical problems that eat up our hours (and days sometimes).. and when we find the resolution, or real cause of the issue, it makes us feel that such issues were not worth of the time and efforts we spent.

Here, I am making a collection of such issues/ troubleshooting notes that may help us to take extra care in order to avoid that issues raising while we develop or deploy.

There are each new post for each different issue/ troubleshooting note, and this post is an index that will keep growing as I add new post in this category.

Keep checking this place regularly.

INDEX:

ASP.NET

1. ExecuteNonQuery() always returns -1

2. SelectedIndexChanged not firing up

ASP.NET MVC

1. Values do not retain in model while posting it from view to controller action in MVC