Microsoft provide a very powerful feature to SQL Server 2005 or later to integrate .Net components with SQL Server that is “CLR Integration”.CLR integration means that you can create database objects like stored procedures, triggers, user-defined types ,functions and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.By using CLR integration,You can make complex tasks easier.
For Example: In SQL Server Express edition,there is no database mail functionality to create mail profile and sent mail.To achive this you can use CLR function.A good demostration of this example is given by Greg Robidoux.
In simple word if you are familiar with .Net application development ,then it is very easy to understand concept of CLR Integration.To implement this,You need to create a class library(dll) and register with SQL Server.
By default,CLR integration is disable.So you need to enable it.
- by using this query
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
OR
- By you can set by using Surface Area Configuration Tool
After enabling CLR integration services,You can create and integrate CLR stored procedure,functions,triggers etc.For this, you need to create CLR code in any .Net compatible language.Visual Studio 2008/2010 already provided template for creating CLR database objects.
Steps to Create CLR database objects:
- Go to File =>New Project
- select DataBase=> SQL Server
- Select Visual C# SQL CLR Database Object
- On ok,New window appears and asking for database reference.Select relevant one or add new reference.
- Go to Solution explorer and add new item
- Select that you want to work with
- Add functionality to hellofunction.cs
- Build your project
- Right click solution explorer=> Deploy your project(it may ask for server credentials)
- The below error may occured
Beginning deployment of assembly clrproject.dll to server localhost: TestDb
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error : Could not connect to server localhost TestDb : Login failed for user 'sa'.
- Provide connection string
- Rebuilt and deploy
- If your Project .Net Framework and SQL Server Supporting .Net framework not compatible, the below error occurred
The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.
Deployment script generated to:D:\learning\clrproject\clrproject\bin\Debug\clrproject.sql
Try to deploy by changing Build Framework in Project Proprties.
- If the error not resolved till now then it may be you mark your assembly as UNSAFE/External and database is TRUSTWORTHY.So change permission level to Safe.
- Now Deploy your solution.I hope it will deployed successfully.
- After successful deployment,The CLR database object will appears in Object Explorer of SQL Server Mgmt. Studio.
- Now you can use this function as SQL Server User defined functions.
select dbo.hellofunction()
Output:
- In the same way you can create CLR stored procedure,types,triggers etc.
Advantages of CLR integration:
- As you know,T-SQL does not support arrays, collections, for-each loops, bit shifting, or classes.It is specifically designed for direct data access and manipulation in the database.But if you are using Managed code then these can be supported. CLR allows these constructs.
- CLR has a built in RegEx object.
- you can consume an external Webservice from a SQLCLR method.
- Potential for improved performance and scalability:
Very easy to follow instructions. Thanks
ReplyDeleteIt's not true, SQL server database mail feature is present in SQL server express edition ! But it is hidden in SSMS, you have to use t-sql commands:
ReplyDelete-- database mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Backup',
@description = 'Mail account for Database Mail',
@email_address = 'email@server.com',
@display_name = 'Backup',
@username='notifyadmin@zoznam.sk',
@password='heslo123456',
@mailserver_name = '213.81.185.97'
-- database profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Backup',
@description = 'Profile used for database mail'
-- account to profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Backup',
@account_name = 'Backup',
@sequence_number = 1
-- principal profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Backup',
@principal_name = 'public',
@is_default = 1 ;
/* update records
SELECT *
FROM sysmail_account
GO
EXECUTE msdb.dbo.sysmail_update_account_sp @account_name = 'Backup',
@display_name='Server example'*/
-- send mail:
exec msdb.dbo.sp_send_dbmail @profile_name = 'Backup', @recipients = 'email1@server.com;email2@server.com', @subject = @subject_failed, @body = @mailstring, @body_format = 'text'
Yes we can do this.This is using SQL mail.
DeleteBut I discuss,there is no database mail functionality in Express edition as SQL server enterprise and standered edition.
Any way thanks for sharing.
But I discuss,there is no database mail functionality in Express edition.
ReplyDeletePlease try that solution and then say if it didn't work! I use this on SQL server 2005/2008/2008 R2 Express edition without any problems.
Yes,
DeleteI post in another blog post.
http://www.queryingsql.com/2013/09/sending-mail-using-sql-server-express.html