Automatically log errors with Elmah in Script0Service-WebService calls

If you’ve used Elmah logging in the past then you know it’s a very useful and easy to set up tool in your web applications.  However, when we encounter errors in web services we sometimes do not get the desired logging into the xml file or database, depending on how you have Elmah configured.  This is because when a error occurs in a script service enabled web service, the errors get serialized into JSON and returned to the ajax caller which is then take by the javascript to handle the exception.  The application is left unaware of the exception occurrence.

There is a way however, to intercept the message before it is returned to the front-end and log the error automatically the way we are used to with Elmah.

First we set up an ErrorHandlerFilter class which will override the default error stream message ( {“Message”:”There was an error processing the request.”, “StackTrace”:””, “ExceptionType”:””})} with a more meaningful error message with details.

   1: using System;

   2: using System.Collections.Generic;

   3: using System.IO;

   4: using System.Linq;

   5: using System.Text;

   6:  

   7: namespace Namespace.ElmahExtension

   8: {

   9:     public class ErrorHandlerFilter : Stream

  10:     {

  11:  

  12:         private readonly Stream _responseFilter;

  13:  

  14:         public List<byte> OriginalBytesWritten { get; private set; }

  15:  

  16:         private const string Content =

  17:           "{\"Message\":\"There was an error processing the request.\"" +

  18:           ",\"StackTrace\":\"\",\"ExceptionType\":\"\"}";

  19:  

  20:         public ErrorHandlerFilter(Stream responseFilter)

  21:         {

  22:             _responseFilter = responseFilter;

  23:             OriginalBytesWritten = new List<byte>();

  24:         }

  25:  

  26:         public override void Flush()

  27:         {

  28:             byte[] bytes = Encoding.UTF8.GetBytes(Content);

  29:             _responseFilter.Write(bytes, 0, bytes.Length);

  30:             _responseFilter.Flush();

  31:         }

  32:  

  33:         public override long Seek(long offset, SeekOrigin origin)

  34:         {

  35:             return _responseFilter.Seek(offset, origin);

  36:         }

  37:  

  38:         public override void SetLength(long value)

  39:         {

  40:             _responseFilter.SetLength(value);

  41:         }

  42:  

  43:         public override int Read(byte[] buffer, int offset, int count)

  44:         {

  45:             return _responseFilter.Read(buffer, offset, count);

  46:         }

  47:  

  48:         public override void Write(byte[] buffer, int offset, int count)

  49:         {

  50:             for (int i = offset; i < offset + count; i++)

  51:             {

  52:                 OriginalBytesWritten.Add(buffer[i]);

  53:             }

  54:         }

  55:  

  56:         public override bool CanRead

  57:         {

  58:             get { return _responseFilter.CanRead; }

  59:         }

  60:  

  61:         public override bool CanSeek

  62:         {

  63:             get { return _responseFilter.CanSeek; }

  64:         }

  65:  

  66:         public override bool CanWrite

  67:         {

  68:             get { return _responseFilter.CanWrite; }

  69:         }

  70:  

  71:         public override long Length

  72:         {

  73:             get { return _responseFilter.Length; }

  74:         }

  75:  

  76:         public override long Position

  77:         {

  78:             get { return _responseFilter.Position; }

  79:             set { _responseFilter.Position = value; }

  80:         }

  81:     }

  82: }

Now, we’re going to need to set up a class that implements IHttpModule.  which we’ll use to intercept the message being passed after the Request and EndRequest handlers execute.

   1: using System;

   2: using System.Diagnostics;

   3: using System.IO;

   4: using System.Text;

   5: using System.Web;

   6: using System.Web.Services.Protocols;

   7: using Elmah;

   8:  

   9: namespace Namespace.ElmahExtension

  10: {

  11:     public class ElmahExtension : IHttpModule

  12:     {

  13:         private HttpApplication _application;

  14:  

  15:         public void Dispose()

  16:         {

  17:         }

  18:  

  19:         public void Init(HttpApplication context)

  20:         {

  21:             _application = context;

  22:             _application.PostRequestHandlerExecute += OnPostRequestHandlerExecute;

  23:             _application.EndRequest += OnEndRequest;

  24:         }

  25:  

  26:         private void OnPostRequestHandlerExecute(object sender, EventArgs e)

  27:         {

  28:             var context = sender as HttpApplication;

  29:             if(context != null && (context.Request.Path.Contains(".asmx") && (context.Response.StatusCode >= 400 && context.Response.StatusCode < 600)))

  30:             {

  31:                 context.Response.Filter = new ErrorHandlerFilter(context.Response.Filter);

  32:             }

  33:         }

  34:  

  35:         static void OnEndRequest(object sender, EventArgs e) 

  36:         {

  37:             var context = (HttpApplication)sender;

  38:             var errorHandlerFilter = context.Response.Filter as ErrorHandlerFilter;

  39:  

  40:             if (errorHandlerFilter == null)

  41:             {

  42:                 return;

  43:             }

  44:  

  45:             var originalContent = Encoding.UTF8.GetString(errorHandlerFilter.OriginalBytesWritten.ToArray());

  46:             ErrorLog.GetDefault(context.Context).Log(new Error(new Exception(originalContent)));

  47:         }

  48:     }

  49:  

  50: }

Notice in the OnPostRequestHandlerExecute function we’re intercepting any request that comes from a Webservice (.asmx page) and has a status code of greater than 400 (All error code types).  At his point we want to set the ErrorHandelerFilter to the current Response.Filter value.

Now in the OnEndRequest function we want to get the current context and set the error handler based on the filter which should now be persisting the values we set in the previous function.  Once we have the error values all we have to do is call the Elmah manual logging API and log the exception.

The final piece you have to do is set up the webConfig to recognize and use your IHttpModule code. n In your config file navigate down to System.Web >> HttpModules and fill in the attribute accordingly.

   1: <system.web>

   2:     <httpModules>    

   3:       <add name="ElmahExtension" type="Namespace.ElmahExtension.ElmahExtension"/>

   4:     </httpModules>

   5: </system.web>

Once you compile and run, any errors encountered in your web services should now be logged by Elmah just like any others.

 

Thanks to Reddy Kadasani, Mike Gerety and Dylan Barrett for their contributions to this solution.

Posted in .NET Framework | Tagged , | Leave a comment

Two more tools for your bag of T-SQL tricks

Have you ever been writing a new view and had the need to use an ORDER BY only to be greeted by an error message when you go to create the view. Never fear, there is a solution. ORDER BY can only be used in views, inline functions, derived tables, sub-queries, and common table expressions if the keywords TOP or FOR XML are used. Thus we can either use TOP or FOR XML to get our solution. We will on talk about the TOP Keyword today and save FOR XML for another time.

TOP allows you to select the first ‘n’ records returned from a select. Consider the following table of s

Posted in Uncategorized | Comments closed

Calling an ASP.Net web service using jQuery and JSON.

Let’s say you have a webpage where you need to call a service but cannot perform a post back.  Recently I was on a client engagement where we needed to improve page performance by dynamically loading a navigation tree with a potential for several thousand links.  We implemented a solution that would load each branch as the user clicked on the expanding icon by calling a web service via ajax and passing the required links back to the page and render them client side.  This saved immensely on the load time for the page and improved the user experience

This kind of solution can be applied to several situations such as complex data processing, dynamic loading, or combining them all into a seamless form submission process that would clear the form on post backs.

We can accomplish this process simply by grabbing an event, such as a button click, and collecting all the required information using jQuery.  Next using a JSON call, we can pass all the parameters to the web service and execute our code from there.

Here, I’ll start with a simple .aspx page with jquery, a textbox, and link that will be used to call the web service.

   1: <%@ Master Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ThisPage.cs" Inherits="ThisSolution.ThisProject.ThisFolder" %>

   2:  

   3: <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">

   4:     <script src="/Scripts/jquery-1.4.1.min.js" type="text/javascript"/>

   5: </asp:Content>

   6:  

   7: <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

   8:     <asp:TextBox ID=”textBoxTextMode="MultiLine" runat="server"></asp:TextBox>


   9:     <a href="#" class="buttonClass"> Save </a>

  10:     <asp:Label ID=”successLabelrunat="server"></asp:Label>

  11: </asp:Content>

Next, you will need to establish your web service and create an .asmx file.  There are a couple of important things to note here. Line 5 will be by default commented out.  So in order for us to make our JSON call we’re going to need this uncommented.  Also, notice I’m returning a string.  For the purposes of this demo I am going to return a string to the UI and display a message, but you can return any kind of object that can be serialized down to a primitive data type.

   1: [WebService(Namespace = "http://tempuri.org/")]

   2: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

   3: [System.ComponentModel.ToolboxItem(false)]

   4: // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 

   5: [System.Web.Script.Services.ScriptService]

   6: public class wService : System.Web.Services.WebService

   7: {

   8:     [WebMethod()]

   9:     [ScriptMethod(UseHttpGet = false, ResponseFormat = ResponseFormat.Json)]

  10:     public string UpdateComment(string textBoxText)

  11:     {

  12:         var success = Service.CallToDb(textBoxText);

  13:  

  14:         if(success)

  15:         {

  16:             return "Call to Database using text was successful";

  17:         }

  18:         else 

  19:         {

  20:             return "Call to Database using text was not successful";

  21:         }    

  22:     }           

  23: }

I use a jQuery selector to pick up on the mouse click of the link and gather our data into an javascript object.  Note that when passing parameters through this object you have to name them the same way as you do in the parameter declaration of the web service.

When setting up the call to the service you’re going to want to remember to call the “JSON.stringify” function on your data object so that it serializes properly and that your contentType as well as dataType are configured properly as well.

   1: <script type='text/javascript>

   2:     $('.buttonClass').live('click', function(){

   3:         var text = $('#textBox').text();

   4:         var data = new Object();

   5:         data.textBoxText = text;

   6:  

   7:         $.ajax({

   8:             type: "POST",

   9:             url: "/Ajax/WbServcie.asmx/UpdateComment",

  10:             data: JSON.stringify(data),

  11:             contentType: "application/json; charset=utf-8",

  12:             dataType: "json",

  13:             success: function(msg){

  14:                 $('#successLabel').val(msg);

  15:             },

  16:             error: function(msg){

  17:                 alert('Something, somewhere, has gone terribly wrong');

  18:                 return false;

  19:             }

  20:         });    

  21: </script>

Once you get a response from the service, you can set any of your data that is returned.  In this case, we’re returning a string indicating the call to the database was successful and will set a label on the page to show that.

And that’s it.  With just a few simple configurations we have a quick call back to the server without having to run an expensive post back.

Posted in .NET Framework, Ajax, Enterprise .NET, JavaScript, jQuery | Tagged , , , , | Leave a comment

Mobile Site Development becoming as important as Desktop.

In recent years, the boom in mobile devices has caused more and more users to abandon their desktop internet browsers and opt for the ones sitting in their pocket. As a result, nearly 10% of 2011’s internet traffic was attributed to mobile device browsers. As more people are browsing the internet in this manner, it is important that currently standing websites, as well as newly developed ones, also include mobile friendly pages in their site.

Many companies have followed models put forth by sites such as www.amazon.com, with their widely known desktop interface for purchasing products. When the mobile markets started to pick up they developed a mobile site that operates in much the same way, but offers a friendlier user experience for those viewing it on a smartphone screen. This move to a mobile intuitive platform can generate an increase in traffic and user engagement with the site by up to 85%.

clip_image001

A push to a mobile platform such as this doesn’t have to be expensive either. On a recent client engagement, I developed a mobile version of their desktop website that was established prior to my arrival. While building the mobile pages I was able to reuse a lot of the code behind, but with some simple device detection logic I finished the project a lot sooner and cheaper than if I had to develop a new site form square one.

Currently, 1 in 4 mobile devices are smartphones, and with the relatively low incremental development cost, and the prospect that mobile device users will overtake the number of desktop casual browsers by 2014, the importance of developing mobile friendly sites in tandem with their desktop versions will increase and will be a great benefit to any client with public facing websites.

Mobile usage statistics courtesy of KissMetrics

Posted in Uncategorized | Tagged , , , | Leave a comment

SSIS FTP Task

 Experience 1 : No ServerPassword? 
The client required a scheduled transfer of multiple TIF files from one server to another over FTP.  Since this task was initially implemented using SSIS, the change seemed to be trivial; I would create a new FTP Connection in the job’s Connection Manager window and replace the current copy task (a Script Task using System.IO functions) with an FTP task inside a For Each Loop to iterate over each file, copying them one by one to the FTP server. 
 
 
 

The "Set FTP Paths" Script Task sets path variables that the FTP Task uses.

After a few “trial and error”s due to some changes in the parameters the process ran successfully in debug mode from my local computer.  Happy that I had completed the task, I started the deployment to our QA server so that I could test a scheduled instance of the job.  This is where I ran into a bit of a headache.

Deployment consisted of creating a configuration file (.dtsConfig file) with all the specific parameters, copying this along with the job file up to the server, and scheduling the job using the config file in Jobs under SQL Server Agent of SQL Server Management Studio.  After getting through a few environment errors related to the configuration file, I found the following error in my log.

“An Error occurred in the requested FTP operation. Detailed error description: The password was not allowed.”

After a bit of troubleshooting I finally realized the password in the Connection String that I was setting using an expression in the FTP Connection was not being carried over correctly to my QA server.  In fact neither was the Username and the only reason the job was running correctly on my local computer was that I had manually set the values into the FTP Manager Connection Editor window when I initially created the FTP Connection.  I then went on to try to set the Username and Password specifically in the Expression Editor for the FTP Connection.  As you can see in the below screen shot, SSIS lets you set the Username (ServerUserName) but does not let you set the Password for an FTP Server.

No ServerPassword!?

(As mentioned in the comment section of a link I reference below, it makes absolutely no sense for them to exclude this property considering the passwords for FTP transfers are sent in plain text .. but I wont get into that.)

The reason the job kept failing out on the QA server was that the Username and Password were not being included in the ConnectionString of the FTP Connection in the dtsConfig file. After a bit of internet searching I came across this article.  In it Frank goes over exactly what the problem was with my job; when you create an FTP Connection in SSIS you cannot set the password through a Connection String expression.  As he displays, one way to get around this is to write a Script Task and manually set the “ServerPassword” parameter with a hard coded string.

SSIS 2012 seems to use the FTP Task so unless something changes, be ready to use this hack in your future SSIS packages using FTP.

Experience 2 : What’s Thumbs.db?

This experience was a little less frustrating than Experience 1 but still annoying.  For my specific package, after the FTP transfer is completed, there is validation performed that compares a count of how many files were supposed to be sent with how many files were actually sent.  This validation failed so I went out to the destination FTP server and found a file called Thumbs.db.  For whatever reason I did not use an expression to filter only the .TIF files I wanted from the source folder, and instead just took every file there assuming there would never be anything else in there.

The counts in the validation were off and were causing the job to fail due to the Thumbs.db file Windows Server 2003 creates to show the thumbnails of images in a Windows explorer window.  This file is not visible when viewing the folder but the FTP process was picking it up and causing the validation to fail.  After adding a simple filter of *.TIF files, the process did not pick up the Thumbs.db and ran successfully.

Posted in Uncategorized | Leave a comment

Creating a Helpful Extension Method for Visibility in XAML

In this post, I’ll show you how to create an extension method for the bool class that will simplify your .NET code in XAML-based apps (either WPF or Silverlight). In particular, this extension method addresses the fact that the Visibility property of UI controls in XAML is not a Boolean (true/false) value (as has traditionally been the case for both Windows Forms and ASP.NET). Instead, it’s one of three possible enumerated constant values: Hidden, Collapsed, and Visible.

Visible obviously means “visible,” while both Hidden and Collapsed mean “invisible.” The difference between Hidden and Collapsed is merely whether or not the invisible control occupies blank space on the screen that it would occupy if it were visible. Collapsed consumes no space, while Hidden does.

It’s nice to have the three options, but in most cases you’ll find that you just need the two options Visible and Collapsed. If you’re setting visibility to XAML controls using these two enums, you’ve probably noticed that it’s just not as clean as it is with Windows Forms or ASP.NET controls. You typically already have a Boolean value—either as a simple variable, or an expression—that determines whether the control should be visible or not. You could use that value to enable or disable the control, as the IsEnabled property is also a Boolean, but you can’t assign it to the Visibility property because that property expects one of the three Visibility enumerations—not a Boolean. That’s frustrating, because from a UX (user experience) perspective, where these concepts are used to convey “availability” to the user, hiding/showing controls versus enabling/disabling them is a subtlety of UI design. So developers should be able to think (and code) freely in terms of Booleans for both IsEnabled and Visibility.

Consider the C# code to manipulate the Visibility and IsEnabled properties of a button control:

OpenCustomerButton.IsEnabled = false;
OpenCustomerButton.IsEnabled = true;
OpenCustomerButton.Visibility = Visibility.Collapsed;
OpenCustomerButton.Visibility = Visibility.Visible;

bool isAvailable = true;
OpenCustomerButton.IsEnabled = isAvailable;
OpenCustomerButton.Visibility =
  (isAvailable ? Visibility.Collapsed : Visibility.Visible);

// Set the visibility according another button's enabled/disabled state
OpenCustomerButton.Visibility =
  (ManageCustomersButton.IsEnabled ? Visibility.Visible : Visibility.Collapsed);

You can see that because Visibility takes the enum rather than a bool, it’s harder to work with than IsEnabled, and just isn’t as neat. But we can do something about this. How? By writing a simple (one-line!) extension method that adds a ToVisibility method to the bool class:

public static class BoolExtensions
{
  public static Visibility ToVisibility(this bool isVisible)
  {
    return (isVisible ? Visibility.Visible : Visibility.Collapsed);
  }
}

Now the same UI code is much easier to read and write:

OpenCustomerButton.IsEnabled = false;
OpenCustomerButton.IsEnabled = true;
OpenCustomerButton.Visibility = false.ToVisibility();
OpenCustomerButton.Visibility = true.ToVisibility();

bool isAvailable = true;
OpenCustomerButton.IsEnabled = isAvailable;
OpenCustomerButton.Visibility = isAvailable.ToVisibility();

// Set the visibility according to another button's enabled/disabled state
OpenCustomerButton.Visibility = ManageCustomersButton.IsEnabled.ToVisibility();

Like? Me too. Neatness counts!

It would be even better if we could instead extend every UIElement with a Boolean property called IsVisible, and embed the conversion logic bi-directionally in the property’s getter and setter. But, unfortunately, you cannot create extension properties in .NET, only extension methods. So extending bool with a ToVisibility method is the next best thing.

One more tip: Put the BoolExtensions class inside a common code namespace that all your XAML classes import, so ToVisibility is always available without needing to add an extra using statement.

Remember, abusing extension methods quickly leads to messy, buggy code. Instead, find appropriate uses for extension methods that yield cleaner, tighter code.

Posted in .NET Framework, User Experience Design | Tagged , , , | Leave a comment

Overriding jQuery Functions

In my last post I showed how to create a localized instance of jQuery that performed custom selection by handling ID selectors in a specific way. There are other parts of jQuery though that can have IDs passed in for arguments, such as the find and children functions. These functions can also be customized to override the default behavior as well. In this example, if the find and children functions are left unchanged, they will fail find any elements by ID if we don’t include the end part of the ID that we append in the $$ function. So let’s look at overriding / extending the find function.

// Extended .find(selector) to append the _elementId to any
    // ID selector (containing "#")
    (function ($$) {
        var originalFind = $.fn.find;
        $$.fn.find = function (selector) {
            selector = getFullSelectorIds(selector);
            return originalFind.call(this, selector);
        };
    })(jQuery);

In the above code we are using a self-executing function to replace jQuery’s find function (specifically for the $$ instance, not the default instance). In this example we do not want to completely remove the original find function, since we do want to use it, we just need to do some preprocessing of the selector. What we do is store a copy of the original, (in originalFind) and then we replace find with our new one. The new one works just like the $$ function in my previous post, only we just have the one argument to process. Then to actually perform the find, we call the originalFind using the processed selector.
Of course this is just one specific case, but any of the built-in jQuery functions can be overridden in the same way. Likewise this is how one would add completely new functionality to jQuery.

Posted in JavaScript, jQuery | Leave a comment

Customizing the Default jQuery Selector Behavior

jQuery on its own is a great JavaScript library that makes a lot of tasks simpler and easier.  Although as easy as it is you may find yourself needing to work within a restricted scope and not constantly wanting to have to include the context for every selector.  This exact case happened on our project.  We had the need to change from supporting just a single instance of a rather complex user control with a large amount of JavaScript, to having several instances of this control on the same page.  The JavaScript for this control utilized jQuery selectors all over the code, making it fairly tedious to accomplish without some help.   To make things easier we needed to restrict jQuery to the specific container that holds the instance of the control that we are interested in.  Now one way to do that would be to simply pass the context into the jQuery selector.

So something like this:  $(‘myselector’) would become $(‘myselector’, context), with context = $(‘mycontrolscontainer’).

Now that certainly would work, but with a large number or selectors in the controls JavaScript it can take quite a long time to replace all of these.

Instead of changing every jQuery selector, we can instead create a private jQuery instance, that automatically uses the correct context.  First we need to assume that each of these controls on the page is limited to the scope of its instance.  Within this you need to define a new variable for the instance specific jQuery, lets call it $$:

var $$ = function (selector, context) {
        selector = getFullSelectorIds(selector);
        context = getFullSelectorIds(context);
        return $(selector, context);
    };

This function $$, takes in a selector and a context, just like the normal $ function, however it does some extra work, using another function “getFullSelectorIds” which takes in either the selector or the context.  Then it uses the output of that function to pass into the normal $ function, which behaves just as it always had.

function getFullSelectorIds(selector) {
        // Selector string (looking for id(s)), check for _elementId appended
        if (typeof selector == "string" && selector.indexOf("#") != -1) {
            var ids = selector.split(",");
            var len = ids.length;
            for (var i = 0; i &lt; len; i++) {
                // Looking for id
                // _elementId is not appended, so add it
                if (ids[i].indexOf("#") != -1 && ids[i].indexOf(_elementId) == -1) {
                    var subIds = ids[i].split(":");
                    subIds[0] = subIds[0] + _elementId;
                    ids[i] = subIds.join(':');
                }
                // _elementId is already appended or not looking for id in this one, no change
            }
            selector = ids.join(",");
        }
        return selector;
    }

The “getFullSelectorIds” function is the part that is special and will vary depending on your needs.  For our project we append a certain string to the end of all the IDs in the control.  This allows the IDs for the same part of each control instance to start with the same ID but end with the control specific one.  What we do here is append the _elementId string to any selectors that are string types, which are selecting based on ID’s (evidenced by the string containing “#”).  We also handle the  case where you have a comma separated list of selectors (that may be a mix of IDs, classes and others).

Now that we have the $$ function and the getFullSelectorIds function (which does all the real work), we are still not quite done.  Currently our $$ function is missing a number of nice jQuery functions.  To copy all of them over into the $$ we need to do the following:

$$.fn = $$.prototype = jQuery.fn;
jQuery.extend($$, jQuery); // copy's trim, extend, find, etc to $$

What this does is copy the jQuery prototype into the  prototype of $$.  Now the last step is to use it.  To use it all we need to do is replace any instances of $ with $$ and we are done.  Of course you can still use the regular $ function anywhere you want with no change to its functionality, or when you need to be scoped to within a specific control, just use that control’s $$.

Posted in JavaScript, jQuery | Leave a comment

Setting Up Azure Deployment Certificates

Azure deployments can be integrated within Visual Studio 2010, making it easy for you to package, deploy and publish your solution to an Azure cloud.  This blog post will show you how to integrate your Azure deployments usingVisual Studio 2010 with an easy two step process.  First, you have to create a certificate within Visual Studio, and second, you have to configure your Azure environment to accept that certificate.

  1. To create a certificate, begin by right clicking your project in the Solution Explorer pane.
  2. Then, in the Credentials dropdown, select <Manage…>
  3. Then, select New.
  4. In the window that appears, 3 steps must be completed before proceeding.  To complete step 1, expand the first drop down and select Create… and create a certificate.  To complete step 2, click “Copy the full path”.  Now, we have to digress from this dialog box and cover the second part of our exercise (configuring the Azure portal to accept our certificate), before coming back to finish up here.
  5. Login to the Windows Azure Portal, click Hosted Service, Storage Accounts & CDN, then click Management Certificates.  Click Add Certificate, (upper left) which opens a dialog box.  Paste the path to the certificate path (the certificate path was put in the clipboard during Step 4) in the dialog box, and then click OK.

  6. Copy the Subscription ID from the field as show above.  The ID has been censored in the screenshot in the screenshot, but will appear as a hexadecimal string similar to the example ID shown in Step 4.
  7. Paste the Subscription ID into the dialog box from Step 4, and click OK.

To deploy your application to the Azure cloud from within Visual Studio, right click your project and select Publish (same as Step 1).  In the dialog box that appears (shown in Step 2) click Publish.

Posted in Cloud | Tagged | Leave a comment

A Look at How to Migrate an Access Database to SQL Server Part 1 of 2

Recently I was tasked with migrating a Microsoft Access 2010 database to SQL Server 2008 R2 while preserving the form functionality built into Access.  While the migration using Microsoft Access’ ‘Upsizing Wizard’ was easy enough for a majority of the objects, I found that a few of the more complex Access queries and tables needed manipulation or outright manual conversion to objects that both SQL Server and Access agreed with.  In this two part blog series I will go through how to use the upsizing wizard to migrate an Access mdb file to an Access Project file connected to a SQL Server backend.  In the second part of this series I will go over some possible roadblocks and issues that could arise, as well as their solutions.

This sample application will have a a simple form opening two parameterized queries, one of which having a few columns formatted as lookups to other tables.  We will be migrating both tables and queries, while retaining the form and and VBA code tied to the form.

  • First we will open the the sample access_to_sql_tutorial.mdb file and select the ‘SQL Server’ button in the ‘Move Data’ group of the  ‘Database Tools’ Tab.

Note: Make sure there are no forms or objects open when using the Upsizing Wizard, as those objects may fail to be migrated to SQL Server.

image

  • Select ‘Create New Database’, then select ‘Next’.
  • Enter the destination SQL Server name and instance name, and name the database to be created.  Select ‘Next’.

image

  • The next screen will prompt to select the tables to export and link to SQL Server.  Select the relevant tables then select ‘Next’.

image

  • The default values in the next screen should be sufficient for simple migrations.  The only option which may be relevant would be the checkbox: ‘Only create the table structure; don’t upsize any data’.  Select ‘Next’.

image

  • In the next screen, you will get the option to either link all of the tables to SQL Server to the existing application (mdb), or create a new ‘Access Project’ file which truly functions as a client / server application.  For this tutorial we will go with the later option ‘Create a New Access client/ server application’.  Give the new application a name and click ‘Next’

image

image

  • In the next screen you can choose to either open the new adp file (if you selected the option to create a new project file in the previous screen), or keep the current pre migrated access file open.  Select ‘Finish’.
  • The next screen will show the progress of migrating to SQL Server.

image

  • When completed, you will be prompted with the Migration report which will list every object migrated and the migration status.

image

  • After the migration has finished you will be brought to the adp project.  You can now go into SQL Server Management Studio and verify the new SQL Server database has been created.

image

  • You can see from the list of database objects that the tables have been successfully migrated,  though the ‘PRODUCT DATA’ query has not.  Simple non parameterized queries are migrated as views in SQL Server, though since this query used parameters it would have to be migrated as am stored procedure or table-valued function.

In the next blog post in the series I will discuss how to migrate the parameterized ‘PRODUCT DATA’ Access query to a compatible table-valued query in SQL Server, and I will also go into possible pitfalls and roadblocks that may appear during migration.

Note: Attached to this article is the sample Access 2010 application used during this tutorial.

Posted in Microsoft, SQL Server, Uncategorized | Tagged , , , , , , | Leave a comment