A basic SQL query to validate the distance via two Latitude / Longitude Points

In this Sprint, we add a new feature called Relocation. Developers complete this feature in C# and I need to validate if the distance between stores is correct when doing relocation. No one told me how to validate the distance. As a SDET, it is important to know how to test in my own way.

After doing some research, the following Excel formula works well if you have 2 Latitude and Longitude points.

ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

However, this is not what I want. What I want to do is:

Enter one Lat/ Long store information and the nearby stores within 16 Kilometers (about 10 Miles) appear in the database. I can use the result set to compare the data in Silverlight Application.

I can’t write a Store Procedure and a User-Defined function in SQL Server because after we push to Production, those can’t be implemented in Production and I still need to validate this scenario for my smoke test. Therefore, writing a basic SQL query is a good way.  Here is my solution:

·         We have the Longitude and Latitude Columns in the table for each store.

·         The Excel Formula is still working in SQL Server 2008 R2.

·         I need to manually enter Lat1 and Long1 as my baseline to validate the distance between stores. That being said, I don’t have to enter Lat2 and Long2 information.

After my several attempts, the following SQL Query is helpful to find the nearby stores after I enter the values of Lat1 and Long1. 

Next time if I want to use another store (Lat/Long) as a baseline to search the nearby stores within 16 Kilometers, it is easy for me to get the list of data. 

Fixed the issue: Microsoft PowerPivot for Excel 2010 breaks the feature of TFS Open Selection in Microsoft Excel

I installed Microsoft Data Explorer for Excel 2010 successfully last week and I can still use TFS 2010 to export work items to Excel without any issue. Today after I installed Microsoft PowerPivot for Excel 2010, the interesting part is that I could not export TFS work items to Excel.  I got the following error:

The document cannot be opened because there is a problem with the installation of the Microsoft Visual Studio v10.0 Team foundation Office integration components…….

(My Environment: Windows 7 X64 SP1 + Visual Studio 2010 SP1 + Office 2010 SP1)

That being said, Microsoft PowerPivot for Excel 2010 breaks the feature of TFS Open Selection in Microsoft Excel.

After looking into this issue, I tried the following command line and it was still not working.

Regsvr32 "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\TFSOfficeAdd-in.dll"

I tried another one and it is working as expected.

Regsvr32 "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\TFSOfficeAdd-in.dll"

If Microsoft PowerPivot for Excel 2010 breaks the feature of TFS Open Selection in Microsoft Excel, you can try the way above. It should work for you.

Burn Down Chart: Let’s Subscribe!

My manager just gave me the permission to update the Project Dashboard where team members can see the Burn Down Chart, Bug Stats, Build Status, recent check-ins and work items in TFS for every Sprint.

In previous Sprints, project managers sent the Project Dashboard link to team members and reminded them of updating the hours in their own tasks every morning.  It was good, but manual.

After I got the permission, I have one thing in my mind: why not subscribe to the Burn down Chart? Team members can open their emails and take a look at it directly every morning after they come to the office.

It is easy to subscribe as follows:

Open Visual Studio IDE and connect to TFS.

In the Reports, right click and “Show Report Site” appears. Click it and it goes to the Report site.

Click Dashboard and you can see many burn down chart reports for different projects.

Choose your report and click “Subscribe”

Enter Email Addresses, Subjects and enter 8:00 AM weekday inside Select Schedule button.

Every morning at 8:00 AM, the Burn Down Chart will be sent to all related people.

You can also add a new subscription to remind people of updating their hours in TFS before they leave in the evening. 

SQL Server : Database Schema Comparison Tool for Different Environments

My colleague asked me: how do you validate the discrepancy of database Schemas in different environments (DEV, TEST, STAGE, and PROD) in SQL Server?

The reason why she asked me is that from our previous experience of pushing the code from DEV/TEST to STAGE, the features in our Silverlight Application will not work well.

Why are the features broken often?

·        The code is based on the Entity Framework. If table structure changes, after pushing code to STAGE, but forget to update the table structure (add/delete columns), the features will not work well.

·        Silverlight Application is using ArcGIS Rest Services. If the changes are in GIS level, but not applied in STAGE, the feature will not work well.

·         DB change will cause the missing primary keys in some tables often.

I talked to her: If you have Visual Studio Ultimate installed, it is easy to compare the discrepancy in different environments such as missing columns in some tables, missing Stored Procedures and Views.

It is easy for her to see the discrepancy of database Schemas in different environments. I don’t have to bother her so much in the future.

Fixed the issue- You have been logged on with Temp Profile on Windows Server 2008 R2

This week when I logged off one remote machine (Windows Server 2008 R2 SP1) and logged in again, my configuration files in this machine disappeared all the time. Therefore, I needed to configure again. The notification message mentioned:  

You have been logged on with Temp Profile.

After looking into this issue, I saw the following user profiles and I didn’t see my profile (ralin).

C:\Users\TEMP.domainName.000 (first time to login)

C:\Users\TEMP.domainName.001 (second time login)

C:\Users\TEMP.domainName.002 (third time to login)

That explained why I always logged in via Temp Profile.

How to solve this issue?  After looking into it, it is easy.

Go to HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList

Search my username (ralin).

Delete the folder S1-5-21-706…

Log off and login again.

My user profile (C:\Users\ralin) appears finally.

MSTR Web Test Automation- one possible way to fix the issue of Dynamic Object ID

On Feb 19, I wrote a post about MSTR web test automation ( Use Telerik Test Studio to do MicroStrategy Web Test Automation successfully ). After that, one interesting challenge comes to me all the time.

I have some tests that need to click the Week/Month/Quarter Buttons in MSTR dashboard. But the tests always fail. After looking into the issue via Telerik Test Studio, I notice that the following yellow part (ID) is changed all the time. If the ID is dynamic, how can the test pass? The automation never clicks those buttons. That can explain why.

    public ArtOfTest.WebAii.Controls.HtmlControls.HtmlInputButton WeekButton

            {

                get

                {

                    return GetHtmlInputButton>("id=mstr262", "|", "tagIndex=input:0");

                }

            }

Is it possible for developers to make ID static for those Buttons? After talking to our developers, they say this is the Object ID and they can’t control it because they are still in development and is changed very often on MSTR side.

After several attempts, here is my solution:

Chrome has a good feature called “Inspect Element” if I do right click on the Week Button. Before clicking the Week button, inspect element features shows “mstr262”. After clicking the Week button, it shows “mstr265” or “mstr268”, or “mstr269”. ID is really dynamic.

Does Telerik support dynamic ID? The answer is using “~” as follows. “~” means contains. What happens if I try ~mstr26? 

    public ArtOfTest.WebAii.Controls.HtmlControls.HtmlInputButton WeekButton

            {

                get

                {

                    return GetHtmlInputButton>("id=~mstr26", "|", "tagIndex=input:0");

                }

            }

After I use the code above and rerun the test, the test really clicks the Week Button. The test passes.

Fixed the issue: Content from the Web site listed below is being blocked by the Internet Explorer Enhanced Security Configuration

Today I created a web test and ran it successfully in my local machine. I implemented it in TFS build, and after queuing the TFS build, the test failed.

Therefore, I logged in to the build machine (Windows Server 2008 R2 SP1) and hit the URL. The following dialog box appeared:

Content from the Web site listed below is being blocked by the Internet Explorer Enhanced Security Configuration.

That explains why the test failed in TFS build.

I put this site in my trusted site and the dialog box still showed up.

After looking into this issue, I need to do the following things:

Click Server Manager

Click Configure IE ESC

In the Administrators section, click off

After that, the test passes in TFS build.