0

I am trying to run a SQL script from PowerShell (which is on my Windows 10 64-bit desktop) and the database hosted on my computer (SQL Server 2019 DEV).

The script itself should create a database, and my intention is that, when I want to run this, I just modify a variable.

$APPNAME="TEST_DB"
$INSTANCE="TEST_INSTANCE\TESTSQL"
$DATABASECREATE ='CREATE DATABASE [$APPNAME]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N"$APPNAME", FILENAME = N"\\mssql\DATA\$APPNAME.mdf" , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N"$APPNAME_log", FILENAME = N"\\mssql\LOG\$APPNAME_log.ldf" , SIZE = 32768KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [$APPNAME] SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE [$APPNAME] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [$APPNAME] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [$APPNAME] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [$APPNAME] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [$APPNAME] SET ARITHABORT OFF 
GO
ALTER DATABASE [$APPNAME] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [$APPNAME] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [$APPNAME] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [$APPNAME] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [$APPNAME] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [$APPNAME] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [$APPNAME] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [$APPNAME] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [$APPNAME] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [$APPNAME] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [$APPNAME] SET  DISABLE_BROKER 
GO
ALTER DATABASE [$APPNAME] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [$APPNAME] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [$APPNAME] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [$APPNAME] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [$APPNAME] SET  READ_WRITE 
GO
ALTER DATABASE [$APPNAME] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [$APPNAME] SET  MULTI_USER 
GO
ALTER DATABASE [$APPNAME] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [$APPNAME] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [$APPNAME] SET DELAYED_DURABILITY = DISABLED 
GO
USE [$APPNAME]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N"PRIMARY") ALTER DATABASE [$APPNAME] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO'

Invoke-Sqlcmd -ServerInstance "$INSTANCE" -Query $DATABASECREATE

The error message:

Invoke-Sqlcmd : Incorrect syntax near 'PRIMARY'.
At line:68 char:1
+ Invoke-Sqlcmd -ServerInstance $INSTANCE -Query $DATABASECREATE

I'm pretty new to this as you can see, but other than this, I tried to write a function for it, with no luck. When I search for this specific issue I see so many complex queries, but I just want to execute a SQL query, with 1 variable in PowerShell.

If you can help me, or push me in the right direction I would appreciate it, thank you!

mklement0
  • 382,024
  • 64
  • 607
  • 775
avoid1t
  • 3
  • 2
  • Aside... string literals in Microsoft SQL Server use single quotes, e.g.: `'a varchar literal'` and `N'an nvarchar (National Characters) literal'` – AlwaysLearning Apr 29 '22 at 11:32
  • 2
    Enclose the PowerShell string literal in double-quotes (e.g. `$DATABASECREATE = "CREATE DATABASE..."`) and use single-quotes in the T-SQL script as @AlwaysLearning suggested. – Dan Guzman Apr 29 '22 at 11:38
  • 1
    Switching to Double Quotes (`"`) will also fix your attempt to inject the variable; variable replace is only performed automatically in Powershell when using double quotes, not single quotes (`'`). Notice the different behaviour in this simple [example](https://i.stack.imgur.com/2WXW1.png). – Thom A Apr 29 '22 at 11:52
  • Thank you guys, I switched the " and ' as described above. Now I'm getting a different error: Invoke-Sqlcmd : 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 de fined as "" or [] are not allowed. Change the alias to a valid name. – avoid1t Apr 29 '22 at 12:42
  • In short: Only `"..."` strings (double-quoted, called _expandable strings_) perform string interpolation (expansion of variable values) in PowerShell, not `'...'` strings (single-quoted, called _verbatim strings_). If the string value itself contains `"` chars., escape them as `\`"` or `""`, or use a double-quoted _here-string_. See [about_Quoting_Rules](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules) – mklement0 Apr 29 '22 at 14:23
  • As @AlwaysLearning points out, only `'...'` quoting is supported for string literals by default, though `"..."` support can be turned on by setting [`SET QUOTED_IDENTIFIER`](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver15) to `OFF` (which has ramifications). – mklement0 Apr 29 '22 at 14:25
  • @avoid1t, as for your follow-up question: that sounds like an unrelated problem with your specific query, so I suggest asking a _new_ question focused just on that. – mklement0 Apr 29 '22 at 14:26

0 Answers0