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
Tweet

More Decks by Harshana Weerasinghe

Other Decks in Programming

Transcript

  1.  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
  2.  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
  3.  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.
  4.  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
  5.  Better code management  Provides object oriented programming 

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

    not be used to execute simple queries.  Deployment may be difficult in some scenarios.
  7.  SqlPipe  Debug  Send SQL query as a

    result  Custom Result Sets  Single record  Multiple records (SendResultsStart, SendResultsRow, SendResultsEnd)  Connection String  ADO.NET
  8.  “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
  9.  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.
  10.  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.
  11.  List down Benefits of using CLR Stored Procedures 

    See the “The Need For CLR Stored Procedures” in presentation.