Create Link Server for MySQL at MSSQL

1. Download and install “Visual C++ Redistributable for Visual Studio 2015”
   https://www.microsoft.com/en-us/download/details.aspx?id=48145
2. Download and install “Connector/ODBC”
   https://dev.mysql.com/downloads/connector/odbc/
3. After install finished, MySQL ODBC Driver will list at "ODBC Data Source Administrator"
   
4. Setup "System DNS" at "ODBC Data Source Administrator".
   Click "Add" button.
   
   Select "MySQL ODBC 8.0 ANSI Driver
   
   Fill in connection info
   
5.  Create link server at Microsoft SQL Management Studio
   
   • General
     Provider: Microsoft OLE DB Provider for ODBC
     Product: MySQL
     Data source: same as "System DNS"
   • Security
     Be made using the security context
6. Test script
   
   • SELECT * from OPENQUERY (Linked_Server, 'select * from test.table'); 
   • INSERT OPENQUERY (Linked_Server, 'select name,address from test.table') VALUES ('Jason','Malaysia'); 
   • UPDATE OPENQUERY (Linked_Server, 'select name from test.table WHERE user_id = 0001') SET name = 'Tom'; 
   • DELETE OPENQUERY (Linked_Server, 'select name from test.table WHERE user_id = 0002');

Convert GPS Location from DMS to DD via Excel

Sample for DMS
#Longitude: 99°51'05.2"E
#Latitude: 6°19'19.3"N

Sample for DD
#Longitude: 99.8514444444444
#Latitude: 6.32202777777778

Excel formulas
#Longitude (B2)
=(MID(B2,1,FIND("°",B2)-1)+(MID(B2,FIND("°",B2)+1,FIND("'",B2)-FIND("°",B2)-1)/60)+(MID(B2,FIND("'",B2)+1,FIND("""",B2)-FIND("'",B2)-1)/3600))*IF(OR(MID(B2,FIND("""",B2)+1,1)="E",MID(B2,FIND("""",B2)+1,1)="N"),1,-1)

#Latitude (D2)
=(MID(D2,1,FIND("°",D2)-1)+(MID(D2,FIND("°",D2)+1,FIND("'",D2)-FIND("°",D2)-1)/60)+(MID(D2,FIND("'",D2)+1,FIND("""",D2)-FIND("'",D2)-1)/3600))*IF(OR(MID(D2,FIND("""",D2)+1,1)="E",MID(D2,FIND("""",D2)+1,1)="N"),1,-1)

How to delete OEM partition with Diskpart

Step 1. Hit "Windows Key + R to open the run dialogue box, enter "diskpart" and click "OK" to open a black command prompt window.

Step 2. Type "list disk" to display all the disks of your computer. Type "select disk n" to identify which disk you need to work with. Here n stands for the disk letter.

Step 3. Type "list partition" to display all the volumes on the hard drive. Type "select partition n" to identify which partition you want to remove. Here n stands for the volume letter.

Step 4. Type "delete partition override" to remove the OEM partition. Finally, type "exit" to close the windows when you receive the message says, DiskPart successfully deleted the selected partition".

在cmd建立鏡像路徑 (subst)

subst w: "C:\Documents and Settings"
建立後即可用 w: 來取代 C:\Documents and Settings\

C:\Documents and Settings\xxx → w:\Documents and Settings\xxx

How to select top 1 record per group

抓取客戶最後一次購買此產品的日期


SELECT   product, cus, date
FROM      (
                 SELECT   product,
                                   cus,
                                   date,
                                   row_number() over (partition by product, cus order by date desc) as ranker
                 FROM      SalesTable
                 ) z
WHERE   ranker = 1

Schedule Auto Restart Server

C:\Windows\System32\shutdown.exe /r

Get mail account from AD at MSSQL

1. Create the linked server
EXEC sp_addlinkedserver @server = 'ADSI',
                                           @srvproduct = 'Active Directory Services 2.5',
                                           @provider = 'ADSDSOObject',
                                           @datasrc = 'adsdatasource'

2. Security

3. SQL Function Script
ALTER FUNCTION [dbo].[GetEmailFromAD]
(
     -- Add the parameters for the function here
     @pUserID varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
     -- Declare the return variable here
     DECLARE @pMail varchar(250)
     -- Add the T-SQL statements to compute the return value here
     SELECT @pMail = mail
     FROM OpenQuery (ADSI, '
                                      SELECT displayName,
                                      mail,
                                      sAMAccountName
                                      FROM ''LDAP://...:389/DC=...,DC=...,DC=...''
                                      WHERE objectCategory = ''User''
                                      ')
     WHERE sAMAccountName = RIGHT(@pUserID,LEN(@pUserID)-CHARINDEX('\',@pUserID))
     -- Return the result of the function
     RETURN @pMail
END