Suppress Power Query's Native Database Query Warning at Excel

Go to "Query Options"


 Uncheck "Require user approval for new database queries" at Security and OK to save

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