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月8日星期二
2015年9月2日星期三
oracle : the password has expired
http://www.dbas-oracle.com/2011/06/ora-28001-password-has-expired.html
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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];
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;
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
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
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]
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
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
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
--------------------------------------------------------------------------------------------------------------------------------
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
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 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
1) Check the size of database file
SELECT a.tablespace_name,2) Check the database path
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;
SELECT * FROM DBA_DATA_FILES where TABLESPACE_NAME = 'XXX_Data';3) Apply the re-size
SELECT * FROM Dba_Users;
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
訂閱:
文章 (Atom)