Invoke-Sqlcmd、InputFile 和 Variable [英] Invoke-Sqlcmd, InputFile, and Variable

问题描述

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

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

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

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

推荐答案

Invoke-Sqlcmd cmdlet 的 Variable 参数很挑剔.不要在变量赋值前后包含空格.

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"

这篇关于Invoke-Sqlcmd、InputFile 和 Variable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！