I love stackoverflow.com

I needed a query to use Rank() row counters within a Group

Here it is:  http://stackoverflow.com/questions/1139719/sql-server-query-for-rank-rownumber-and-groupings

Here is my query and resultset

SELECT
    Rank() over (Partition by EncounterID Order by EncounterID, RefPhysID)+1 as ranks
    ,EncounterID
    , RefPhysID
FROM 
    tbEncountersCourtesyCopies
Group By
    EncounterID, RefPhysID, RefPhysOfficeID 
Order by
    ranks asc

ranks    EncounterID    RefPhysID
2        9              1022
2        12             1095
2        18             91
3        12             1279

Windows 7 Activation Problems.

I've been struggling with Windows 7 license issues.  After hard drive changes (not the boot drive), one of my test machines keeps have the Windows 7 Pro license invalidated.  This is odd since I thought I had a real license key for the machine and not one of those tech-net, it expires in a year licenses but I'm not really sure anymore.

My solution was to purchase a discounted license from a non-Microsoft website whose claimed to be a Genuine MS Partner.  Initially the key I received did not work but I used the following steps and I was able to activate Windows 7 Pro using automated phone registry.

From http://social.microsoft.com/Forums/en-US/genuinewindows7/thread/dd8f477a-07a7-42b5-b5bb-5079a5fd8140

First off, try these alternate steps to change the product key

1)    Click the Start button

2)    Type: slui.exe 3 and hit the Enter key

3)    Type in the Product key from the sticker on your computer

4)    Click the Next button.

5)    You will be asked if want to Activate, click ok

6)    It will attempt to Activate by the internet and will return an Invalid Key error (this is ok, continue to step 7)

7)    Click the Start button

8)    Type: slui.exe 4 and hit the Enter key

9)    Select your location in the drop down menu and click the Next button

10) The next screen provides the number to call to Activate by Phone

If that doesn't work, try these steps that will recreate Windows 7's Licensing Store.  When it recreates the file, Windows will need to repopulate it with Licensing Information so will ask you to enter the Product Key and to Activate (use the Activate by Phone method)

1) Open an Internet Browser

2) Type %windir%\system32 into the browser address bar.

3) Find the file CMD.exe

4) Right-Click on CMD.exe and select 'Run as Administrator'

5) Type: net stop sppsvc  (it may ask you if you are sure, select yes)

6) Type: cd %windir%\ServiceProfiles\NetworkService\AppData\Roaming\Microsoft\SoftwareProtectionPlatform

7) Type: rename tokens.dat tokens.bar

8) Type: slui

9) After a couple of seconds Windows Activation dialog will appear. You may be asked to re-activate and/or re-enter your product key.

Lastly, if neither set of steps resolves the problem, download and run the Genuine Diagnostics tool (MGADiag.exe) at this linkhttp://go.microsoft.com/fwlink/?linkid=52012. Click "Continue", click the "Copy" button then “Paste” the report into a reply message in this thread.

How to take 1 record from an INNER JOIN

Hit a problem moving data between systems where there is legitimate duplicate mappings between the two systems but I need just one.  and It really does not matter which one.  basically I want to take SELECT TOP 1 on records coming off a join:
I should use this approach more but it can be slower under some conditions

SELECT
        A.RetailerID,
        X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID 

ORDER BY B.RetailerIDTheirs ASC) AS X

Interesting SQL Arithmetic overflow error problem

Hit an interesting SQL problem:
There were a number of int and numeric fields being converted to varchar() fields migrating data from System2 to System1.  I eliminated the obvious fields and hadn't solved it.  There were a number of columns with NULL values - one in particular: StandardTurnAroundTime

Cause:

 INSERT INTO SYSTEM1.dbo.W_EXAM (Exam_Duration_Tm)

SELECT StandardTurnAroundTime

FROM SYSTEM2.[dbo]. tbProcedures

Exam_Duration_Tm – varchar(4)

StandardTurnAroundTime –numeric(5.2) – max value 999.99 would cause Arithmetic overflow error

Turns out NULL value as tbProcedures. StandardTurnAroundTime was causing the issue.

Fix:

       CONVERT( varchar(4),CONVERT(int,ISNULL(StandardTurnAroundTime,0)))

INSERT INTO  SYSTEM1 .dbo.W_EXAM (Exam_Duration_Tm)

SELECT CONVERT( varchar(4),CONVERT(int,ISNULL(StandardTurnAroundTime,0)))

FROM  SYSTEM2 .[dbo]. tbProcedures

Returns values that will fit varchar(4) field even for values at the max of numeric(5,2) 999.99.

I was careful to test maximum values for numeric(5.2) to be converted to varchar(4).

How many times this would have been caught by manual code review?