Invoke-Sqlcmd, InputFile, and Variable

Question

I need to exec a powershell script that exec a SQL script to create a database. Based on my knowledge I exec the powershell command in this way:

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"
}

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

I updated the $sqlVariable as @Chad Miller suggested. So the problem was there. Now I got this error: Creating the database error: Database error: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. The label 'C' has already been declared. Label names must be unique within a query batch or stored procedure..Exception.Exception

Answer 1

The Variable parameter for the Invoke-Sqlcmd cmdlet is picky. Do not include spaces before or after the variable assignment.

$sqlVariable = "DATABASEFILENAME='$DATABASEFILENAME'", "DATABASELOGNAME='$DATABASELOGNAME'", "DBUSEROWNER='$DBUSEROWNER'"

Also you need remove single quotes from variables:

$sqlVariable = "DATABASEFILENAME=$DATABASEFILENAME", "DATABASELOGNAME=$DATABASELOGNAME", "DBUSEROWNER=$DBUSEROWNER"

Answer 2

I know this is old, there is not any real good explanations/examples on this so I am posting this answer.

The error is being generated from the TSQL script having two single quotes around the values for the data file name and log file name after the command variable substitution has taken place. This is due to the variable values containing single quotes and the TSQL script usage of the variables being enclosed in single quotes too. Command variables are not that picky, the parser doesn't know how to handle embedded spaces in the definition. You were correct in placing the single quotes in the string variables, however keep in mind the parser replaces the variable with exactly what was defined in the variable.

Your end result script file names had two single quotes together on either side of the file name. i.e.

"DATABASEFILENAME = '$DATABASEFILENAME'"

placed in the script would result in the following after substitution took place

FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008INSTANCE\MSSQL\DATA\myDB.mdf''

To fix what you posted:

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

Answer 3

First of all, specify the error message if any.

Also, there is no need to define log and data file locations, try to use the snippet below, it works for me in many projects.

$sqlServerName = "%your SQL server instance%" #for example, ".\SQLEXPRESS"
$file = "C:\yourScript.sql"
$database = "testDatabaseName"

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $sqlServerName
Invoke-SqlCmd -inputfile $file -serverinstance $server -database $database