sql-server - Invoke-Sqlcmd、InputFile 和变量

Question

我需要执行一个 powershell 脚本来执行 SQL 脚本来创建数据库。根据我的知识，我以这种方式执行 powershell 命令:

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脚本

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

我按照 @Chad Miller 的建议更新了 $sqlVariable。所以问题就在那里。现在我收到此错误:创建数据库错误:数据库错误:对象或列名称丢失或为空。对于 SELECT INTO 语句，验证每列都有一个名称。对于其他语句，请查找空别名。不允许将别名定义为“”或[]。将别名更改为有效名称。标签“C”已经被声明。标签名称在查询批处理或存储过程中必须是唯一的..Exception.Exception

Answer 1

Invoke-Sqlcmd cmdlet 的 Variable 参数很挑剔。变量赋值之前或之后请勿包含空格。

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

您还需要从变量中删除单引号:

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