PowerShell uses invoke-sqlcmd to concatenate a script file and saves parameters to the script file,

PowerShell uses invoke-sqlcmd to concatenate a script file and saves parameters to the script file,

Original http://stackoverflow.com/questions/16654866/invoke-sqlcmd-inputfile-and-variable

Init. ps1

$DATABASEFILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008INSTANCE\MSSQL\DATA\myDB.mdf"$DATABASELOGNAME = "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008INSTANCE\MSSQL\DATA\myDB_log.ldf"$DBUSEROWNER = "domain\spsetup"

CreateDatabase. ps1

try {        $createDatabaseScript = ($scriptsFolder,$eachRelease,$DeployEnvironment,"Config" -join "\") + "\JM SiteRequest Database.sql"        $sqlVariable = "DATABASEFILENAME=$DATABASEFILENAME", "DATABASELOGNAME=$DATABASELOGNAME", "DBUSEROWNER=$DBUSEROWNER"        Invoke-Sqlcmd -ServerInstance "$MySQLServer" -InputFile "$createDatabaseScript" -ErrorAction Stop -Variable $sqlVariable}catch [Exception] {        Write-Error "Database error: $_.Exception"}

$ SqlVariable is very picky about parameters. No space is allowed before or after a variable.

Note that there cannot be spaces before and after variables in $ sqlVariable

SQL script

CREATE DATABASE [SiteRequestDB] ON  PRIMARY( NAME = N'SiteRequestDB', FILENAME = N'$(DATABASEFILENAME)' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SiteRequestDB_log', FILENAME = N'$(DATABASELOGNAME)' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO

Variable calling method for $ sqlVariable in SQL script $ (variable name)