Creating a new assembly

In this recipe, we will create a new user-defined assembly.

Getting ready

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.

How to do it...

These are the steps required to create a new assembly in SQL Server:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. 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...