Pages

Evernote 表格內容如何加密

Sep 10, 2017
當內容中有表格或水平水格線...等,「加密選取的文字」是不能選取的。
Image

不過卻可以複製貼上的方式,將 表格 偷渡進入加密文字中。
步驟 1
先選取幾行純文字內容做「加密選取的文字」
步驟 2
「顯示加密文字」後,在原本加密的內容中貼上表格
Image
Read more ...

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 ...