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 into this folder.

We will load this to the SampleDB database. Feel free to use a database accessible to you; just ensure you replace the database name in the script.

How to do it...

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

1. Open the PowerShell console application by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $instanceName = "KERRIGAN"
   $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...