Upgrade to Pro — share decks privately, control downloads, hide ads and more …

CLR Stored Procedures

CLR Stored Procedures

CLR Stored Procedures for http://www.sqlserveruniverse.com 2011 JAN Monthly meetup


Harshana Weerasinghe

April 10, 2012


  1. Harshana Weerasinghe http://about.me/harshana

  2.  SQL CLR  CLR Stored Procedures  The Need

    For CLR Stored Procedures  Drawbacks  Configuration  How to use (demo)  Debugging SQL CLR DLLs  External Access  External Access (demo)  Deployment
  3.  Is the technology that hosts the Microsoft .NET Common

    Language Runtime engine within SQL Server.  SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment
  4.  Similar to normal Stored Procedures, but code written using

    Microsoft .NET (C#, VB.NET or any language in .NET)  Works as an MSIL assembly integrated with the SQL Engine.
  5.  Gives better results while executing complex logic:  Intense

    string operations/string manipulation  Cryptography  Accessing system resources  File Management  CLR Stored Procedures are managed code  Ensures type safety  Ensures memory management
  6.  Better code management  Provides object oriented programming 

    Encapsulation  Polymorphism  Inheritance  Can be written using  C#  VB  Any other language that the .NET Framework supports
  7.  Not convenient in all scenarios  E.g. they should

    not be used to execute simple queries.  Deployment may be difficult in some scenarios.
  8.  Enable CLR in SQL Server

  9.  SqlPipe  Debug  Send SQL query as a

    result  Custom Result Sets  Single record  Multiple records (SendResultsStart, SendResultsRow, SendResultsEnd)  Connection String  ADO.NET
  10. None
  11. None
  12. None
  13.  “Cannot load dynamically generated serialization assembly”  Add post

    build event "C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"  More information : http://support.microsoft.com/kb/913668
  14.  Deploy assembly  SP

  15.  What Sgen.exe does ?  The XML Serializer Generator

    creates an XML serialization assembly for types in a specified assembly in order to improve the startup performance of a XmlSerializer when it serializes or deserializes objects of the specified types.
  16.  What is the deference between PERMISSION_SET SAFE, EXTERNAL_ACCESS and

    UNSAFE ?  Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry and EXTERNAL_ACCESS can access.  UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code.
  17.  List down Benefits of using CLR Stored Procedures 

    See the “The Need For CLR Stored Procedures” in presentation.
  18.  http://msdn.microsoft.com/en- us/library/ms131094.aspx - CLR Stored Procedures

  19. None