Once a routine is written, tested, and, if necessary, debugged, it can finally be rolled out to production. The process of doing this is quite simple: the release version of the DLL is copied to the server, and a few T-SQL statements are executed.
In order to produce a release version, change the build option on the Standard toolbar from Debug to Release, as shown in Figure. Once the configuration is set, click Build from the main toolbar, and then click Build Solution. This will produce a release version of the DLL—a version with no debug symbols—in the [Project Root]\bin\Release folder. So if the root folder for the project is C:\Projects\SalesCrossTabs, the DLL will be in C:\Projects\SalesCrossTabs\bin\Release.
Configuring the project for a release build
The release version of the DLL can be copied from this location onto any production server in order to deploy it. Only the DLL is required to deploy the CLR routines compiled within it. The DLL is registered with SQL Server using the CREATE ASSEMBLY statement, which has the following syntax:CREATE ASSEMBLY assembly_name
The assembly_name represents a user-defined name for the assembly. Generally, it’s best to use the name of the project. The AUTHORIZATION clause is optional and allows the DBA to specify a particular owner for the object. The important part of the FROM clause is the client_assembly_specifier, which is the physical path to the DLL file. The assembly_bits option is used for situations in which the DLL has been binary-serialized.
The most important clause of CREATE ASSEMBLY, however, is the optional WITH PERMISSION_SET
clause. The DBA is in complete control when it comes to what CLR routines can do. Routines can be assigned to one of three permission sets, each progressively less restrictive.
By controlling CLR routine permission, the DBA can keep a close watch on what routines are doing and make sure that none are violating system policies.
Assuming that the SalesCrossTabs DLL was copied to the C:\Assemblies folder on the SQL Server, it could be registered using the following T-SQL:CREATE ASSEMBLY SalesCrossTabs
Since this assembly doesn’t use any external resources, the default permission set doesn’t need to be overridden. Keep in mind that if the assembly had already been deployed using Visual Studio, this T-SQL would fail; assembly names must be unique within a database. If there is already an assembly called SalesCrossTabs from a Visual Studio deployment, it can be dropped using the DROP ASSEMBLY statement.
Once CREATE ASSEMBLY has successfully registered the assembly, the physical file is no longer accessed. The assembly is now part of the database in which it’s registered.
The next step is to tell SQL Server how to use the procedures, functions, and types in the assembly. This is done using slightly modified versions of the CREATE statements for each of these objects. To register the GetSalesPerTerritoryByMonth stored procedure, the following T-SQL would be used:CREATE PROCEDURE GetSalesPerTerritoryByMonth
The parameter list must match the parameters defined on the CLR method. The EXTERNAL NAME clause requires three parameters, delimited by periods: the user-defined name of the assembly, the name of the class defined in the assembly (in this case, the default StoredProcedures class), and finally the name of the method defined as the stored procedure in the assembly. This clause is case sensitive, so be careful. Changing the case from that defined in the routine will result in an error.
Once the stored procedure is defined in this way, it can be called just like any native T-SQL stored procedure.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.