Lab by sRNcOz

VIEWS: 12 PAGES: 30

									Hands-On Lab
Using CLR Integration in SQL Server 2008

Lab version:    1.0.0
Last updated:   11/10/2011
CONTENTS

OVERVIEW ................................................................................................................................................... 3

EXERCISE 1: CREATING USER-DEFINED FUNCTIONS USING SQL CLR ............................................ 5
       Task 1 - Enabling CLR Integration in SQL Server 2008 .......................................................................... 5
       Task 2 - Creating a SQL Server Project in Visual Studio 2008 ............................................................... 6
       Task 3 - Reviewing the Generated Project .......................................................................................... 10
       Task 4 - Adding a User-Defined Function to the Project..................................................................... 13

EXERCISE 2: DEBUGGING SQL CLR OBJECTS ................................................................................... 17
       Task 1 - Setting up a Debugging Session in Visual Studio ................................................................... 17
       Task 2 - Using Test Scripts to Debug SQL CLR Objects ........................................................................ 19
       Task 3 - Attaching to the SQL Server Process to Debug SQL CLR Objects........................................... 25
       Task 4 - Debugging SQL CLR Code without a Project .......................................................................... 28

SUMMARY .................................................................................................................................................. 30
Overview
SQL Server integration with the .NET Framework common language runtime (CLR) enables database
programmers to implement functions, stored procedures, triggers, data types, and aggregates using
managed code. The CLR provides cross-language integration, code access security, object lifetime
management, and debugging and profiling support.
Transact-SQL is specifically designed for direct data access and manipulation in the database. While
Transact-SQL excels at data access and management, it is not a full-fledged programming language. For
example, Transact-SQL does not support arrays, collections, for-each loops, bit shifting, or classes. While
some of these constructs can be simulated in Transact-SQL, managed code has integrated support for
these constructs.
Database programmers have two alternatives for implementing their database objects. Transact-SQL is
an appropriate choice when code will mostly perform data access with little or no procedural logic.
Managed code is better suited for calculations and complicated execution logic, and features extensive
support for many complex tasks, including string handling and regular expressions.
This hands-on lab introduces you to writing CLR objects using managed code. You will learn how to
create SQL CLR user-defined functions using Visual Studio and how to deploy them to SQL Server. You
will also explore different alternatives for debugging your SQL CLR managed code.


Objectives
In this Hands-On Lab, you will learn how to:

       Enable CLR integration in SQL Server
       Use Visual Studio to implement a SQL CLR user-defined function
       Automatically deploy SQL CLR database objects and source code to SQL Server
       Debug SQL CLR database objects in Visual Studio



System Requirements
You must have the following items to complete this lab:
       Microsoft SQL Server 2008 R2
       Microsoft Visual Studio 2008 Professional or Team editions
Setup
All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is
correctly configured, follow these steps.

 Note: To perform the setup steps you need to run the scripts in a command window with
 administrator privileges.



    1. Launch the Configuration Wizard for this Lab by running the Setup.cmd script located under the
       Setup folder in the Source folder of this lab. Install any pre-requisites that are missing
       (rescanning if necessary) and complete the wizard.

         Note: For convenience, much of the code you will be managing along this lab is available as
         Visual Studio code snippets. The Setup.cmd file launches the Visual Studio installer file that
         installs the code snippets.
         The Configuration Wizard also creates the SQLTrainingKitDB database that you will use along
         the Lab and the 'SQLServerTrainingKitAlias' sever alias used to establish a connection with the
         database server.




Exercises
The following exercises make up this Hands-On Lab:
    2. Creating User-Defined Functions Using SQL CLR
    3. Debugging SQL CLR Objects


 Note: The source code that accompanies this hands-on lab includes an end folder where you can find a
 Visual Studio solution with the code that you would obtain if you complete the steps in each exercise.
 You can use this solution as a guide if you need additional help working through the exercises.



Estimated time to complete this lab: 60 minutes.
Exercise 1: Creating User-Defined
Functions Using SQL CLR
In this exercise, you will create a Visual Studio SQL Server project and implement a user-defined function
using managed code. In addition, you use automatic deployment in Visual Studio to publish the
assembly to SQL Server.
Task 1 - Enabling CLR Integration in SQL Server 2008
The common language runtime (CLR) integration feature is off by default. You must enable it in order to
use objects that are implemented using CLR integration. In this task, you will enable CLR integration
using a stored procedure named sp_configure.
    1. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
       Server 2008 R2.
    2. In the Connect to Server dialog, type SqlServerTrainingKit (the server alias) in the Server name
       combo box




        Figure 1
        Connecting to the SQL Server instance


    3. Press CTRL+N to open a new query window.
    4. Enable SQL Server to execute CLR code. To do this, enter the following Transact-SQL script in
       the query window.
        T-SQL
        sp_configure 'show advanced options', 1;
        GO
        RECONFIGURE;
        GO
        sp_configure 'clr enabled', 1;
        GO
        RECONFIGURE;
        GO



         Note: The 'clr enable' option is an advance option so you first have to configure SQL Server to
         show advanced options with the 'show advanced options' option.



    5. Press CTRL+E to execute the Transact-SQL statements in the query window and enable CLR
       integration.




        Figure 2
        Enabling CLR integration in SQL Server



Task 2 - Creating a SQL Server Project in Visual Studio 2008
In this task, you will create a new SQL Server project in Visual Studio to implement CLR database objects.
You will use this project for the remainder of the lab to explore CLR integration features in SQL Server
2008.
    1. Start Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual Studio 2008.
       Make sure that the account used to start Visual Studio is a member of the sysadmin role in SQL
       Server.
     Note: To debug on SQL Server, both the account running Visual Studio and the account used to
     connect to SQL Server must be members of the sysadmin role. By default, the administrator
     account is a member of this role. To launch Visual Studio as an administrator, right-click the
     program shortcut on the start menu and select Run as administrator.
     For more information, see How to: Create a Database User.



2. Create a new SQL Server project. To do this, in the File menu, select New and then Project. In
   the New Project dialog, expand the Visual C# project type node, select Database from the list of
   categories and choose the SQL Server Project template. (If this template is missing, ensure that
   .NET Framework 3.5 is selected in the drop-down list on the top right of the dialog box).
3. Enter SqlClassLibrary as the name of the new project, set the Location to the Ex1-
   CreatingFunctions folder inside the Source folder of this lab, set the Solution Name to Begin,
   check Create directory for solution, and click OK.




   Figure 3
   Creating a new SQL Server project
4. Configure a reference to the database where automatic deployment will publish the project
   assembly containing the CLR database objects. To do this, select the SQLTrainingKitDB.dbo
   reference and click OK.

     Note: The Add Database Reference dialog enumerates data connections that you have
     defined in Server Explorer. The list will be empty if you currently have no data connections. If
     you have not previously defined a connection to the SQLTrainingKitDB database, you will need
     to do so now. The setup script creates this database during setup. You need to have completed
     the setup procedure before proceeding.




   Figure 4
   Adding a database reference


5. [Optional] If you cannot find any available reference in the Add Database Reference dialog, you
   will need to create the SQLTrainingKitDB reference manually. To do this, follow these steps:
           a. In the Add Database Reference dialog, click Add New Reference.
           b. In the New Database Reference dialog, type the SqlServerTrainingKitAlias server
              alias in the Server name combo box.
           c. Choose the SQLTrainingKitDB database in the Select or enter a database name drop
              down box.
          d. Click Test Connection to ensure that the server name is correct and the database
             exists, then click OK.




   Figure 5
   Creating a reference to the SQLTrainingKitDB database


6. Enable SQL CLR debugging for the connection. This step is required to be able to debug the
   project. When prompted by Visual Studio, click Yes to enable debugging for the
   SQLTrainingKitDB connection.

     Note: The message shown by Visual Studio warns that debugging will stop all managed threads
     on the server. Later on, you will see how this affects other queries executing on the server
     instance.
        Figure 6
        Enabling SQL/CLR debugging for the connection



         Note: The dialog will not appear if you have already configured debugging on this data
         connection previously. You may also configure debugging from Server Explorer if you right-
         click the data connection and enable or disable Allow SQL CLR Debugging in the context menu.




Task 3 - Reviewing the Generated Project
In the previous task, you created a SQL Server project starting from its template. You now examine the
code that Visual Studio generated for you in the SqlClassLibrary project.
    1. Open Solution Explorer to review the files that were included in the solution.

         Note: The project does not contain any source code at this stage. It only includes a Test Scripts
         folder where you place Transact-SQL scripts that you use to test and debug CLR database
         objects implemented in the solution. Currently, it contains a single script file with boilerplate
         Transact-SQL code that has been commented out.




        Figure 7
   The SqlClassLibrary project in Solution Explorer


2. Next, review the database configuration settings for the project. To do this, right-click the
   SqlClassLibrary project node in Solution Explorer and select Properties. In the project
   properties page, click the Database tab to display the database configuration settings.




   Figure 8
   Database settings for the project



     Note: The Database property page only applies to SQL Server projects. It contains a
     connection string for the target database where Visual Studio deploys the SQL CLR assembly.
     You configured this database connection in the first part of this exercise, when you created the
     database reference.
     This property page also allows you to configure a Permission Level for the assembly, which is
     currently set at SAFE, the most restrictive permission set. Code executed by an assembly with
     SAFE permissions cannot access external system resources such as files, the network,
     environment variables, or the registry.
     Additionally, you can assign the name of a user or role as owner of the assembly. If you do not
     specify an Assembly Owner, the deployment process assigns ownership to the current user.



3. Click the Browse button to open the Add Database Reference dialog. Here you can explore the
   available database references and choose another data connection. Click Cancel to close the
   dialog.
   Figure 9
   Add Database Reference dialog


4. Now, click the Deploy tab to review the current deployment options. In this property page,
   setting the Deploy Code option ensures that Visual Studio copies source code files in the project
   to the target database during deployment. This option allows source-level debugging of the CLR
   objects in the project without having the Visual Studio project available. Make sure that you
   leave this option enabled for this exercise.
       Figure 10
       Deployment settings for the project



Task 4 - Adding a User-Defined Function to the Project
Implementing a user-defined function using managed code allows you to take advantage of the richer
programming environment provided by the .NET Framework. In this task, you will create a function that
converts an IP address from an IPV4 string format to its numeric 32-bit equivalent. Database
applications that deal with IP address information often need to perform similar conversions, for
example, to perform lookups against IP location databases or to store this information in a more
compact form.

 Note: The IP function is used to illustrate the use of control loops, string manipulation and
 mathematical computation, which can be difficult to implement using Transact-SQL, but does not
 necessarily represent an optimal implementation.



    1. Add a new user-defined function to the project. To do this, right-click the SqlClassLibrary
       project node in Solution Explorer, point to Add, and then select User-Defined Function. In the
       Add New Item dialog, select the User-Defined Function template, type IPHelpers.cs in the
       Name field, and click OK.

         Note: Many different CLR database items can be created using SQL CLR including stored
         procedures, aggregates, triggers, user-defined types, and regular classes.
   Figure 11
   Adding a new user-defined function to the project




   Figure 12
   Add New Item dialog showing different CLR database objects available in SQL Server projects


2. Open the IPHelpers.cs file in the code editor. The generated class contains an IPHelpers method
   with a default body, which you will replace shortly.
     Note: The UserDefinedFunctions class has been qualified with the partial keyword. Using
     partial classes allows multiple developers to implement different functions in separate source
     code files without interfering with each other.
     Also notice the Microsoft.SqlServer.Server.SqlFunction attribute that decorates the method.
     This attribute indicates that Visual Studio should register the method in SQL Server as a
     function when it deploys the assembly.




   Figure 13
   Template generated code for a user-defined function


3. Delete the IPHelpers method in the UserDefinedFunctions class; it is not required. You will
   replace it with a new function in the next step.
4. Create a new user-defined function to convert IP addresses from IPV4 string format to a 32-bit
   value. To do this, insert the IPAddressToNumber function shown below in the code block, and
   press CTRL+S to save the file.
    (Code Snippet – IPAddressToNumber function - Ex01 Creating Function Using SQL CLR)
   C#
   [Microsoft.SqlServer.Server.SqlFunction]
   public static SqlInt64 IPAddressToNumber(SqlString IPaddress)
   {
       long num = 0;

        if (IPaddress.IsNull || IPaddress.Value == String.Empty)
        {
            return 0;
          }

          string[] arrDec = IPaddress.Value.Split('.');
          for (long i = arrDec.Length - 1; i >= 0; i--)
          {
              num += (Int64.Parse(arrDec[i]) % 256) * (long)Math.Pow(256, (3 - i));
          }

          return num;
   }



       Note: The implementation of this function is based on code available at:
       http://www.justin-cook.com/wp/2006/11/28/convert-an-ip-address-to-ip-number-with-php-
       asp-c-and-vbnet



5. In Solution Explorer, expand the Test Scripts folder and double-click Test.sql to open this file in
   the script editor. The file contains sample Transact-SQL statements to test different kinds of CLR
   objects.

       Note: The content in this file has been commented out because you will need to provide your
       own T-SQL statements to test any objects that you implement in the project.



6. Scroll to the bottom of the file and insert the following Transact-SQL statement to test the
   IPAddressToNumber function passing in an IP address parameter.
   T-SQL
   SELECT [dbo].[IPAddressToNumber]('10.27.4.60')



7. Press CTRL+S to save the script file.
8. Build and deploy the solution to SQL Server. To do this, in the Build menu, select Build Solution
   and then Deploy Solution.

       Note: This step is not strictly necessary if you are going to debug the solution in Visual Studio.
       When you choose Start from the Debug menu, Visual Studio automatically compiles and
       deploys the code to the SQL Server database. In the next exercise, you will debug the assembly
       and execute the user-defined function it contains.
Exercise 2: Debugging SQL CLR Objects
Previously, you created a new SQL CLR User Defined Function (UDF) and deployed it to SQL Server. In
this exercise, you will explore different features provided by Visual Studio to debug SQL CLR objects.


Task 1 - Setting up a Debugging Session in Visual Studio
In this task, you will verify and configure settings that affect your debugging session.
    1. If not opened, start Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual
       Studio 2008 with administrative privileges. To do this, right-click the program shortcut and
       select Run as administrator.
    2. Open the solution that you completed in the previous exercise. Alternatively, you may use the
       provided End.sln solution located under the Ex1-CreatingFunctions\End folder in the Source
       folder of this lab.

          Note: If you use the “End” solution, you will need to configure the database connection string
          before you can proceed. To do this, right-click the project in Solution Explorer, select
          Properties, and click the Database tab to open the database settings property page. In
          Connection String, click Browse to open the Add Database Reference dialog, select the
          SQLTrainingKitDB data connection that you configured during the first exercise and click OK.



    3. In Solution Explorer, right-click Test.sql in the Test Scripts folder and select Set as Default
       Debug Script. You can have multiple test script files in your solution and setting the default
       debug script allows you to choose which one executes when you start your debugging session.




        Figure 14
        Configuring a default debug script in Visual Studio
4. Make sure that the database connection has been set up to allow SQL CLR debugging. To do
   this, open the Server Explorer (if it is not already open) by pressing CTRL+W+L, expand the Data
   Connections node and right-click the connection that points to the SQLTrainingKitDB database
   that you created in the previous exercise. Make sure that the Allow SQL/CLR Debugging option
   is checked.




   Figure 15
   Enabling SQL CLR debugging on the connection


5. Verify that you have correctly deployed the SqlClassLibrary assembly and its source code to the
   server.

     Note: In the previous exercise, the project deployment options specified the Deploy Code
     option.



   To verify that Visual Studio copied the necessary files to SQL Server, select the SQLTrainingKitDB
   data connection node in Server Explorer, expand the Functions node and check that the
   IPAddressToNumber function appears under this node. In addition, expand the Assemblies
   node, locate the SqlClassLibrary assembly and confirm that the source code files for this
   assembly appear under this node. You will use the source code files deployed to SQL Server later
   on.
        Figure 16
        Viewing deployed objects in Server Explorer



Task 2 - Using Test Scripts to Debug SQL CLR Objects
In this task, you will debug a SQL CLR database object by executing a Transact-SQL script file that
performs the actions in the database that are required to start and test the object. You will also examine
the impact that debugging has on other queries running on a SQL Server instance while you are trying to
debug against it.
    1. Open the test script file in the editor. To do this, double-click Test.sql in Solution Explorer.
    2. Set a breakpoint on the first executable (non-commented) Transact-SQL statement in the file.
       To do this, click the area on the left margin of the script editor window, next to the line of script
       where this statement is located or press F9 with the cursor positioned on this line.




        Figure 17
        Setting breakpoints in the test script file
3. In the Debug menu, select Start Debugging to begin execution of the Test.sql script.

     Note: Pressing F5 does not launch the debugger when the input focus is on a Transact-SQL
     script file. You need to switch the focus to Solution Explorer or a C# code file to be able to
     start debugging by using F5.



4. Depending on your current configuration, when debugging starts, Visual Studio might display
   one or more messages prompting for firewall configuration settings required to enable remote
   debugging. If you are debugging against a local instance of SQL Server, you can select the first
   option labeled 'Cancel remote debugging'; otherwise, you will need to enable remote
   debugging through the firewall. Select a suitable option and click OK to start the debugging
   session.




   Figure 18
   Firewall configuration with UAC enabled




   Figure 19
   Configuring the firewall for remote debugging



     Note: The first time that you run the Remote Debugging Monitor, it must configure the
     Windows Firewall on the computer where it is installed. This configuration is necessary to
     enable remote debugging.
5. When you start debugging, the Transact-SQL script begins to execute and hits the breakpoint
   defined earlier.




   Figure 20
   Hitting a breakpoint in the test script


6. Press F11 to single-step so that the next executable statement is the one that calls the
   IPAddressToNumber function.

     Note: You can step through each Transact-SQL statement in the script file.



7. Press F11 again to execute the next statement.

     Note: The Visual Studio debugger switches its debugging context from the script file to the
     source code file that contains managed code for the IPAddressToNumber function, allowing
     you to debug SQL CLR objects directly in Visual Studio.
   Figure 21
   Single-stepping through a SQL CLR user-defined function


8. Press F11 a few more times to step through part of the code in the function and then press F5
   to resume execution and exit the function.
9. Press CTRL+W+O to open the Output window and see the output generated by the call to the
   SQL CLR function. The SELECT statement in the Transact-SQL test script returns a single scalar
   value denoted in the output window as Column1.
   Figure 22
   Viewing the output from the SQL CLR function


10. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
    Server 2008 R2 and connect to a Database Engine using the SqlServerTrainingKitAlias server
    alias.




   Figure 23
   Connecting to the SQL Server instance


11. Press CTRL+N to open a new query window.
12. In the query window, enter the following Transact-SQL statement:
   T-SQL
   DECLARE @point Geography = Geography::Point(45, 200, 4326)



     Note: The statement declares and initializes a variable that uses the Geography spatial data
     type. SQL Server implements the Geography type as a managed type. When you execute the
     following statement, it will run managed code on the SQL Server instance that you are
     debugging.



13. Press CTRL+E to execute the Transact-SQL statement.

     Note: The query completes immediately. The result is not significant for the purposes of this
     lab; you are using this statement solely for executing managed code.



14. Switch back to Visual Studio.
15. Make sure that the breakpoint in the T-SQL test script is still set on the first executable
    statement in this file and that no breakpoints exist in the IPAddressToNumber function.
16. In the Debug menu, select Start Debugging to begin execution of the Test.sql script again. Wait
    for execution to stop at the breakpoint.
17. Press F11 to step into the IPAddressToNumber managed code function. Note that you are now
    debugging managed code and therefore all managed threads in the SQL Server instance are
    suspended.
18. Return to SQL Server Management Studio and press CTRL+E to execute the query once again.
    Notice that this time the query does not complete immediately and that the status bar indicates
    that it is still in progress.

     Note: If execution stops at a breakpoint while debugging in Visual Studio, all managed threads
     in the SQL Server instance that you are debugging are suspended and queries that use
     managed code cannot proceed. You must ensure that no one else is using the SQL Server
     instance while you are trying to debug against it.



19. In Visual Studio, press F5 to resume execution of the function and release all managed code
    threads.
20. Return to SQL Server Management Studio and notice that the query is now able to complete.
        Figure 24
        Queries are blocked while managed code is being debugged



Task 3 - Attaching to the SQL Server Process to Debug SQL CLR Objects
Previously, you started a debugging session by launching a test script in Visual Studio that created and
used the SQL CLR database objects in your project. In certain situations, you may need to debug SQL CLR
objects created or triggered by other applications accessing the database. In this task, you will start a
debugging session by attaching the Visual Studio debugger to the SQL Server process and then set
breakpoints in the source code in your project. When the applications using the database access the
corresponding SQL CLR objects, execution stops at the breakpoints that you have defined and allows you
to debug the code.
    1. Make sure that you are running Visual Studio as an administrator. This is required to attach to
       the SQL Server process.
   Figure 25
   Running Visual Studio with administrative privileges


2. In the Debug menu, select Attach to Process.
3. In the Attach to Process dialog, make sure that Show processes from all users is selected. If you
   are running through a remote desktop connection, also make sure that Show processes in all
   sessions is selected.
4. In the list of available processes, select the SQL Server process (sqlservr.exe). Verify that the
   Attach to list indicates automatic detection of the type of code to debug (denoted by the label
   Automatic) and that the Managed code option is included in the list. If this is not the case,
   ensure that you have selected the correct instance of the SQL Server process, click Select to
   open the Select Code Type dialog, check that Automatically determine the type of code to
   debug is selected, and click OK to close the dialog. Back in the Attach to Process dialog, click
   Attach to debug the SQL Server process in Visual Studio.

     Note: If you are running multiple instances of SQL Server, the available processes list will list
     each one. If you are unsure about which instance to choose, open SQL Server Configuration
     Manager and select the SQL Server Services node to display all running services. Identify the
     correct SQL Server service instance by examining the instance name displayed in parenthesis,
     next to the service type. Make a note of the Process ID for this instance. Back in Visual Studio,
     locate the correct instance by matching the Process ID obtained previously with the value
     displayed in the ID column of the Attach to Process dialog.
   Figure 26
   Attaching to the SQL Server process for debugging



    Note: To attach to the SQL Server process, you require administrative privileges.



5. Depending on your SQL Server service account configuration, Visual Studio might prompt you
   with a security warning. Click Attach to proceed.




   Figure 27
   Security warning when attaching to untrusted processes
    6. In Solution Explorer, double-click IPHelpers.cs to open this file in the code editor.
    7. Place a breakpoint on the IPAddressToNumber function. To do this, click the area on the left
       margin of the code editor window, next to the line that contains the opening brace of this
       method or press F9 with the cursor positioned on this line.
    8. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
       Server 2008 R2 and connect to the SQL Server instance that is hosting the CLR managed code
       objects.
    9. Press CTRL+N to open a new query window.
    10. In the query window, enter the following Transact-SQL statements that call the
        IPAddressToNumber function. Press CTRL + E to execute these statements.
        T-SQL
        USE SQLTrainingKitDB
        GO

        SELECT [dbo].[IPAddressToNumber]('10.98.11.40')
        GO



         Note: When you execute the query in SQL Server Management Studio, the focus switches back
         to Visual Studio and execution stops at the breakpoint that you set previously.



    11. Press F11 to single-step through the lines in the function or F5 to resume execution.



Task 4 - Debugging SQL CLR Code without a Project
In this task, you will debug the SQL CLR function that you created in the previous exercise without
accessing the Visual Studio solution that you used to create it.
    1. In Visual Studio, close the current solution. To do this, in the File menu, click Close Project.
    2. In Server Explorer, expand the Data Connections node and select the SQLTrainingKitDB data
       connection node. You should already have configured this data connection to allow SQL CLR
       debugging in a previous task.
    3. In the SQLTrainingKitDB data connection node, expand the Functions node, right-click the
       IPAddressToNumber function and select Step Into Function to start debugging this function.
   Figure 28
   Debugging a function from Server Explorer


4. Depending on your current configuration, Visual Studio might display one or more messages
   prompting for firewall configuration settings required to enable remote debugging. If you are
   working against a local instance of SQL Server, you can select the first option labeled 'Cancel
   remote debugging'; otherwise, you will need to enable remote debugging through the firewall.
   Click OK to start the debugging session.
5. Enter the parameters for the IPAddressToNumber function. In the Run Function dialog, type a
   valid IP address in the Value field and click OK. Notice that the IPHelpers.cs source code file
   opens in the debugger.
        Figure 29
        Entering parameter data to execute the IPAddressToNumber function in the debugger



         Note: The debugger retrieves the source code file from SQL Server, where it was stored when
         the project was originally deployed. In task 3 of the previous exercise, you saw how the project
         deployment settings included the Deploy Code option. This ensured that Visual Studio copied
         the source code files in the project to SQL Server during deployment.



    6. Press F11 to step through the code in the IPAddressToNumber method. Notice that the
       debugging experience is unchanged even though the original project is not available.
    7. Press F5 to continue execution and exit the function.



Summary
In this hands-on lab, you have examined CLR integration and seen how to enable this feature in SQL
Server 2008. With the CLR hosted in SQL Server, you can author stored procedures, triggers, user-
defined functions, user-defined types, and user-defined aggregates in managed code. During the lab,
you used Visual Studio to create a SQL Server project and implement a user-defined function using
managed code. You have seen how Visual Studio automatically deploys your managed code assemblies
to SQL Server. Finally, you explored the support in Visual Studio for debugging SQL CLR database objects.

								
To top