Debugging CLR Stored Procedures in Visual Studio 2010

One of the problems I encountered recently was a CLR stored procedure that I had created which accepted accepted a file and wrote this file locally, while putting a row in a database to reference its location.

The problem came around when I tried it out and it through an UnauthroizedAccessException. So I though straight away, fine I guess I haven't given the directory permissions for the SQL Service user to write. So I added this and tried again but I got the same error! Intrestingly if I had been sensible and looked up the MSDN documentation for File.WriteAllBytes(), which I was using to write the transfered file to disk, I would have seen that UnauthroizedAccessException is actually thrown for more reasons than just security permissions, in fact it says I can occur for:

 path specified a file that is read-only.

-or-

This operation is not supported on the current platform.

-or-

path specified a directory.

-or-

The caller does not have the required permission.

So if I had heeded this information,. I would have found that my bug was caused by quite a noob mistake where I was trying to write to a directory and not a file.

Either way though I didn't, and I wanted to understand how to debug CLR procedures incase a real dire problem came up in a more complex procedure. So this is how I achieved it:

1) Re-create your CLR stored procedures in SQL Server using a debug version of your assembly. Make sure you have the symbol file for it. You'll need it to be able to catch break points and debug the assembly. In my set-up both assembly and debug symbol file were both in the same folder.

2) Add this folder to your symbol directory list in visual studio. It may be that visual studio will be able to find the symbols anyway, but I guess it doesn't harm to give VS a bit of a hint as to where to look. This can be done by going to Tools > Options > Debugging > Symbols and adding the new location in the 'Symbol file (.pdb) locations' box.

Adding a Path to the Assembly Symbol Files.

3) Ensure the setting for debugging 'Just My Code' is turned off. This can be verified by going to Tools > Options > Debugging > General and making sure that the 'Enable Just My Code' check box is unchecked.

Changing Debug Settings

4) Ensure that the assembly that contains your stored procedures is the active solution in Visual Studio. At this stage we can attach Visual Studio to the SQLServer process which is using our CLR stored procedure assembly. To do this, go to Debug > Attach To Process. This will open up a window showing all the processes we can attach to. Check both 'Show processes from all users' and 'Show processes in all sessions'. In this list above, you should be able to find a process called something like sqlservr.exe. If the process has loaded your assembly you wish to debug, the type should include 'Managed'.

Attaching to the SQL Process

On attaching to this process, checking the output should show that its found and loaded the symbols successfully for your assembly containing the stored procedures. If everything is ok, the breakpoints in the stored procedure assembly should become active too (go from outlines to filled)! Click 'Attach'.

To check if the correct assembly has been loaded after attaching the the right symbols, you can view the loaded modules in visual studio. To do this click Debug > Windows > Modules.

Modules View

If your symbols haven't been loaded, you can always try and load them manually by right clicking and selecting load symbols. If this still doesn't work, ensure that your symbol path has a root to your stored procedure assembly symbol file.

If it isn't exactly the same .pdb for the source you have open in visual studio, the break point may not still be filed and it won't catch, but the differences may be small enough for you to understand the problem, so you can resolve this by right clicking on the break point, selecting 'Location' and checking 'Allow the source to be different...'.

5) Run the application past the point where it uses the stored procedure and the break point should catch! And your free too debug!

This indeed was how I found my noob mistake! Good experience though! :)

There was one problem I encountered upon the way though, and that was that I kept getting the error message:

'Attaching T-SQL debugger failed. The debugger is not properly installed.'

Interestingly enough for me it was a problem with having database connections in the Server Explorer tab of Visual Studio. Removing these caused the error to go away and my debugging to work!

Also, using release versions of the stored procedure libraries didn't seem to work for me, although I may be overlooking something here.

Anyway, I hope this helps someone along their way! :)

Using external styles with static resources in Silverlight

I've recently had the need to apply an external style (i.e. one thats outside the .xap file) which includes static resources and at runtime.

... Anyone starting to see a problem here? Maybe not, its actually a little subtle. Basically, the problem is surrounding the use of static resources, and in particular trying to change a static resource at runtime.

The short and curlys are that it's not an easy thing to do. As the resource is static, its loaded once at the start (during the InitializeComponent() call in the App.xaml.cs contructor) and if you want to change it there after, you have to find the particual resource from the application resource dictionaries (see Application.Current.Resources) and changes its value manually. I've found that even clearing the resource dictionary and re-loading didn't work either... go figure!

So my solution was to not even bother to load the generic style (containing static resources) if there was an external one we wanted to use instead. A bit like the code below:

public partial class App : Application
    {
        private static IDisplayPage m_currentPage = null;
        private IDictionary m_initParams = null;

        public App()
        {
            this.Startup += this.Application_Startup;
            this.Exit += this.Application_Exit;
            this.UnhandledException += this.Application_UnhandledException;     
        }

        /// 

        /// Occurs when the application is started/// 
        /// 
        /// 
        private void Application_Startup(object sender, StartupEventArgs e)
        {
            // Set the init params and intialise the application
            m_initParams = e.InitParams;
            Initialise();
        }

        private void Initialise()
        {
            // We need to load the new theme before InitaliseComponent so that the right static resources are loaded first time!
            // Otherwise the defaults will be loaded and the newer ones are ignored.
            string themeLocation = ConfigurationManager.Settings["ThemeLocation"];
            if (string.IsNullOrEmpty(themeLocation) == false)
            {
                // We have a theme location present, so try to load it! The application will be 
                // loaded after the theme is downloaded and applyed to the application resources.
                LoadTheme(themeLocation);
            }
            else
            {
                // Skip loading the theme as we haven't supplied one. Go straight for the good stuff!
                LoadApplication();
            }
        }

        /// 

        /// Loads the given theme into the application resources
        /// 
        /// 
        private void LoadTheme(string themeLocation)
        {
            // Create the client to load the file
            WebClient client = new WebClient();

            // When the download is complete, apply it!
            client.DownloadStringCompleted += ThemeDownloaded;

            // Start the download of the theme off. The location should be local as the screen will be blank while downloading!
            client.DownloadStringAsync(new Uri(themeLocation, UriKind.RelativeOrAbsolute));
        }

        /// 

        /// Occurs when an external theme has finished downloading
        /// 
        /// 
        /// 
        private void ThemeDownloaded(object sender, DownloadStringCompletedEventArgs e)
        {
            string themeLocation = ConfigurationManager.Settings["ThemeLocation"];

            if (!e.Cancelled && (e.Error == null))
            {
                try
                {
                    // Read in the external style and store it as a resource dictionary
                    ResourceDictionary dictionary = XamlReader.Load(e.Result) as ResourceDictionary;

                    if (dictionary != null)
                    {
                        // If the reading was successful, store it as our current dictionary
                        Application.Current.Resources.MergedDictionaries.Add(dictionary);
                    }
                }
                catch (XamlParseException ex)
                {
                    ServiceClient webService = new ServiceClient();
                    webService.LogErrorAsync("Problem parsing theme (" + themeLocation + "): " + ex.Message, ex.StackTrace, SLUtilities.GetClientVersion());
                }
            }
            else
            {
                if (e.Error != null)
                {
                    ServiceClient webService = new ServiceClient();
                    webService.LogErrorAsync("Problem loading theme (" + themeLocation + "): " + e.Error.Message, e.Error.StackTrace, SLUtilities.GetClientVersion());
                }
            }

            // After our attempt at loading an external theme, load the rest of the application
            LoadApplication();
        }

        /// 

        /// Loads the rest of the application, initialising the components if an external theme hasn't already been loaded.
        /// 
        private void LoadApplication()
        {
            // If we haven't been able to reach an external theme for the app, use the internal default
            if (Application.Current.Resources.MergedDictionaries.Count == 0)
            {
                // Initialise the main app.xaml page, including resources and styles
                InitializeComponent();
            }

           // Do the rest of our application specific setup
        }
}

So as you can see, the application when first loaded, checks a settings file in the .xap and a location to an external theme. If it can find it, the theme is downloaded and instead of calling InitializeComponents(), we just load the resources in the theme instead. If there is no location for an external theme, we just load up the generic resources and styles using InitializeComponents() as normal.

I have to say, not the most satisfying work around, but it does work!

Hope this helps someone!