2015年9月8日星期二

MTECH - Workflow Details

MTECH Workflow Details

SELECT DISTINCT WM.WORKFLOWMASKKEY, WM.WORKFLOWCODE, WM.WORKFLOWDESCRIPTION, WE.WORKFLOWEVENTKEY, WE.SEQUENCE, WE.EVENTKEY, E.EVENTDESCRIPTION, WE.DESCRIPTIONDISPLAY, -- WORKFLOW DETAIL
R.RULEKEY, -- RULE
TE.EVENTKEY, TE.EVENTDESCRIPTION, RT.NUMBEROFDAYS, -- TRIGGERING EVENT
CE.EVENTKEY, CE.EVENTDESCRIPTION,    -- CONDITIONEVENT
LE.EVENTKEY, LE.EVENTDESCRIPTION     -- LIMITEDEVENT
FROM WORKFLOWMASK WM
LEFT JOIN WORKFLOWEVENT WE ON WM.WORKFLOWMASKKEY = WE.WORKFLOWMASKKEY
LEFT JOIN TABLEEVENT E ON WE.EVENTKEY = E.EVENTKEY
LEFT JOIN TABLERULE R ON WE.EVENTKEY = R.EVENTKEY AND R.LAWKEY IS NULL
    LEFT JOIN TABLERULETRIGGER RT ON RT.RULEKEY = R.RULEKEY         -- TRIGGERING EVENT
    LEFT JOIN TABLEEVENT TE ON TE.EVENTKEY = RT.TRIGGEREVENTKEY
    LEFT JOIN TABLERULECONDITION RC ON RC.RULEKEY = R.RULEKEY       -- CONDITIONEVENT
    LEFT JOIN TABLEEVENT CE ON CE.EVENTKEY = RC.CONDITIONEVENTKEY
    LEFT JOIN TABLERULELIMIT RL ON RL.RULEKEY = R.RULEKEY           -- LIMITEDEVENT
    LEFT JOIN TABLEEVENT LE ON LE.EVENTKEY = RL.LIMITEVENTKEY
WHERE WM.WORKFLOWDESCRIPTION LIKE 'HK%'
ORDER BY WM.WORKFLOWCODE, WE.SEQUENCE

2015年9月2日星期三

oracle : the password has expired

http://www.dbas-oracle.com/2011/06/ora-28001-password-has-expired.html

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
1) -- CHECK PASSWORD_LIFE_TIME, SHOULD BE 180 BY DEFAULT
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

2) -- CHANGE IT TO UMLIMITED
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

3) -- GEN SCRIPT TO UPDATE PASSWORD AS THE CURRENT PASSWORD
select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;  

4) -- UNLOCK THE USER
alter user CPADEMO account unlock;

5) -- CHECK THE USER, IT SHOULD BE OPNE NOW.
select username,account_status from dba_users;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Solutions:
1.  Connect to database using sys users.
2. Execute the following query
Sql > select * from dba_profiles;
the output of this query will be like.
PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE   COMPOSITE_LIMIT           KERNEL   DEFAULT
DEFAULT              COMPOSITE_LIMIT           KERNEL   UNLIMITED
MONITORING_PROFILE   SESSIONS_PER_USER         KERNEL   DEFAULT
DEFAULT              SESSIONS_PER_USER         KERNEL   UNLIMITED
MONITORING_PROFILE   CPU_PER_SESSION           KERNEL   DEFAULT
DEFAULT              CPU_PER_SESSION           KERNEL   UNLIMITED
MONITORING_PROFILE   CPU_PER_CALL              KERNEL   DEFAULT
DEFAULT              CPU_PER_CALL              KERNEL   UNLIMITED
MONITORING_PROFILE   LOGICAL_READS_PER_SESSION KERNEL   DEFAULT
DEFAULT              LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
MONITORING_PROFILE   LOGICAL_READS_PER_CALL    KERNEL   DEFAULT

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
DEFAULT              LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
MONITORING_PROFILE   IDLE_TIME                 KERNEL   DEFAULT
DEFAULT              IDLE_TIME                 KERNEL   UNLIMITED
MONITORING_PROFILE   CONNECT_TIME              KERNEL   DEFAULT
DEFAULT              CONNECT_TIME              KERNEL   UNLIMITED
MONITORING_PROFILE   PRIVATE_SGA               KERNEL   DEFAULT
DEFAULT              PRIVATE_SGA               KERNEL   UNLIMITED
MONITORING_PROFILE   FAILED_LOGIN_ATTEMPTS     PASSWORD UNLIMITED
DEFAULT              FAILED_LOGIN_ATTEMPTS     PASSWORD 10
MONITORING_PROFILE   PASSWORD_LIFE_TIME        PASSWORD DEFAULT
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD 180

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- ---------------
MONITORING_PROFILE   PASSWORD_REUSE_TIME       PASSWORD DEFAULT
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
MONITORING_PROFILE   PASSWORD_REUSE_MAX        PASSWORD DEFAULT
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
MONITORING_PROFILE   PASSWORD_VERIFY_FUNCTION  PASSWORD DEFAULT
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
MONITORING_PROFILE   PASSWORD_LOCK_TIME        PASSWORD DEFAULT
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD 1
MONITORING_PROFILE   PASSWORD_GRACE_TIME       PASSWORD DEFAULT
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD 7

32 rows selected.
Here PASSWORD_LIFE_TIME field is responsible for expiring of password after 180 days.
3.  execute following command to disable this feature:
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now crosscheck for disabling of this feature.
Sql > select * from dba_profiles;
The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.

5. Now reset the password of locked user. Here are two options to reset password. Either you can keep same password or you can give a new password.

To Keep same Password:

SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;  
Enter value for username: scott
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('scott')

C
--------------------------------------------------------------------------------
alter user "SCOTT" identified by values 'F894844C34402B67';

SQL> alter user "SCOTT" identified by values 'F894844C34402B67';

To give a new Password:

sql> alter user [user_name] identified by [password];

6. Unlock user account using below command

sql> alter user [User_name] account unlock;

7. Crosscheck by value of accout_status field in dba_users view.

sql> select username,account_status from dba_users;

The value of account_status filed should by "OPEN" for corresponding user. 

2015年8月20日星期四

HLDev Script

select d.name, p.* from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'Inprous'


RESTORE DATABASE [InproAsia.lud] FROM  DISK = N'G:\DBDump\InproAsia_backup_2015_07_06_013003_6850601.bak' WITH  FILE = 1, 
MOVE N'InproHKG' TO N'F:\DBData\InproAsia.lud.mdf', 
MOVE N'InproHKG_log' TO N'E:\Trans\InproAsia.lud_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE DATABASE [InproAsia] FROM  DISK = N'G:\DBDump\InproAsia_backup_2015_07_06_013003_6850601.bak' WITH  FILE = 1, 
MOVE N'InproHKG' TO N'F:\DBData\InproAsia1.mdf', 
MOVE N'InproHKG_log' TO N'E:\Trans\InproAsia1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE DATABASE [InproEU.lud] FROM  DISK = N'G:\DBDump\InproEU_backup_2015_07_06_013003_6850601.bak' WITH  FILE = 1, 
MOVE N'INPRO' TO N'F:\DBData\InproEU.lud.mdf', 
MOVE N'INPRO_log' TO N'E:\Trans\InproEU.lud_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE DATABASE [InproEU] FROM  DISK = N'G:\DBDump\InproEU_backup_2015_07_06_013003_6850601.bak' WITH  FILE = 1, 
MOVE N'INPRO_log' TO N'E:\Trans\InproEU_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE DATABASE [InproUS.lud] FROM  DISK = N'G:\DBDump\InproUS_backup_2015_07_06_013003_7006602.bak' WITH  FILE = 1, 
MOVE N'INPRO' TO N'F:\DBData\InproUS.lud.mdf', 
MOVE N'INPRO_log' TO N'E:\Trans\InproUS.lud_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE DATABASE [InproUS] FROM  DISK = N'G:\DBDump\InproUS_backup_2015_07_06_013003_7006602.bak' WITH  FILE = 1, 
MOVE N'INPRO_log' TO N'E:\Trans\InproUS_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO


SELECT TOP 3 * FROM INPROASIA..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC
SELECT TOP 3 * FROM INPROEU..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC
SELECT TOP 3 * FROM INPROUS..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC

SELECT TOP 3 * FROM [INPROASIA.LUD]..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC
SELECT TOP 3 * FROM [INPROEU.LUD]..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC
SELECT TOP 3 * FROM [INPROUS.LUD]..CASEEVENT WHERE EVENTNO = -14 ORDER BY EVENTDATE DESC


-- SELECT name, database_id FROM SYS.databases WHERE name NOT IN ('master','tempdb','model','msdb','RSDB','RSDBTempDB')

--------------------------------------------------------------------------------------------------------------------------------

USE MASTER
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @DATABASE Nvarchar(100), @DBID INT, @FILENAME NVARCHAR(200), @SCRIPT NVARCHAR(MAX);

DECLARE DBCursor CURSOR FOR

SELECT D.NAME, D.database_id, F.NAME FROM SYS.databases D JOIN sys.master_files F ON D.database_id = F.database_id
WHERE F.TYPE_DESC = 'LOG' AND D.name IN ( 'INPROASIA', 'INPROEU', 'INPROUS', 'INPROASIA.LUD', 'INPROEU.LUD', 'INPROUS.LUD')
--NOT IN ('master','tempdb','model','msdb','RSDB','RSDBTempDB','ReportServer$TOKYO2','ReportServer$TOKYO2TempDB')
ORDER BY 1;

OPEN DBCursor;

FETCH NEXT FROM DBCursor
INTO @DATABASE, @DBID, @FILENAME;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
    SET @Database = '['+@Database+']'
    -- 1.
    SET @SCRIPT ='ALTER DATABASE ' +@Database+ ' SET RECOVERY SIMPLE'
    PRINT @SCRIPT
    EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)',    @Database
   
    -- 2.
    --SELECT @FILENAME = name FROM sys.master_files WHERE database_id = db_id(@DATABASE) AND type = 1
   
    -- PRINT '.....................' +  @FILENAME + '.....................'
    SET @SCRIPT =    'USE ' +@DATABASE+ ';'+
                    'DBCC SHRINKFILE ('+@FILENAME+', 1)'
    PRINT @SCRIPT
    EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)',    @Database
      
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM DBCursor
   INTO @DATABASE, @DBID, @FILENAME;
END

CLOSE DBCursor;
DEALLOCATE DBCursor;
GO

--------------------------------------------------------------------------------------------------------------------------------

--USE [Inproasia]
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [Inproasia]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (InproHKG_Log, 0);
--    GO

--USE [Inproeu]
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [InproEU]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (Inpro_Log, 1);
--    GO

--USE [InproUS] 
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [Inprous]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (Inpro_Log, 1);
--    GO

--USE [Inproasia.lud]
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [Inproasia.lud]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (InproHKG_Log, 1);
--    GO

--USE [Inproeu.lud]
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [InproEU.lud]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (Inpro_Log, 1);
--    GO

--USE [InproUS.lud] 
--GO
--    -- Truncate the log by changing the database recovery model to SIMPLE.
--    ALTER DATABASE [Inprous.lud]
--    SET RECOVERY SIMPLE;
--    GO
--    -- Shrink the truncated log file to 1 MB.
--    DBCC SHRINKFILE (Inpro_Log, 1);
--    GO

--USE master
--GO

--------------------------------------------------------------------------------------------------------------------------------

---- Truncate the log by changing the database recovery model to SIMPLE.
--ALTER DATABASE Inproasia
--SET RECOVERY SIMPLE;
--GO
---- Shrink the truncated log file to 1 MB.
--DBCC SHRINKFILE (InproHKG_Log, 1);
--GO
---- Reset the database recovery model.

--ALTER DATABASE Inproasia

--SET RECOVERY FULL;

2015年8月5日星期三

bat - hide or unhide folder


attrib C:\folder | findstr "^....H" >nul && (
  : unhide 
  attrib C:\folder -s -h -r
) || (
  : hide
  attrib C:\folder +s +h +r
)

2015年7月31日星期五

TSQL Load Image to ImageData

INSERT INTO IMAGETABLE( IMAGENAME, IMAGEDATA )
SELECT ImageName, *
FROM OPENROWSET(BULK 'D:\Image.png', SINGLE_BLOB) rs

TSQL - DBO.U_KillAllTasks

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[u_KillAllTasks]
( @Database nvarchar(200))
AS
BEGIN
    SET @Database = REPLACE (@Database, ' ','')
    SET @Database = ''''+REPLACE (@Database, ',',''',''')+''''
   
    DECLARE @SCRIPT NVARCHAR(MAX);
   
    DECLARE @spid INT;

    DECLARE MyCursor CURSOR FOR
    SELECT P.spid FROM SYS.sysprocesses P JOIN SYS.databases D ON P.dbid = D.database_id
    WHERE D.name NOT IN ('MASTER','MODEL','MSDB','TEMPDB') AND P.spid <> @@SPID
    --WHERE D.name IN (@Database)
   
    --PRINT @Database
   
    OPEN MyCursor;

    -- Perform the first fetch and store the values in variables.
    -- Note: The variables are in the same order as the columns
    -- in the SELECT statement.

    FETCH NEXT FROM MyCursor
    INTO @spid;

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN

       -- Concatenate and display the current values in the variables.
       SET @SCRIPT = 'KILL '+ LTRIM(STR(@spid))
        PRINT @SCRIPT
        EXEC ( @SCRIPT )
       
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM MyCursor
       INTO @spid;
    END

    CLOSE MyCursor;
    DEALLOCATE MyCursor;

    ----------------------------------------------------------------
    -- EXEC DBO.U_KillAllTasks @DATABASE='DATABASE01,
DATABASE02,DATABASE03'
END

2015年7月30日星期四

TSQL - Concatenate many rows into a single text string

SELECT [MAIN].CASEID, LEFT([MAIN].INTCLASS, LEN([MAIN].INTCLASS)-1) AS INTCLASS
FROM (
    SELECT CASEID, ( SELECT RIGHT('0'+Y.INTCLASS,2) + ',' AS [text()]
                    FROM DBO.HL_WRIGLEY_SPEC Y
                    WHERE X.CASEID = Y.CASEID
                    ORDER BY RIGHT('0'+Y.INTCLASS,2)
                    For XML PATH ('') ) AS INTCLASS
    FROM DBO.HL_WRIGLEY_SPEC X
    ) [MAIN]

ToolBox - Create Views for SIG and EMP


USE ToolBox

CREATE VIEW SB_SIGEMP_ASIA
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROASIA..OFFICE O
LEFT JOIN INPROASIA..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROASIA..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROASIA..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROASIA..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROASIA..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME

CREATE VIEW SB_SIGEMP_EU
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROEU..OFFICE O
LEFT JOIN INPROEU..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROEU..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROEU..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROEU..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROEU..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME

CREATE VIEW SB_SIGEMP_US
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROUS..OFFICE O
LEFT JOIN INPROUS..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROUS..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROUS..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROUS..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROUS..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME


2015年7月28日星期二

Excel VBA - Before Dataimport

Sub RomoveEmptyColumn()

    Dim IsEmpty As Boolean
   
    ro = getRowCount(0)
    co = getColumnCount(0)
   
    For c = co To 1 Step -1
        IsEmpty = True
        For r = 2 To ro
            'Cells(r, c).Select
            If Cells(r, c).Value <> "" Then
                IsEmpty = False
                r = ro
            End If
        Next r
       
        If IsEmpty = True Then
            Columns(c).Select
            Columns(c).Delete
        End If
    Next c
       
End Sub

Sub DataTidy()

    ro = getRowCount(0)
    For r = 2 To ro
        Cells(r, 2).Select
        Cells(r, 2).Value = "'" & Cells(r, 2).Value
    Next

    ro = getRowCount(0)
    For r = 2 To ro
        Cells(r, 2).Value = "'" & Cells(r, 2).Value
        Cells(r, 7).Value = "'" & Cells(r, 7).Value
        Cells(r, 12).Value = "'" & Cells(r, 12).Value
        Cells(r, 13).Value = "'" & Cells(r, 13).Value
        Cells(r, 26 + 4).Value = "'" & Cells(r, 26 + 4).Value
        Cells(r, 26 + 5).Value = "'" & Cells(r, 26 + 5).Value
        Cells(r, 26 + 16).Value = "'" & Cells(r, 26 + 16).Value
        Cells(r, 26 + 21).Value = "'" & Cells(r, 26 + 21).Value
        Cells(r, 26 + 22).Value = "'" & Cells(r, 26 + 22).Value
        Cells(r, 52 + 4).Value = "'" & Cells(r, 52 + 4).Value
        Cells(r, 52 + 14).Value = "'" & Cells(r, 52 + 14).Value
    Next

End Sub

2015年7月15日星期三

T-SQL : u_KillAllTasks

USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[u_KillAllTasks]    Script Date: 07/15/2015 15:39:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[u_KillAllTasks]
( @Database nvarchar(200))
AS 
BEGIN 
SET @Database = REPLACE (@Database, ' ','')
SET @Database = ''''+REPLACE (@Database, ',',''',''')+''''

DECLARE @SCRIPT NVARCHAR(MAX);

DECLARE @spid INT;

DECLARE MyCursor CURSOR FOR
SELECT P.spid FROM SYS.sysprocesses P JOIN SYS.databases D ON P.dbid = D.database_id 
WHERE D.name NOT IN ('MASTER','MODEL','MSDB','TEMPDB') AND P.spid <> @@SPID
--WHERE D.name IN (@Database)

--PRINT @Database

OPEN MyCursor;

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM MyCursor
INTO @spid;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

  -- Concatenate and display the current values in the variables.
  SET @SCRIPT = 'KILL '+ LTRIM(STR(@spid))
PRINT @SCRIPT 
EXEC ( @SCRIPT )

  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM MyCursor
  INTO @spid;
END

CLOSE MyCursor;
DEALLOCATE MyCursor;

----------------------------------------------------------------
-- EXEC DBO.U_KillAllTasks @DATABASE='INPROUS, INPROEU, TOOLBOX'
END 

TSQL - Shrink Files

-- SELECT name, database_id FROM SYS.databases WHERE name NOT IN ('master','tempdb','model','msdb','RSDB','RSDBTempDB')

--------------------------------------------------------------------------------------------------------------------------------

USE MASTER
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @DATABASE Nvarchar(100), @DBID INT, @FILENAME NVARCHAR(200), @SCRIPT NVARCHAR(MAX);

DECLARE DBCursor CURSOR FOR

SELECT D.NAME, D.database_id, F.NAME FROM SYS.databases D JOIN sys.master_files F ON D.database_id = F.database_id 
WHERE F.TYPE_DESC = 'LOG' AND D.name NOT IN ('master','tempdb','model','msdb','RSDB','RSDBTempDB','ReportServer$TOKYO2','ReportServer$TOKYO2TempDB') 
ORDER BY 1;

OPEN DBCursor;

FETCH NEXT FROM DBCursor
INTO @DATABASE, @DBID, @FILENAME;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
SET @Database = '['+@Database+']'
-- 1. 
SET @SCRIPT ='ALTER DATABASE ' +@Database+ ' SET RECOVERY SIMPLE' 
PRINT @SCRIPT 
EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database

-- 2. 
--SELECT @FILENAME = name FROM sys.master_files WHERE database_id = db_id(@DATABASE) AND type = 1

-- PRINT '.....................' +  @FILENAME + '.....................'
    SET @SCRIPT = 'USE ' +@DATABASE+ ';'+
'DBCC SHRINKFILE ('+@FILENAME+', 1)'
PRINT @SCRIPT 
EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database
       
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM DBCursor
   INTO @DATABASE, @DBID, @FILENAME;
END

CLOSE DBCursor;
DEALLOCATE DBCursor;
GO

2015年7月13日星期一

Excel VBA - Layout formatting for an exported excel worksheet

Sub main()

    Dim TheRange As Range
    ro = getRowCount(1)
    co = getColumnCount(1)
    
    Range(Cells(1, 1), Cells(ro, co)).Select
    Range(Cells(1, 1), Cells(ro, co)).VerticalAlignment = xlTop
    Range(Cells(1, 1), Cells(ro, co)).WrapText = True
    Range(Cells(1, 1), Cells(ro, co)).Font.Size = 8
    
    ' Set margin if need printing
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.2)
        .FooterMargin = Application.InchesToPoints(0.2)
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .PrintTitleRows = ActiveSheet.Rows(1).Address
        '.Zoom = False
        '.FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
    
    ActiveSheet.DisplayPageBreaks = False
    
    ' Design as table
    Dim lstList As ListObject
    For Each lstList In ActiveSheet.ListObjects
        If lstList.Name = "Table1" Then
            ActiveSheet.ListObjects("Table1").Unlist
            Exit For
        End If
    Next
    
    Range(Cells(1, 1), Cells(ro, co)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"
    
    ' Set Width
    Range(Cells(1, 1), Cells(ro, co)).Columns.AutoFit
    
    For c = 1 To co
        Columns(c).Select
        If Columns(c).ColumnWidth > 15 Then
            Columns(c).ColumnWidth = 15
            If Cells(1, c).Value = "Goods and Services" Then Columns(c).ColumnWidth = 45
        End If
    Next c
    
    Cells(1, 1).Select
End Sub

Public Function getRowCount(sheet As Variant) As Integer
    If sheet = 0 Then
        getRowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        getRowCount = Sheets(sheet).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function

Public Function getColumnCount(sheet As Variant) As Integer
    If sheet = 0 Then
        getColumnCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Else
        getColumnCount = Sheets(sheet).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If
End Function

2015年7月9日星期四

Work VBA - Do Something on all TextBox

Word VBA, Do something on all TexBox
  

Dim oShp As Word.Shape
Dim i As Long
   
x = ActiveDocument.Shapes.Count
For i = 1 To x
    Set oShp = ActiveDocument.Shapes(i)
    If oShp.Type = msoTextBox Then
        oShp.Select
        ' Do Somethinng
       
    End If
Next i
    

2015年6月27日星期六

Excel VBA : set autofiltering

Using VBA to set autofiltering :

Sheets(1).SelectIf ActiveSheet.AutoFilterMode = True Then  ActiveSheet.AutoFilterMode = FalseEnd If    Range("A4:AK4").ActivateActiveSheet.Range("A4:AK4").AutoFilter

2015年6月26日星期五

VBA Code to remove Duplicate Rows consider columns

VBA Code to remove Duplicate rows consider column
ro = getRowCount(0)
Range("A1:Z" & ro).Select
Range("A1:Z" & ro).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

VBA sub : DeleteColumns

In order to delete columns of a sheet, we can use below simple sub:
InputString = "C,G,H,I,J,T,U,V,W,X,Y"
DeleteColumns (InputString)
Sub DeleteColumns(InputString As String)
    Dim aString As Variant
    aString = Split(InputString, ",")
    cString = UBound(aString) + 1
    For c = cString To 1 Step -1
        Columns(aString(c - 1)).Select
        Columns(aString(c - 1)).Delete
    Next c
End Sub

Excel VBA : Hide or Unhide columns

To hide or unhide the column by vba:

Columns( 6 ).EntireColumn.Hidden = False
Columns("A:Z").EntireColumn.Hidden = False

2015年5月29日星期五

filepizza - P2P File Transfer Tools

filepizza - P2P File Transfer Tools

Reference Link : http://free.com.tw/filepizza/

免費下載高畫質相片

免費下載高畫質相片

Reference Source: http://free.com.tw/13-stock-photos-free-for-commercial-use/
1. MMT–這網站提供大量、可用於商業用途的免費相片,由 Jeffery Betts 拍攝。每週會定期更新相片,網站的相片都有相當好的品質,無須註冊即可下載。
2. Camarama.de–網站的宗旨為讓使用者下載、分享所有可用於公眾領域的圖片或相片,包括個人及商業用途,該網頁的相片都有經過濾鏡特效處理,看起來跟一般圖庫相片的感覺不太一樣,每週更新。
3. Splashbase–在網站上可以搜尋、探索免費高畫質的相片和影片素材,網站內容可以說相當豐富,允許使用者投稿、上傳自己拍攝的相片,影片大多採用 CC 3.0 授權。
4. Realistic Shots–每週固定更新 7 張新相片,網站提供免費、高解析度的相片素材,可用於個人或商業用途。
5. BOSS FIGHT(推薦!)–提供多種分類的免費相片圖庫,包括科技、物件、自然、人物、動物、食物、建築,網站收錄的相片都非常有水準,而且看起來跟一般免費圖庫很不一樣。
6. FancyCrave–高畫質可用於個人或商業用途的免費圖庫,每日更新。
7. Skitterphoto–提供公眾領域宣告相片(Public Domain Photos),無須註冊,網站提供搜尋功能,每日更新。
8. Moveast–一位葡萄牙人設立的免費相片圖庫,決定將這些照片釋出,讓使用者自由下載使用。來這裡找找有沒有你需要的相片,然後把它帶走吧!
9. Jay Mantri–固定每週四會更新 7 張新相片,一個提供大量黑白相片的免費圖庫。
10. Free Nature Stock–如果你想尋找有關大自然的相片素材,例如花花草草、星空、瀑布河流等等,來這個網站就沒錯了!
11. SplitShire–無論你是設計師、部落客、網站管理者、社群行銷人員或是企業公司,都能自由使用網站上提供的免費相片素材,這些相片皆採用 CC0 授權,可用於個人或商業用途且沒有任何限制。
12. Magdeleine–每日更新的免費高畫質相片圖庫,網站還依照圖片主題、授權、顏色和標籤下去分門別類。
13. Startup Stock Photos–就網站名稱不難想見,這是一個已提供創業者相關的圖庫,主題包括蘋果電腦、工作、Mac、辦公室等等的相片素材。

總結

一口氣再次介紹 13 個免費圖庫,相信大家手邊都已有非常充裕、完整的免費圖庫來源,希望讓需要相片素材的讀者們,都能有更多更棒的發現。
如果你還有其他不錯的免費圖庫網站,也歡迎提供給我

2015年5月12日星期二

TSQL - s.p. to enable and disable all Referential Integrity

Create a s.p., which can enable or disable all Referential Integrity

USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[u_AllReferentialIntegrity]    Script Date: 05/12/2015 11:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[u_AllReferentialIntegrity]
(
@Database NVARCHAR (100),
@Disable BIT
)
AS 
BEGIN 
----------------------------------------------------------------
-- DISABLE ALL REFERENTIAL INTEGRITY

DECLARE @NAME nvarchar(100), @STR nvarchar(300), @Script nvarchar(max)

SET @Script = 'DECLARE table_cursor CURSOR FOR ' +
'SELECT NAME FROM ' +@Database+ '.SYS.TABLES WHERE NAME NOT IN (''HL_Vietnam.BAK'', ''Lovells_Tokyo_DAKS.BAK'') ORDER BY NAME '
EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database

OPEN table_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM table_cursor
INTO @NAME

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Concatenate and display the current values in the variables.
IF @Disable = 1 
BEGIN 
SET @STR = 'USE ' +@Database+ '; ALTER TABLE ' + @NAME + ' NOCHECK CONSTRAINT ALL'
END 
ELSE 
BEGIN 
SET @STR = 'USE ' +@Database+ '; ALTER TABLE ' + @NAME + ' CHECK CONSTRAINT ALL'
END 

PRINT @STR
EXECUTE sp_executesql @STR

  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM table_cursor
  INTO @NAME
END

CLOSE table_cursor
DEALLOCATE table_cursor

-- EXEC [u_AllReferentialIntegrity] @DATABASE = 'INPROASIA', @DISABLE = TRUE
END 


TSQL - s.p. to enable and disable all trigger

Create a s.p. which can enable and disable all trigger.
USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[u_AllTriggers]    Script Date: 05/12/2015 11:23:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[u_AllTriggers] 
(
@Database NVARCHAR (100),
@Disable bit
)
AS 
BEGIN 
----------------------------------------------------------------
-- DISABLE OR ENABLE ALL TRIGGERS
DECLARE @TRNAME nvarchar(100), @TANAME nvarchar(100), @STR nvarchar(300), @Script nvarchar(MAX)

SET @Script = 'DECLARE trigger_cursor CURSOR FOR ' +
'SELECT tr.name AS TRNAME, ta.name AS TANAME from ' +@Database+ '.sys.objects tr ' +
'join ' +@Database+ '.sys.objects ta on tr.parent_object_id = ta.object_id ' +
'where tr.type = ''tr'' ' +
'order by ta.name, tr.name '

EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database

PRINT @Script 

OPEN trigger_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM trigger_cursor
INTO @TRNAME , @TANAME

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
IF @Disable=1 
BEGIN
SET @STR = 'USE ' +@DATABASE+'; DISABLE TRIGGER ' +@TRNAME+ ' ON ' +@TANAME
END 
ELSE 
BEGIN 
SET @STR = 'USE ' +@DATABASE+'; ENABLE TRIGGER ' +@TRNAME+ ' ON ' +@TANAME
END 

PRINT @STR
EXECUTE sp_executesql @STR

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM trigger_cursor
INTO @TRNAME, @TANAME
END

CLOSE trigger_cursor
DEALLOCATE trigger_cursor

-- EXEC [u_AllTriggers] @DATABASE = 'INPROASIA', @DISABLE=FALSE 
END  

SQL Script - s.p. to kill active tasks for databases

Below is a script to kill active tasks for databases 

USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[u_KillAllTasks]    Script Date: 05/12/2015 11:18:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[u_KillAllTasks]
( @Database nvarchar(200))
AS
BEGIN
SET @Database = REPLACE (@Database, ' ','')
SET @Database = ''''+REPLACE (@Database, ',',''',''')+''''
DECLARE @SCRIPT NVARCHAR(MAX);
DECLARE @spid INT;
DECLARE MyCursor CURSOR FOR
SELECT P.spid FROM SYS.sysprocesses P JOIN SYS.databases D ON P.dbid = D.database_id
WHERE D.name NOT IN ('MASTER','MODEL','MSDB','TEMPDB') AND P.spid <> @@SPID
--WHERE D.name IN (@Database)
--PRINT @Database
OPEN MyCursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM MyCursor
INTO @spid;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Concatenate and display the current values in the variables.
  SET @SCRIPT = 'KILL '+ LTRIM(STR(@spid))
PRINT @SCRIPT
EXEC ( @SCRIPT )
  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM MyCursor
  INTO @spid;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;
----------------------------------------------------------------
-- EXEC DBO.U_KillAllTasks @DATABASE='INPROUS, INPROEU, TOOLBOX'
END 

2015年5月11日星期一

Oracle - Refresh Stats


Below is a script to refresh the statics

SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES ORDER BY 1
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'OwnerName')

Oracle - Resize Database File


When Oracle database file need to be enlarged or re-sized, below script can help.

1) Check the size of database file 
SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name,
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES,
                  SUM (BYTES) BYTES,
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;
2) Check the database path
SELECT * FROM DBA_DATA_FILES where TABLESPACE_NAME = 'XXX_Data';
SELECT * FROM Dba_Users;
3) Apply the re-size
ALTER DATABASE DATAFILE 'E:\APP\ORACLE\ORADATA\MEMOTECH\POLYU.DAT' RESIZE 2048M;

2015年5月9日星期六

getRowCount

I always use this function to get row count in the excel worksheet.

Public Function getRowCount(sheet As Variant) As Integer
    'Sheets(sheet).Select
    If sheet = 0 Then
        getRowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    Else
        getRowCount = Sheets(sheet).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    End If
End Function

Public Function getColumnCount(sheet As Variant) As Integer
    'Sheets(sheet).Select
    If sheet = 0 Then
        getColumnCount = ActiveSheet.Cells.Find("*", SearchOrder:=
xlByColumns, SearchDirection:=xlPrevious).column
    Else
        getColumnCount = Sheets(sheet).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    End If
End Function