In this recipe, we will create a new user-defined assembly.
Create a folder named C:\CLR Files and copy the QueryWorksCLR.dll file that comes with the book's sample files to this folder.
We will load this to the SampleDB database. Feel free to use a database accessible to you; just ensure that you replace the database name in the script.
These are the steps required to create a new assembly in SQL Server:
Open PowerShell ISE as an administrator.
Import the SQLPS module as follows:
#import SQL Server module Import-Module SQLPS -DisableNameChecking
Add the following script and run it:
$instanceName = "localhost" $databaseName = "SampleDB" $assemblyName = "QueryWorksCLR" $assemblyFile = "C:\CLR Files\QueryWorksCLR.dll" #this is for SAFE assemblies only $query = @" CREATE ASSEMBLY $assemblyName FROM '$assemblyFile' WITH PERMISSION_SET = SAFE "@ Invoke-Sqlcmd -ServerInstance $instanceName -Database $databaseName -Query $query
When you are done, open...