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

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

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

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

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

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