Programming Microsoft SQL Server 2005 : Deployment (part 1) - Deploying Your Assembly

1/29/2011 6:38:53 PM
Before you can test your SQL CLR code, you must deploy the assembly containing it and register the individual functions that you want recognized as stored procedures. A number of deployment methods are at your disposal; we will pause to cover them now, before discussing testing of your stored procedures and the other four basic SQL CLR entities.

Deploying Your Assembly

Visual Studio deploys the SQL Server project version of the sample code when you build, start, or step through the project or use the Build/Deploy function on Visual Studio’s main menu. If you’re working with the SQL Server project version of the samples, go ahead and use the Deploy option or one of the Start or Build options in Visual Studio now.

You can execute the following T-SQL command from within Management Studio:

FROM 'C:\ProgrammingSQL2005\Chapter03\VS\Chapter03Manual\Chapter03\bin\Debug\Chapter03.dll'

The AUTHORIZATION clause allows you to specify a name or role to which ownership of the assembly is assigned. The default authorization is that of the current user, and because you are most likely logged in as dbo for AdventureWorks, in this case the clause is unnecessary (which is why we omitted it from our previous example).

The meaning and effect of the WITH PERMISSION_SET clause are discussed at the end of this article. For now, just note that this clause allows you to specify the security permissions with which your assembly runs. As with the AUTHORIZATION clause, in this case the WITH PERMISSION_SET clause is technically unnecessary because SAFE is the default PERMISSION_SET value used when a CREATE ASSEMBLY command is executed.

If your assembly has dependencies on other assemblies, SQL Server looks to see if those assemblies have already been loaded into the database and, if so, confirms that their ownership is the same as that of the specified assembly. If the dependent assemblies have not yet been loaded into the database, SQL Server looks for them in the same folder as the specified assembly. If it finds all dependent assemblies in that location, it loads them and assigns them the same ownership as the primary assembly. If it does not find the dependent assemblies in that folder, the CREATE ASSEMBLY command will fail.

You can supply a string expression instead of a literal in the FROM clause, allowing for some interesting data-driven possibilities. For example, you could fetch an assembly path reference from a table in your database. It is also possible to supply a bitstream in the FROM clause instead of a file specification. You do this by specifying a varbinary literal value or expression (or a comma-delimited list of varbinary values or expressions, when dependent assemblies must be specified) that contains the actual binary content of your assembly (or assemblies). This allows the creation of a database, including any CLR assemblies it contains, to be completely scripted, without requiring distribution of actual assembly files. The binary stream can be embedded in the script itself or, using an expression, it can be fetched from a table in a database.

More Info

See SQL Server Books Online for more information on this option.

In addition to using Visual Studio deployment and the T-SQL CREATE ASSEMBLY command, you can upload the assembly into your database interactively from Management Studio. Simply right-click the servername/AdventureWorks/Programmability/Assemblies node in the Object Explorer (where servername is the name of your server) and choose New Assembly... from the shortcut menu. The New Assembly dialog box, shown in Figure 3-7, appears.

Figure 1. The Management Studio New Assembly dialog box

Type the assembly path and file name in the Path To Assembly text box, or use the Browse... button to specify it interactively. You can specify AUTHORIZATIONWITH PERMISSION_SET details in the Assembly Owner text box (using the ellipsis button, if necessary) and the Permission Set combo box, respectively. and

Regardless of the deployment method you use, once your assembly has been added to your database, it becomes an integral part of that database and its underlying MDF file. This means if your database is backed up and restored, or xcopy deployed, any assemblies within it move along with the data itself and need not be manually added as a subsequent step.

  •  Programming Microsoft SQL Server 2005 : CLR Stored Procedures and Server-Side Data Access
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 3) - Using the Resource Governor
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 2)
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 1)
  •  Reporting Services with SQL Azure : Deploying the Report & Creating a Subreport
  •  Reporting Services with SQL Azure : Creating the Report Design
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 2) - Using RANK, DENSE_RANK and NTILE
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 1) - Using ROW_NUMBER
  •  SQL Server 2008 : Demystifying Data Types - Computed Columns
  •  Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone