European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

FREE SQL Server 2012 Hosting UK - HostForLIFE.eu :: An Application Error Occurred On The Server Running On SQL Server 2012

clock March 29, 2014 18:42 by author Peter

Recently one of my application website went down. I checked all the basic connectivity troubleshooting on my SQL Server 2012 Hosting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.

Error from the event viewer:

The quick solution is rebooting the SQL browser (Start –>All programs–>Microsoft SQL server 200X–>Configuration tools –>SQL server configuration Manager) without rebooting SQL service. I searched and found a couple of MS links (KB-2526552 And SQLBrowser Unable to start) but, I did not apply it. I used another way that is also a permanent fix.

Troubleshooting ways and a permanent fix:

For me it’s a named instance and listening a dynamic port and DBAs knows the browser service is mainly for named instance. From the local machine we can connect the server through SSMS by using server name and server name + port number. But, other than local machine you cannot connect the server by using server name. (You can test that by connecting some other server or better install only SSMS on the application server and try to connect it) so I went to the application server and opened a connection string as expected the data source only has the server name. So We changed it from Datasource “from Data Source=Servername\Instance to Data Source= Servername\Instance,port” Ex: Muthu1\SQL1,5432.

Application team made a standard to always include a port number in the connection string block i.e. FQDN. 

A Basic SQL Connectivity checks:

- Check SQL service is running or not and try to connect through SSMS from local and remote

- Check TCP/IP protocol enabled on SQL server configuration manager and find the port number

- Connect using a server+port number from SSMS local and remote

- For firewall block/port not opened you can check through command prompt TELNET server port ex: TELNET server 1433

- Check remote connections are enabled & SQL Browser service is running (For a named instance which is not using FQDN)

- Check you have any alias/DNS name.



European FREE ASP.NET 4.5 Hosting UK - HostForLIFE.eu :: Sending additional form data in multipart uploads with ASP.NET Web API

clock March 20, 2014 06:10 by author Peter

If you've used ASP.NET MVC you'll be used to being able to easily process multipart form data on the server by virtue of the ASP.NET MVC model binder. Unfortunately things are not quite so simple in ASP.NET Web API. Most of the examples I have seen demonstrate how to upload a file but do not cover how to send additional form data with the request. The example below (taken from the Fabrik API Client) demonstrates how to upload a file using HttpClient:

public async Task<IEnumerable<MediaUploadResult>> UploadMedia(int siteId, params UploadMediaCommand[] commands)

{

    Ensure.Argument.NotNull(commands, "commands");

    Ensure.Argument.Is(commands.Length > 0, "You must provide at least one file to upload.");

    var formData = new MultipartFormDataContent();

    foreach (var command in commands)

    {

        formData.Add(new StreamContent(command.FileStream), command.FileName, command.FileName);

    }

    var request = api.CreateRequest(HttpMethod.Post, api.CreateRequestUri(GetMediaPath(siteId)));

    request.Content = formData;

    var response = await api.HttpClient.SendAsync(request).ConfigureAwait(false);

    return await response.Content.ReadAsAsync<IEnumerable<MediaUploadResult>>().ConfigureAwait(false);

}

The UploadMediaCommand passed to this method contain a Stream object that we've obtained from an uploaded file in ASP.NET MVC. As you can see, we loop through each command (file) and add it to the MultipartFormDataContent. This effectively allows us to perform multiple file uploads at once. When making some changes to our API recently I realized we needed a way to correlate the files we uploaded with the MediaUploadResult objects sent back in the response. We therefore needed to send a unique identifier for each file included in the multipart form.

Since the framework doesn't really offer a nice way of adding additional form data to MultiPartFormDataContent, I've created a few extension methods below that you can use to easily send additional data with your file uploads.

/// <summary>

/// Extensions for <see cref="System.Net.Http.MultipartFormDataContent"/>.

/// </summary>

public static class MultiPartFormDataContentExtensions

{      

    public static void Add(this MultipartFormDataContent form, HttpContent content, object formValues)

    {       

        Add(form, content, formValues);

    }

    public static void Add(this MultipartFormDataContent form, HttpContent content, string name, object formValues)

    {

        Add(form, content, formValues, name: name);

    }

     public static void Add(this MultipartFormDataContent form, HttpContent content, string name, string fileName, object formValues)

    {

        Add(form, content, formValues, name: name, fileName: fileName);

    }

    private static void Add(this MultipartFormDataContent form, HttpContent content, object formValues, string name = null, string fileName = null)

    {        

        var header = new ContentDispositionHeaderValue("form-data");

        header.Name = name;

        header.FileName = fileName;

        header.FileNameStar = fileName;

        var headerParameters = new HttpRouteValueDictionary(formValues);

        foreach (var parameter in headerParameters)

       {

            header.Parameters.Add(new NameValueHeaderValue(parameter.Key, parameter.Value.ToString()));

        }

         content.Headers.ContentDisposition = header;

        form.Add(content);

    }

}

With these extensions in place I can now update our API client to do the following:foreach (var command in commands)

{

    formData.Add(

        new StreamContent(command.FileStream),

        command.FileName, command.FileName,

        new {

            CorrelationId = command.CorrelationId,

            PreserveFileName = command.PreserveFileName

        }

    );

}

This sets the content disposition header like so:

Content-Disposition: form-data;

    name=CS_touch_icon.png;

    filename=CS_touch_icon.png;

    filename*=utf-8''CS_touch_icon.png;

    CorrelationId=d4ddd5fb-dc14-4e93-9d87-babfaca42353;

    PreserveFileName=False

On the API, to read we can loop through each file in the upload and access the additional data like so:

foreach (var file in FileData) {

    var contentDisposition = file.Headers.ContentDisposition;

    var correlationId = GetNameHeaderValue(contentDisposition.Parameters, "CorrelationId");

}

Using the following helper method:

private static string GetNameHeaderValue(ICollection<NameValueHeaderValue> headerValues, string name)

{          

    if (headerValues == null)

        return null;

    var nameValueHeader = headerValues.FirstOrDefault(x => x.Name.Equals(name, StringComparison.OrdinalIgnoreCase));

    return nameValueHeader != null ? nameValueHeader.Value : null;

}

In case you were interested below is the updated code we are using to process the uploaded files within ASP.NET MVC:

[HttpPost]

public async Task<ActionResult> Settings(SiteSettingsModel model)

{

    await HandleFiles(new[] {

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.LogoFile, uri => model.LogoUri = uri),

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.IconFile, uri => model.IconUri = uri ),

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.FaviconFile, uri => model.FaviconUri = uri)

    });

    await siteClient.UpdateSiteSettings(Customer.CurrentSite, model);

    return RedirectToAction("settings")

        .AndAlert(AlertType.Success, "Success!", "Your site settings were updated successfully.");

}

private async Task HandleFiles(Tuple<HttpPostedFileBase, Action<string>>[] files)

{

    var uploadRequests = (from file in files

                          where file.Item1.IsValid() // ensures a valid file

                          let correlationId = Guid.NewGuid().ToString()

                          select new

                          {

                              CorrelationId = correlationId,

                              Command = file.Item1.ToUploadMediaCommand(correlationId),

                              OnFileUploaded = file.Item2

                          }).ToList();

    if (uploadRequests.Any())

    {

        var results = await mediaClient.UploadMedia(Customer.CurrentSite,

            uploadRequests.Select(u => u.Command).ToArray());

         foreach (var result in results)

        {

            // find the original request using the correlation id

            var request = uploadRequests.FirstOrDefault(r => r.CorrelationId == result.CorrelationId);

            if (request != null)

            {

                request.OnFileUploaded(result.Uri);

            }

        }

   }

}



SQL Server Hosting France - HostForLIFE.eu :: SQL String concatenation with CONCAT() function

clock March 10, 2014 08:05 by author Peter

We have been using plus sign (+) operator for concatenating string values for years in SQL Server with its limitations (or more precisely, its standard behaviors). The biggest disadvantage with this operator is, resulting NULL when concatenating with NULLs. This can be overcome by different techniques but it needs to be handled. Have a look on below code;

 -- FullName will be NULL for 
 -- all records that have NULL 

 -- for MiddleName 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + MiddleName + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- One way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- Another way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 

SQL Server 2012 introduced a new function called CONCAT that accepts multiple string values including NULLs. The difference between CONCAT and (+) is, CONCAT substitutes NULLs with empty string, eliminating the need of additional task for handling NULLs. Here is the code.

 SELECT  
   BusinessEntityID 
   , CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName 
 FROM Person.Person 

If you were unaware, make sure you use CONCAT with next string concatenation for better result. However, remember that CONCAT substitutes NULLs with empty string which is varchar(1), not as varchar(0).



SQL Server 2012 Spain Hosting - HostForLIFE.eu :: Configure a SQL Server Alias for a Named Instance

clock March 5, 2014 05:09 by author Peter

There are plenty of tutorials out there that explain how to configure an MS SQL Server alias. However, since none of them worked for me, I wrote this post so I'll be able to look it up in the future. Here's what finally got it working for me.

My Use Case

In my development team at work, some of our local database instances have different names. Manually adapting the connection string to my current local development machine every single time is not an option for me because it's error-prone (changes might get checked into version control) and outright annoying.

The connection string we're using is defined in our Web.config like this:

<add name="SqlServer" connectionString="server=(local)\FooBarSqlServer;…"   

providerName="System.Data.SqlClient" />

This is the perfect use case for an alias. Basically, an alias maps an arbitrary database name to an actual database server. So I created an alias for FooBarSqlServer, which allows me to use the above (unchanged) connection string to connect to my local (differently named) SQL Server instance. That was when I ran into the trouble motivating me to write this post. The alias simply didn't work: I couldn't use it to connect to the database, neither in our application nor using SQL Server Management Studio.

The Working Solution

I googled around quite a bit and finally found the solution in Microsoft's How to connect to SQL Server by using an earlier version of SQL Server: The section Configure a server alias to use TCP/IP sockets pointed out that I had to look up the specific port number used by the TCP/IP protocol:

Here's how you find the port number that's being used by TCP/IP on your machine:

1) Open the SQL Server Configuration Manager.

2) Expand SQL Server Network Configuration and select Protocols for <INSTANCE_NAME>.

3) Double-click on TCP/IP and make sure Enabled is set to Yes.

4) Remember whether Listen All is set to Yes or No and switch to the IP Addresses tab.

- Now, if Listen All was set to Yes (which it was for me), scroll down to the IPAll section at the very bottom of the window and find the value that's displayed for TCP Dynamic Ports.

- If Listen All was set to No, locate the value of TCP Dynamic Ports for the specific IP address you're looking for.

You'll have to copy this port number into the Port No field when you're configuring your alias. Note: that you'll have to set the Alias Name to the exact value used in your connection string. Also, if you're not using the default SQL Server instance on your development machine (which I am), you'll need to specify its name in the Server field in addition to the server name. In my case, that would be something like YourDirectory\NAMED_SQL_INSTANCE. Remember to also define the alias for 32-bit clients when your database has both 64-bit and 32-bit clients.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2012 Hosting, ASP.NET 4.5 Hosting, ASP.NET MVC 5 Hosting, SQL 2014 Hosting and SQL 2014 Hosting.

Tag cloud

Sign in