Pages

Firewall open Protocol 47 GRE

Nov 10, 2016
在安裝 VPN 時最後需要在防火牆設定:
For PPTP: 1723 TCP and Protocol 47 GRE (also known as PPTP Pass-through)
1723 TCP 是指在防火牆上開啟一個  1723 PORT,是很常見的設定方式。
而要如何設定 Protocol 47 GRE ??
以下就是設定的步驟,主要是前兩個動作與開放一個 PORT做法不一樣

image

image

image
以下就是一連串的 NEXT 直到最後一部輸入一個名稱即可。
image

image

image

image
Read more ...

T-SQL 一次查詢庫存天數分析

Oct 25, 2016

image

前提

ERP 系統中在財務方面有帳齡分析;在庫存方面有庫齡分析,這是要分析目前在庫產品的存放天數。

這裡有個簡化的範例資料,分別有一個庫存主檔與入庫明細檔,

  • 庫存主檔有三個產品,經銷售後目前庫存量分別為 8, 24, 36
  • 每項產品每個月均採購 10 個

image

最後要推算出

各項產品庫存分別在那月份採購 與 平均在庫天數

image

範例資料可以點擊後取得: Create T-SQL script


實作步驟

實作過程使用到 SQL Server 提供 T-SQL (較特別)的方法,包括:


1.查出各產品最近一次的入庫單

image

2. 以 CTE 往前一次交易推算

image

3  合計平均庫存天數 = 總庫存天數 / 庫存量

image

4 PIVOT 列出分布在各月份入庫數

完整 T-SQL script 如下

Read more ...

Oppo R9 Turn on/off developer mode

Jul 30, 2016

First time turn on developer mode

Go to Setting > About phone, type [Build number] many times, when system show:

No need to proceed this option, you are already a developer.

image


Turn off / on developer mode

Go to Settings > Additional settings > Developer options

Screenshot_2016-07-30-17-11-14-56


image


then turn on (1) Developer options and (2) USB debugging

Screenshot_2016-07-30-17-11-58-47

Read more ...

Code Snippet for SSMS

Jul 22, 2016

在 SQL Server Management Studio (SSMS) 中也可以像 Visual Studio 使用程式碼片段,並可以自訂屬於個人或團隊的程式碼片段。


編輯中加入 Code snippet

在 SSMS Script 編輯區中,按下滑鼠右鍵,出現如下

image

點擊 [Insert Snippet] 或 [Surround With] 會出現搜尋工具,可以滑鼠挑選或輸入關鍵字進行搜尋

image

選擇 Code Snippet 後會在原本編輯區中加入以下的片段

image

這畫面上的變數會有醒目的底色,修改後按下 [Tab] 會移至下一個變數,
全部修改後按下 [Enter] 後原本醒目的底色消失,回到先前編輯得狀態。


Code snippet editor - Snippet Designer

每一個 Code snippet 都是一個附檔名為 .snippet 的 XML 檔案,建議透過 Visual Studio Extension 來編輯。

從 Visual Studio 選單 > [Tools]  > 點擊 [Extensions and Updates],如下圖順序操作,在搜尋框輸入 : Snippet Designer

image


安裝後 Visual Studio 重新啟動,新增檔案時,可以看到  Snippet Designer  下有 Code Snippet 的範本。

image

新增 Code Snippet 檔案前,必須在 Solution 或 專案下才能新增或開啟既有的 *.snippet 檔案。 


編輯主畫面上方是(類似)程式碼,下方是宣告變數

image


在屬性視窗中 [Snippet Type] 會決定這個 Code Snippet 歸類在 [Insert Snippet] 或 [Surround With]

image

選擇 Expansion 會歸類在 [Insert Snippet] ,選擇 SurroundsWith 會歸類在 [Surround With] 。


建議可以參考或複製修改 SSMS 預設的 Code Snippet ,預設儲存的位置在

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets


自訂 Code snippet

自行修改完成的 Code snippet 儲存於同一個目錄下,
從 SSMS 選單 [Tools] > 點擊 [Code snippets Manager…]

image

點擊 [Add…] ,選擇儲存 Code snippet 檔案的目錄即可。

image


調整 SSMS 顏色

先前有提到 Customize SSMS Setting,若想調整從 code snippet 新加入 Script 中變數的顏色,修改以下兩個項目:

  • Code Snippet Field
  • Code Snippet Field (Selected)

image


筆者選擇 Code Snippet Field 選擇灰色 Gray,Code Snippet Field (Selected) 選擇綠色 Green,效果如下圖

image


參考資源

Getting started with Code Snippets feature of SQL Server 2012 ( article )

Visual Studio Extension - Snippet Designer

Read more ...

SQL Server Release Memory

Feb 24, 2016
In my case, when server run my data transfer task, the server memory will get hight. I need to restart service.
Another way in discuss : [SQL Server Bulk Insert Physical Memory Issue]
I write it to a stored procedure, like bellow:
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SQL_Server_Release_Memory]    Script Date: 2/24/2016 12:42:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------- 
description: SQL Server Release Memory
author: Robin
date: 2016/02/24
testing code: 
-----------------------------------------------------
EXEC SQL_Server_Release_Memory
-----------------------------------------------------*/
CREATE PROCEDURE [dbo].[SQL_Server_Release_Memory]

AS
BEGIN
/*Query physical memory in use*/
SELECT [description], value_in_use
 , (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';


DECLARE @MaxMemorySize int;

SELECT @MaxMemorySize = convert(int, value)
FROM sys.configurations
WHERE name ='max server memory (MB)';


/*1.Set Maximum server memory to 1.5GB */
DECLARE @TempMemorySize int
SET @TempMemorySize = 1024 * 1.5;

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'max server memory (MB)', @TempMemorySize RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;

DECLARE @WaitAndTry int = 0
WHILE (@WaitAndTry < 5)
BEGIN
 /*Check has already released ? */
 WAITFOR DELAY '00:00:10'; 

 IF EXISTS(
  SELECT (physical_memory_in_use_kb/1024)
  FROM sys.dm_os_process_memory
  WHERE (physical_memory_in_use_kb/1024)<2000
 )
 BEGIN
  /*2.Set Maximum server memory to original setting */
  EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max server memory (MB)', @MaxMemorySize RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;
  
  SET @WaitAndTry = 10;

  PRINT N'Finish.';
 END
 ELSE
 BEGIN
  SET @WaitAndTry = @WaitAndTry + 1;
  IF @WaitAndTry = 5
  BEGIN
   PRINT N'Fail: have to check and setting [Maximum server memory].';
  END
 END

END

/*Query physical memory in use*/
SELECT [description], value_in_use
 , (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';

/*
reference 
http://serverfault.com/questions/251832/sql-server-bulk-insert-physical-memory-issue
http://serverfault.com/questions/408919/how-do-you-get-the-minimum-and-maximum-memory-allocation-of-a-sql-instance-using
*/
END
When your server memory get hight,  run this stored procedure will release SQL Server memory .
Read more ...

Database Copy Paste

Feb 18, 2016

This  T-SQL script is like copy A database paste to another database B in SQL Server .

My SQL Server version is 2014, haven’t run another version.

/*declare*/
DECLARE @path varchar(100)='E:\SQL_DATA\BACKUP\';
DECLARE @backfile varchar(100)=@path+'SourceDB.baktmp';

/*backup*/
BACKUP DATABASE [SourceDB] TO  DISK = @backfile WITH NOFORMAT
, NOINIT
,  NAME = N'SourceDB-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
;

/*import*/
ALTER DATABASE [TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TargetDB] FROM  DISK = @backfile WITH  FILE = 1
,  MOVE N'TargetDB' TO N'E:\SQL_DATA\TargetDB.mdf'
,  MOVE N'TargetDB_log' TO N'E:\SQL_DATA\TargetDB_log.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [TargetDB] SET MULTI_USER
;

/*delete backup file*/
DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day, +1, GETDATE()), 111);
EXECUTE master.dbo.xp_delete_file 0,@path,N'baktmp',@deldate;

You need modify these :

  1. Modify @path
  2. Replace “SourceDB” to your source database name
  3. Replace “TargetDB” to your target database name

Maybe change  to a stored procedure will be easier.

Create following script in master db.

image 

USE [master]
GO
/*--------------------------------------------------
description: Copy database A to database B
author: Robin
date: 2016/02/20
testing Code:
--------------------------------------------------
EXEC Database_Copy_Paste 'DATABASE_A','DATABASE_B'
--------------------------------------------------*/
ALTER Procedure [dbo].[Database_Copy_Paste]
	@SourceDB nvarchar(32)
	, @TargetDB nvarchar(32)
AS
BEGIN
/*set login files name*/
DECLARE @DBRowsFile varchar(32), @DBLogFile varchar(32);
SET @DBRowsFile='DefaultDBRows';
SET @DBLogFile='DefaultDBLog';

/*check logic file are the same*/
DECLARE @SQLString nvarchar(1024), @ParmDefinition nvarchar(512);
SET @SQLString=
'IF exists(
	select top 1 name from (
		select ''[@SourceDB]'' as db, name from [@SourceDB].sys.database_files
		union all
		select ''[@TargetDB]'', name from [@TargetDB].sys.database_files
	) t
	where t.name not in (
		select @DBRowsFile as name
		union
		select @DBLogFile
	))
BEGIN
	select ''ALTER DATABASE ''+t.db+'' MODIFY FILE (NAME=''+t.name+'', NEWNAME=''+s.name+'');'' as [Have to excute]
	from (
		select ''[@SourceDB]'' as db, name, type_desc from [@SourceDB].sys.database_files
		union
		select ''[@TargetDB]'', name, type_desc from [@TargetDB].sys.database_files
	)t
	left outer join (
		select @DBRowsFile as name, ''ROWS'' as type_desc
		union
		select @DBLogFile, ''LOG''
	) s on t.type_desc=s.type_desc
	where t.name<>s.name
END';
SET @SQLString = REPLACE(@SQLString, '[@SourceDB]', @SourceDB);
SET @SQLString = REPLACE(@SQLString, '[@TargetDB]', @TargetDB);
SET @ParmDefinition='@DBRowsFile varchar(32), @DBLogFile varchar(32)';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @DBRowsFile=@DBRowsFile, @DBLogFile=@DBLogFile;

IF @@ROWCOUNT>0
BEGIN
	PRINT 'Have to change logic file name first...';
	RETURN;
END;


/*declare database parms */
DECLARE @BackupPath nvarchar(128), @BackupFile nvarchar(128);
SET @BackupPath = 'E:\SQL_DATA\BACKUP\';
SET @BackupFile = @BackupPath+@SourceDB+'.baktmp';

SET @SQLString=
'/*backup*/
BACKUP DATABASE [@SourceDB] TO DISK = @BackupFile WITH NOFORMAT
, NOINIT
,  NAME = N''[@SourceDB]-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10 ;

/*import*/
DECLARE @TargetDBFile nvarchar(128), @TargetDBLogFile nvarchar(128);
select @TargetDBFile = physical_name    from [@TargetDB].sys.database_files where type_desc=''ROWS'';
select @TargetDBLogFile = physical_name from [@TargetDB].sys.database_files where type_desc=''LOG'';

ALTER DATABASE [@TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [@TargetDB] FROM  DISK = @BackupFile WITH  FILE = 1
,  MOVE @DBRowsFile TO @TargetDBFile
,  MOVE @DBLogFile TO @TargetDBLogFile
,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [@TargetDB] SET MULTI_USER ;

/*delete backup file*/
DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day, +1, GETDATE()), 111);
EXECUTE master.dbo.xp_delete_file 0,@BackupPath,N''baktmp'',@deldate ;
';

SET @SQLString = REPLACE(@SQLString, '[@SourceDB]', @SourceDB);
SET @SQLString = REPLACE(@SQLString, '[@TargetDB]', @TargetDB);

SET @ParmDefinition=
'@SourceDB nvarchar(32), @TargetDB nvarchar(32)
, @BackupPath nvarchar(128), @BackupFile nvarchar(128)
, @DBRowsFile varchar(32), @DBLogFile varchar(32)';

--print @ParmDefinition
--print @SQLString

EXECUTE sp_executesql @SQLString, @ParmDefinition
	, @SourceDB=@SourceDB
	, @TargetDB=@TargetDB
	, @BackupPath=@BackupPath
	, @BackupFile=@BackupFile
	, @DBRowsFile=@DBRowsFile
	, @DBLogFile=@DBLogFile;

END
GO

Then call stored procedure:

USE [master]
EXEC Database_Copy_Paste 'DATABASE_A','DATABASE_B'

^_^

Read more ...

Website authentication flow example

Dec 24, 2015

mailchimp.com 為例,來看一個網站從使用者註冊、啟動、登入、忘記密碼/帳號等這些常見的功能,還有哪些細節可以注意。

以下這些功能幾乎每個網站都有做,但很少做到讓人操作起來這麼流暢的!!


帳號申請

SNAGHTML8e8b84

  1. 若不需要申請,則可以回到登入頁
  2. 當滑鼠停佇後,才顯示輸入格式、規則的提示
  3. 可以顯示密碼
  4. 輸入密碼通過規則後,顏色會變淡
  5. 符合表單規則後,才可以點擊


欲申請的使用者名稱已被搶走囉

SNAGHTML8f27a4

很逗趣的英文提示。


申請完成

SNAGHTML71bb3c

提示會收到 Email 且需要啟動


帳號啟動

寄送帳號啟動的 email

SNAGHTML706fb1

Email 中提示帳號與一顆很明顯的連結按鈕。


點擊啟動連結進入的畫面

image


辨識是否是機器人

image


辨識完成後

image


寄送 Welcome email

image


登入主頁面

SNAGHTML8fdbe1

  1. 忘記帳號
  2. 忘記密碼
  3. 顯示密碼
  4. 記住登入狀態
  5. 登入
  6. 申請新帳號
  7. 無法登入時的線上說明頁
  8. 進入升級說明頁


帳號輸入錯誤

image


密碼輸入錯誤

SNAGHTML77fdd5


第一次進入系統

其他需要填寫資訊還蠻多的,還好沒放在申請頁面上,應該會嚇跑大部份的人。

SNAGHTML7abe4d



登出頁面 See ya later

image

網址很特別 See ya later (http://mailchimp.com/see-ya-later/)


忘記密碼

SNAGHTML7ed1f1


輸入不存在的帳號

image


驗證帳號存在

SNAGHTML801e95

會提示將 Email 寄送到哪個帳號,Email 帳號部分將英數字以 * 取代,若是本人,應該可以看得出來寄送到哪個 Email 信箱。


寄送 email 重新設定密碼

SNAGHTML837448

很明顯的帳號與重設密碼的連結按鈕。


點擊後完成的畫面

image


寄送 email 通知已重新設定新的密碼

SNAGHTML913602


回上一頁,會出現提示訊息

image



忘記帳號

image


輸入不存在的 Email

SNAGHTML851f58


很明確告知寄送到那個 email 信箱

SNAGHTML8586ad


寄送 email 通知相關資訊

SNAGHTML91bda1

Read more ...