Posts Tagged 'CLR'

Debugging Stored Prcedures in Visual Studio

I was speaking with a developer friend of mine the other day and the topic of debugging stored procedures came up. My friend was unaware that this was even possible, so I thought that I would share.

In SQL2000 debugging was as simple as right clicking on a stored procedure in Query Analyzer and selecting Debug from the context menu. This was a simple, yet effective method, but is very limited in its capabilities. That feature was removed in SSMS (SQL Server Management Studio).

For SQL 2005 and above, this can be done in Visual Studio. In server explorer create a data connection. Drill down to the stored procedure that you want to debug and click “Step Into Stored Procedure.” From here you can step though the stored procedure.

Alternatively, you can right click on the stored procedure and select “Open” from the context menu. From there you can set breakpoints, change SQL blocks, execute SQL blocks and step into the stored procedure.

My two favorite features of debugging stored procedures in Visual Studio are the ability to see and change variables as you step through the code and the ability to see the managed code when you open a CLR procedure.

For a step by step explanation (with pictures) you can check out SQL Team‘s article, which explains the steps better than I.

Happy debugging!

~Ron

How to Upload and Download Image/BLOB Data in SQL Server

There was a very interesting post over on SQL Authority on how to upload image data to SQL Server. The question was posed, “How do you then retrieve that data from SQL Server.”

You could build an application and retrieve the data through a GUI, but that would be a rather cumbersome process if you had multiple images to download.

Within SQL Server itself, this requires a multi-step process of created a BCP format file and then using BCP QueryOut to create the actual image files. Fast and efficient, but it could be better.

My preferred method would be to use CLR. Once you have registered the assembly, making sure to mark it for external access, this task can be accomplished in a single step.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void usp_RetrieveBlobData(string query, string outpath)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader();

            using (reader)
            {
                while (reader.Read())
                {
                    FileStream fs = File.Create(outpath);
                    BinaryWriter bw = new BinaryWriter(fs);

                    byte[] file = (byte[])reader.GetSqlBinary(0);

                    bw.Write(file);

                    bw.Close();
                    fs.Close();
                }
            }
        }
    }
};

NOTE: You will also need to enable CLR on the server.

~Ron