Connecting to a Remote SQL Server and Import Data from your local SQL Server (2008 Express)

I have a remote database sitting on VPS and I need to transfer the data to the remote server. All I do is first register/connect the remote Sql server in my Management Studio. For this, enter the provided server name using the registered domain name (or IP Address)  along with the provided port number. Append the Sql server name (I appended \SQLExpress).

Doing this will enable you to connect the remote server in your local management studio (assuming you enter the correct authentication details for username/password !).

Before you go ahead with the next step of doing data migration, make sure first you run the database scripts so that your indexes/foreign keys are intact. Once this is done, you can use the import/export wizard to export data from your local machine to the remote server. A tiny trick is to use the import wizard from the destination database rather than using the export wizard from the source database. This will ensure your configuration for destination database is aptly configured.

Sorry this is very bland write-up without any screenshots or proper explanation, but hopefully I'll provide them when I have more time.

Profiler for SQL Express

I have SQL Express currently installed on my machine. Since I am working on the "free" version, the package doesn't include SQL Profiler. I highly depend on SQL Profiler to trace down queries. What to do now? Well, Google is my friend! I found this utility, available on Codeplex. Download and use it!

Links to Bookmark and Follow up

Some Notes on Semantic Versioning. Follow up on this, Usman! 

http://semver.org/

The World of Entity Framework :

http://msdn.microsoft.com/en-US/data/ee712907

Entity Framework Version History :

http://msdn.microsoft.com/en-us/data/jj574253

AJAX Caching issue in Internet Explorer (IE) 9 (way back upto IE 6)

I had been pulling my hair apart for resolving the browser compatibility issue. My MVC App has been working well in other browsers, but while doing testing, I found a notorious issue in IE. I had been hung on this issue for several hours. Tried clearing history/cache.. No luck. Tried using the cache trough developer tools (F12)... No luck.. Tried resetting the Browser to its default installation, thinking I might have accidently clicked on a setting for the browser to malfunction... No luck! restarted the machine a couple of times.... But I guess I was looking at the wrong place. Doing a google search for the correct keywords might have helped in the first place! Further research led me to find that IE caches the AJAX requests while others don't. Thats actually BAD news for me, because that gives me inconsistency in data.


Fellow internet geeks had posted a few solutions to overcome this issue:

http://dougwilsonsa.wordpress.com/2011/04/29/disabling-ie9-ajax-response-caching-asp-net-mvc-3-jquery/

http://www.dashbay.com/2011/05/internet-explorer-caches-ajax/

http://ivida.co.uk/2011/09/29/ie-9-ajax-request-caching-mvc/




This worked for me! Yay!

$(document).ready(function() {

$.ajaxSetup({ cache: false });

});

Access the Log for the scheduled processes

The log for the scheduled processes (to trace the failures) is available at:

\\WINDIR\Tasks\SchedLgU.txt

Other resources that I found on the web regarding this can be looked here:

 http://blogs.technet.com/b/rspitz/archive/2010/11/07/scheduled-tasks-appear-hung-in-the-running-state-on-windows-server-2003-based-systems.aspx

Check the following link to increase the size of the file:
http://support.microsoft.com/kb/169443

Using Heidi as MySql Client to connect to remote server

I had to make some db modifications for one of my clients to the mysql database on the remote server. I hardly have any experience working on the php/mysql arena. Call it my lack of knowledge or understanding, but I had a hard time connecting to the remote server. I tried mysql workbench, but that would crash right when I went ahead to access the database. I was frustrated since I had to make one small change and it was sucking up my time. Then someone over the internet suggested HeidiSql.

Apart from being able to "connect successfully", HeidiSql is really fast and provides and great clean UI to manage your data. Point to be noted for future refs!!

Resolving the Validation of viewstate MAC failed error

I have been receiving the following error on the page when I clicked on the link (in the Gridview) before the Page had fully loaded. My Page has a Gridview which uses DataKeyNames. Additionally it had a lot of Javascript ads which slowed down loading of the Page.

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.




On doing some research, I found out that the due to the use of GridView and the DataKeyNames, the Framework implements a ViewState Encryption Technique for keeping the data secure. Hence, when a link is clicked on a partial page load, the encryption tag might not have been rendered on the page, causing the page to crash.  

I read this excellent post which aptly details the issue and its resolution.

Basically, it appears to be some sort of bug in the existing Framework and the blog posts a workaround.

I used Solution 3 since it still was keeping the Security aspects in place which executing the page elegantly. Basically what it did was to remove the Validation script right before the end of the form tag and place it after the beginning of the Tag. 

protected override void Render(System.Web.UI.HtmlTextWriter writer)
    {
        System.IO.StringWriter stringWriter =
            new System.IO.StringWriter();
        HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
        base.Render(htmlWriter);
        string html = stringWriter.ToString();
        string[] aspnet_formelems = new string[5];
        aspnet_formelems[0] = "__EVENTTARGET";
        aspnet_formelems[1] = "__EVENTARGUMENT";
        aspnet_formelems[2] = "__VIEWSTATE";
        aspnet_formelems[3] = "__EVENTVALIDATION";
        aspnet_formelems[4] = "__VIEWSTATEENCRYPTED";
        foreach (string elem in aspnet_formelems)
        {
            //Response.Write("input type=""hidden"" name=""" & abc.ToString & """")
            int StartPoint = html.IndexOf("= 0)
            {
                //does __VIEWSTATE exist?
                int EndPoint = html.IndexOf("/>", StartPoint) + 2;
                string ViewStateInput = html.Substring(StartPoint,
                  EndPoint - StartPoint);
                html = html.Remove(StartPoint, EndPoint - StartPoint);
                int FormStart = html.IndexOf("", FormStart) + 1;
                if (EndForm >= 0)
                    html = html.Insert(EndForm, ViewStateInput);
            }
        }

        writer.Write(html);
    }

Hiding Tabs(MenuItem) in asp:Menu

I was working on the asp:Menu where I have to hide a certain Tab if there is no content for it. This is what I did. I knew the index of the Tab that Had to be removed:

//tabs_Menu is the id for asp:Menu
MenuItemCollection menuitems=tabs_Menu.Items; 
menuitems.Remove(tabs_Menu.Items[1]);

Finding Duplicates and copying Excel formula to the end of data

I have an excel file that has about 100,000 records. I wanted to find out the duplicates in a particular column before removing them. So I had to mark the rows with something as an identifier. Below is what I found for the formula. So essentially if the duplicates are in Column A, this formula should be placed in an empty column(or a new column):

=IF(COUNTIF(A:A, A1)>1, "Duplicate !","")

I had to apply this formula to all the 100,000 records, and someone over the internet posted a nice example of doing this:

http://www.handyexceltips.com/2008/01/16/dragging-a-formula-down/

Very useful in those time crunches !

Batch Update using SQL

Though this is a fairly easy process, its worth mentioning here since it is something which cannot be thought about. Using top x in your query, you could basically choose how many records need to be updated. This is helpful for processing the records as required

UPDATE top (100)  TableName 
SET
    column1 = getdate(),
    remarks = 'batch for API Upload'
 where column2 is not null and column1 > '2012-1-1'