CODEWITHSUNDEEP
sql-serverpowershell
Salvatore Di Fazio
Salvatore Di Fazio

Reputation: 715

Invoke-Sqlcmd, InputFile, and Variable

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

Upvotes: 13

Views: 18799

Answers (3)

Jim
Jim

Reputation: 752

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

Upvotes: 1

Chad Miller
Chad Miller

Reputation: 41777

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"

Upvotes: 13

Alexander Doroshenko
Alexander Doroshenko

Reputation: 1715

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

Upvotes: 0

Related Questions