Sql-server – Powershell Invoke-Sqlcmd Replacing £ with

Making the columns non-nullable and removing the filegroup code worked for me:

# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)  | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’)  | out-null
$server= new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$dbname = "HekatonTest"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $dbname)
# Add FileGroups
$FG1Name = "PRIMARY"
$Normalfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG1Name)
$db.FileGroups.Add($Normalfg)
$FG2Name = "MemOpt"
$MemOptFG = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG2Name)
$MemOptFG.FileGroupType = "MemoryOptimizedDataFileGroup"
$db.FileGroups.Add($MemOptFG)
#Create datafiles
$normallogname = "HekatonTest_Data"
$dbdfnormal = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($Normalfg, $normallogname)
$dbnormalfile= $server.Information.MasterDBPath + '\' + $normallogname + '.mdf'
$normalfg.Files.Add($dbdfnormal)
$dbdfnormal.FileName = $dbnormalfile
$dbdfnormal.Size = [double](5.0 * 1024.0)
$dbdfnormal.GrowthType = 'Percent'
$dbdfnormal.Growth = 25.0
$dbdfnormal.IsPrimaryFile = 'True'
$MemOptFilename = "MemOpt_Data"
$MemOptDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($MemOptFG, $MemOptFilename)
$MemOptDataFilePath= $server.Information.MasterDBPath + '\' + $MemOptFilename + '.ndf'
$MemOptFG.Files.Add($MemOptDataFile)
$MemOptDataFile.FileName = $MemOptDataFilePath
$db.Create() 
#Create Table
$dbname = "HekatonTest"
$db = $server.databases[$dbname]
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "MemOptTable")
#Add Columns
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"Col1", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col2", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$col3 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col3", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))

# DIFFERENCE 1: Columns made non-nullable
# Profiler error event reported the error:
# "The feature 'nullable columns' is not supported with indexes on memory optimized tables."
$col1.Nullable = $false
$col2.Nullable = $false
$col3.Nullable = $false

$tb.Columns.Add($col1)
$tb.Columns.Add($col2)
$tb.Columns.Add($col3)
$C1Name =$col1.Name
#Create Index
$IX = New-Object Microsoft.SqlServer.Management.Smo.Index ($Tb, "PK_PrimaryKeyName")   
$IX_col = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn ($IX,$C1Name)
$IX.IndexedColumns.Add($IX_col)
$IX.IndexKeyType = "DriPrimaryKey"
$IX.IndexType = "HashIndex"
$IX.BucketCount = "1024"

#  DIFFERENCE 2: Filegroups don't really work in the same way with memory-optimized tables; 
# all data goes to the filegroup specified as CONTAINS MEMORY_OPTIMIZED_DATA when the db was created
# when you specify the table is MEMORY_OPTIMIZED = ON
#$IX.FileGroup = "PRIMARY"
#$tb.FileGroup = "PRIMARY"

$IX.IsMemoryOptimized = $true
$Tb.Indexes.Add($IX)
$tb.Durability = "SchemaAndData"
$tb.IsMemoryOptimized = $true
$tb.Create()

NB I got the hint about the nullable columns using the Error event in Profiler (old-school!). I worked out the filegroup thing by inspecting the properties for an existing memory-optimized table.

You could grant the account that is going to run the job the necessary permissions onto the SQL Server to run the query. That would be the easiest. If you are going to use Task Scheduler, it even gives a method for specifying the account to use.

The next best would be to use SQL Authentication.

Finally, you can use PowerShell to invoke a new thread to run under a different windows account, that thread then to use Invoke-sql. However, the password then needs to be stored somewhere somehow (usually encrypted in a file). This is not the best solution and not one that I would recommend using.