股票市票【估及買】的因由

哈哈，股市就是由一班自以為理性的神經病人及另一班完全沒理性神經病人組成。

舊版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