舊版Excel (2003 .XLS) 升級到新版(2007 .XLSX) - 批次

公司改用office 2013也有日子，停用舊版Excel(.xls)的呼聲都愈來愈大，日常使用的檔案同事都自己轉換了，餘下一大批封塵的檔案等著一次性更新。
一開始使用Nopi轉，但發現有些檔案的Style (底線, 顏色), Pivot都出現問題，到後期更出現檔案損壞。到最後原來還是使用Excel自己轉換最安全。 以下是使用Macro 在指定資料來內把.xls檔案轉換成.xlsx.

Sub ConvertToXlsx()
    Dim strPath As String
    Dim strFile As String
    Dim wbk As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
    strPath = "D:\Current Folder\" '目標位置
    strFile = Dir(strPath & "*.xlsx")
    Do While strFile <> ""
    On Error GoTo ErrorHandler
        If Right(strFile, 4) = "xlsx" Then
            Set wbk = Workbooks.Open(Filename:=strPath & strFile)
            wbk.SaveAs Filename:="D:\New Folder\" & strFile, FileFormat:=xlHtml

ErrorHandler:
            wbk.Close SaveChanges:=False
            Kill strPath & strFile
        End If
        strFile = Dir
    Loop
MsgBox "Done!!"

End Sub

SQL Tigger 如何區分Insert, Update, Delete

ALTER TRIGGER [dbo].[TRI_TableName]

     ON  [dbo].[TableName]

     AFTER INSERT,DELETE,UPDATE

AS

BEGIN

 SET NOCOUNT ON;

 DECLARE @action as char(1);

 SET @action = 'I'; -- Set Action to Insert by default.

 IF EXISTS(SELECT * FROM DELETED)

    BEGIN

        SET @action =

            CASE

                WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.

                ELSE 'D' -- Set Action to Deleted.      

            END

    END

if @action = 'I'

     begin

         -- 新增 Insert

     end

if @action = 'U'

     begin

         -- 修改 Update

     end

if @action = 'D'

     begin

         -- 刪除 Delete

     end

END

【土法煉鋼】為所有資料表增加Trigger ，記錄(Insert, Update, Delete)

在清理evernote時看到以前的DIY還是忍不住笑了出來。
這stored procedure 出世的原因是當初接手一個開發中及由兩代人編寫的項目，就想出這土法來追查bug。
Tigger保存的格式是XML，也不是很好用，一直都是在別無選擇下使用。
到了現在都改用CDC了。

CREATE TABLE dbo.WormsRows

     (

     dt datetime NULL,

     host varchar(50) NULL,

     SPID int NULL,

     tbl nvarchar(50) NULL,

     mode nchar(10) NULL,

     xml xml NULL

     )  ON [PRIMARY]

      TEXTIMAGE_ON [PRIMARY]

GO

DECLARE @sql varchar (8000), @TABLE_NAME sysname

SELECT @TABLE_NAME = MIN(name) FROM sys .tables

WHILE @TABLE_NAME IS NOT NULL

     BEGIN

      SELECT @sql = 'CREATE TRIGGER [TR_' + @TABLE_NAME + '_CUD] ON [' + @TABLE_NAME +'] '

       + 'FOR INSERT, UPDATE, DELETE AS '

       + 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host,SPID,  tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''In'', (select * from inserted for XML auto, root) ) '

       + 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host, tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''Up'', (select * from (select ''inserted'' as act, * from inserted union select ''deleted'' as act, * from deleted )a for xml auto, root) ) '

       + 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '

       + 'INSERT INTO WormsRows (dt, host, tbl, mode, xml) VALUES     (GETDATE(), HOST_NAME(), ''' + @TABLE_NAME + ''' ,''Del'', (select * from deleted for XML auto, root) ) '

     SELECT @sql

     begin try

         EXEC(@sql )      

     end try

     begin catch

         print '產生Trigger 失敗:' +  @TABLE_NAME

     end catch

     --下一個資料表

     SELECT @TABLE_NAME = MIN (name) FROM sys.tables where name > @TABLE_NAME

END

匯出outlook 某資料庫來所有的附件

Public Sub SaveAttachments()
        Dim SaveToPath As String
        Dim dateFormat
        Dim Str As String
        Str = Right(CStr(10000 + CInt(9000 * Rnd)), 4) + "-"

        SaveToPath = "D:\Email_att\"  '附件儲存位置 

        '由目前選訂的資料夾
        myfolder = Application.ActiveExplorer.CurrentFolder

        For Each myitem In myfolder.Items
            If myitem.Attachments.Count > 0 Then If Dir(SaveToPath, vbDirectory) = "" Then MkDir SaveToPath

            For Each myattachment In myitem.Attachments
                Dim MyFile As String
                  
                MyFile = Dir(SaveToPath & Str & myattachment.FileName)
                If MyFile <> "" Then Str = Right(CStr(10000 + CInt(9000 * Rnd)), 4) + "-"
                MyFile = Dir(SaveToPath & Str & myattachment.FileName)
                If MyFile <> "" Then Str = Right(CStr(10000 + CInt(9000 * Rnd)), 4) + "-"


                myattachment.SaveAsFile SaveToPath & Str & myattachment.FileName

            Next
        Next

        MsgBox("完成")
    End Sub

SQL - for loop 迴圈

for loop

都還算實用

-- for loop 100 次

DECLARE @i INT = 0

WHILE (@i< 100)

     BEGIN                

         -- 工作內容

         insert #tmp select @i        

         if @i >= 50 

              begin

                  BREAK -- 離開迴圈

              end        

         Set @i=@i+1        

     END

t-sql 備份所有資料庫及壓縮檔案

SQL資料庫備份後仍有很大的壓縮空間(一般都有80%以上)，我習慣把備份檔案再上傳到dropbox以免出現黑天鵝。

begin

--  檔案儲存位置

DECLARE @Str_File_Path NVARCHAR( 256) = 'C:\Dropbox\oldboy\' 

DECLARE @db_name VARCHAR( 50) 

DECLARE @fileName VARCHAR( 256) 

DECLARE @Command VARCHAR( 256) 

--  使用cursor 所有資料庫

DECLARE db_cursor CURSOR FOR

SELECT name FROM master .dbo. sysdatabases

WHERE name NOT IN ( 'master','model' ,'msdb', 'tempdb')  -- 系統資料庫除外

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @db_name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

          

           SET @fileName = @Str_File_Path + @db_name

           SET @Command = @Str_File_Path + '7z a -t7z '  + @fileName + '.7z ' + @fileName + '.bak'

           SET @fileName = @Str_File_Path + @db_name   + '.bak'          

           BACKUP DATABASE @db_name TO DISK = @fileName WITH INIT 

           exec xp_cmdshell @Command

           SET @Command = 'del ' +   @Str_File_Path + @db_name   + '.bak'

           exec xp_cmdshell @Command

          

           FETCH NEXT FROM db_cursor INTO @db_name 

END 

CLOSE db_cursor  

DEALLOCATE db_cursor

end

取得 SQL Server 資料庫正在執行的 T-SQL 指令與詳細資訊

這時我們可以利用 SQL Server 2005 以上都有支援的幾個 DMVs (Dynamic Management Views) 來查詢 SQL Server 資料庫中即時的運作資訊，當然也包括正在執行的完整 T-SQL 命令句，技術細節我就留在本文最後的相關連結讓各位自行研究，以下是我的研究成果：

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
    ltrim(q.text),
    r.statement_start_offset/2+1,
    (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
   LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

相信是非常實用的一句 T-SQL，全中文的喔！ ^__^   

令人停止呼吸的一幕 【Interstellar Docking】

"It's not possible!"
"No... it's necessary." 

G.E.M - 奇蹟