SQL Server .NET integration

constructed by the function to be queried in SELECT statements by embedding them in the FROM clause. CLR code objects can be written in .NET languages which are safe and fast. CLR code can work with data types like XML, varchar binary.

How to configure CLR for SQL Server?

CLR by default is not configured in SQL server. It needs to be explicit ally configured using sp_configure syntax. This is shown as below:

Sp_configure 'clr enabled', 1 What is SAND BOX in SQL server?

Sandbox is a safe place to run semi-trusted programs or scripts that originate at a third party.

SQL Server .NET integration

Overview of SQL Server Integration Services (SSIS)

SQL Server Integration Services is used for building applications with data integration and workflow. SSIS is most commonly used in ETL jobs of data warehousing. It can be used for automation of SQL server’s database maintenance. SSIS has a unique feature to move data from a single data source to a destination with no transformations.

How can we drop an assembly from SQL SERVER?

An assembly can be dropped using DROP ASSEMBLY command.

DROP ASSEMBLY assembly_name

IF no dependent assemblies need to be dropped, “WITH NO DEPENDENTS” needs to be specified.

Why do we need to drop assembly for updating changes?

Assemblies are stored in the database. Because of this, when the assembly is modified and recompiled, it must be first DROPPED from the database and then reloaded using CREATE ASSEMBLY command to reflect changes on the server.

How to see assemblies loaded in SQL SERVER?

Sys.assemblies can be used to see the assemblies loaded in SQL SERVER.

Example:

Select * from sys.assemblies

If the files that were used to create the assemblies need to be viewed, sys.assembly_files can be used.

How is .NET Appdomain allocated in SQL SERVER 2005?

A separate Appdomian is created for each database in order to run the database code. The appdomain may be allocated based on identity of the user who owns the assembly. The memory allocated to the instantiations of .Net type objects is done from a pool of close memory managed by CLR. The .Net framework also has a garbage collector for unusable objects and reclaiming the memory.

What is the syntax for creating a new assembly in SQL Server 2005?

Assemblies can be created using CREATE ASSEMBLY command. It takes the path of the DLL as the parameter.

Example:

CREATE DIRECTORY Sample

FROM ‘\\sampleserver\CodeLib\Mysample.dll’

When the CREATE ASSEMBLY is executed, the DLL is copied to master database.

CLR Integration vs. Extended Stored Procedures

CLR Integration provides a more convenient and robust solution to Extended stored procedures for implementing server side logic. Using Common Language Runtime integration allows the results

RECONFIGURE WITH OVERRIDE

Here, 1 is the configuration setting.

How to create functions in SQL Server using .NET?

Functions in SQL server can be created using the .NET common language interface or CLR. The functions code is written and then complied into a .NET assembly to deploy on the SQL server. This can be achieved either by using a user friendly interface of Visual studio 2005 or compiling the visual studio class library into an assembly.

Syntax:

CREATE FUNCTION MyFunction()
RETURNS INT AS
EXTERNAL NAME
MyAssembly:[MyClass]::MyFunction
Here, EXTERNAL NAME clause is to link the user-defined function name to the appropriate method in the .NET assembly.