Skip to content

SQL RESEED Indentity Column

SELECT field1, field2 INTO #temptable FROM table  --(Leave off identity column)
DELETE FROM table

DBCC CHECKIDENT('table', RESEED, 1)

INSERT INTO table
SELECT * FROM #temptable --(Select in order you want it seeded)
DROP TABLE #temptable
Advertisements

Register ActiveX on Window Server 2008 R2

1. Put ActiveX files into C:\Windows\System. Make sure it is not in C:\Windows\System32.
2. Click Start and type in cmd. Do not click enter.
3. Right click on cmd in the window and Run as administrator.
4. At command prompt type RegSvr32 *Your ActiveX*.ocx

SQL Get Last Entry For Each Type In Data Log Using RANK()

WITH	a AS ( SELECT	*, RANK() OVER ( PARTITION BY System ORDER BY LogTime DESC ) r
			   FROM		tblLog)
	SELECT	*
	FROM	a
	WHERE	r = 1

SQL Find Last Update/Lookup Times For All Tables In Database

SELECT	OBJECT_NAME(OBJECT_ID) AS tablename, last_user_update, last_user_seek, last_user_scan, last_user_lookup,
		last_system_scan, user_scans, user_seeks, user_lookups, user_updates, system_seeks, system_scans, system_lookups,
		system_updates
FROM	sys.dm_db_index_usage_stats
WHERE	database_id = DB_ID('DatabaseName')
		AND OBJECT_ID IN ( SELECT	object_id
						   FROM		sys.Tables )

Windows Service – Restart windows service after idle timeout

UltraVNC’s idle timeout setting does not work so I created this service to monitor idle time on the computer and then restart the service to disconnect all current connections.
The default timeout is set to 120s. This can be changed through the registry setting.


Imports System.Runtime.InteropServices
Imports System.ServiceProcess

Public Class VNCRestart
    Private tMonitor As New System.Timers.Timer(5000)
    Private idle As Boolean

    Protected Overrides Sub OnStart(ByVal args() As String)
        AddHandler tMonitor.Elapsed, AddressOf tMonitor_Tick
        tMonitor.Enabled = True
    End Sub

    Protected Overrides Sub OnStop()
        tMonitor.Enabled = False
        RemoveHandler tMonitor.Elapsed, AddressOf tMonitor_Tick
    End Sub

    Private Sub tMonitor_Tick(ByVal sender As Object, ByVal e As EventArgs)
        tMonitor.Stop()
        Dim tout = Timeout * 1000
        If GetIdleTime() > tout And Not idle Then
            RestartService("uvnc_service", 30000)
            idle = True
        ElseIf GetIdleTime() < 60000 And idle Then
            idle = False
        End If
        tMonitor.Start()
    End Sub

    Shared ReadOnly Property Timeout() As Integer
        Get
            Timeout = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\Software\VNC Idle Timeout\", "Timeout", 0)
            If Timeout.Equals(0) Then
                Timeout = 120
                My.Computer.Registry.SetValue("HKEY_LOCAL_MACHINE\Software\VNC Idle Timeout\", "Timeout", Timeout)
            End If
        End Get
    End Property

    'Create a structure to pass the reference to the dll.
    Public Structure LASTINPUTINFO
        Public cbSize As UInteger
        Public dwTime As UInteger
    End Structure

    'Importing the Dll & declaring the neccessary funtion
    <DllImport("user32.dll")> _
    Private Shared Function GetLastInputInfo(ByRef plii As LASTINPUTINFO) As Boolean
    End Function

    'Function to return the idle time
    Private Function GetIdleTime() As Integer
        'Creating the object of the structure
        Dim lastOne As New LASTINPUTINFO()

        'Initialising
        lastOne.cbSize = CUInt(Marshal.SizeOf(lastOne))
        lastOne.dwTime = 0

        Dim idleTime As Integer = 0

        'To get the total time after starting the system.
        Dim tickCount As Integer = Environment.TickCount

        'Calling the dll funtion and getting the last input time.
        If GetLastInputInfo(lastOne) Then
            idleTime = tickCount - CInt(lastOne.dwTime)
            Return idleTime
        Else
            Return 0
        End If
    End Function

    Public Sub RestartService(serviceName As String, timeoutMilliseconds As Integer)
        Dim service As New ServiceController(serviceName)
        Try
            Dim millisec1 = Environment.TickCount
            Dim timeout = TimeSpan.FromMilliseconds(timeoutMilliseconds)

            service.Stop()
            service.WaitForStatus(ServiceControllerStatus.Stopped, timeout)

            ' count the rest of the timeout
            Dim millisec2 = Environment.TickCount
            timeout = TimeSpan.FromMilliseconds(timeoutMilliseconds - millisec2 - millisec1)

            service.Start()
            service.WaitForStatus(ServiceControllerStatus.Running, timeout)
        Catch
        End Try
    End Sub
End Class
    

Source
Install

Create SQL trigger that will log specific fields changed in log table

CREATE TRIGGER [t_Test] ON [tbl_Test]
	AFTER INSERT
AS
	SET NOCOUNT ON
		
	DECLARE	@Cursor CURSOR ,
		@Column NVARCHAR(50) ,
		@SQL VARCHAR(MAX)

	SELECT	*
	INTO	##New
	FROM	INSERTED;
		
	WITH	a AS ( SELECT	*, ROW_NUMBER() OVER ( ORDER BY ChangeTime DESC ) AS rn
				   FROM		dbo.tbl_Test
				   WHERE	[System] IN ( SELECT	[System]
										  FROM		INSERTED ))
		SELECT	*
		INTO	##Old
		FROM	a
		WHERE	rn = 2

	SET @Cursor = CURSOR FORWARD_ONLY STATIC FOR
			SELECT	[name]
			FROM	sys.columns
			WHERE	OBJECT_ID = ( SELECT	OBJECT_ID
								  FROM		sys.objects
								  WHERE		[name] = 'tbl_Test' )
					AND [name] <> 'DataID'
					AND [name] <> 'ChangeDate'
					AND [name] <> 'ChangedBy'
					AND [name] <> 'System'  

	OPEN @Cursor
	FETCH NEXT FROM @Cursor INTO @Column

	WHILE @@FETCH_STATUS = 0 
		BEGIN
			SET @SQL = N'INSERT INTO tbl_Test_ChangeLog 
					SELECT DISTINCT	p1.ChangeDate, p1.ChangedBy, p1.System, 
						''' + @Column + ''' Field, p1.[' + @Column + '] OldValue, p2.[' + @Column + '] NewValue
						FROM ##Old p1
						INNER JOIN ##New p2 ON p1.System = p2.System 
						WHERE	p1.System <> '''' AND p1.[' + @Column + '] <> p2.[' + @Column + ']'
				--PRINT @SQL
			EXEC (@SQL)

			FETCH NEXT FROM @Cursor INTO @Column
		END

	CLOSE @Cursor
	DEALLOCATE @Cursor
		  
	DROP TABLE ##New
	DROP TABLE ##Old

SQL Rename Tables, Views and Stored Procedures

In this example I am removing “__” from the front of all tables, views and stored procedures.

DECLARE @Cursor CURSOR	
DECLARE @name VARCHAR(50)

SET 
@Cursor = CURSOR FORWARD_ONLY STATIC FOR
    SELECT  name
    FROM    sysobjects
    WHERE   ([type] = 'U'  --Tables
            OR [type] = 'V' --View
            OR [type] = 'P')  --Stored Procedures
            AND SUBSTRING(name, 1, 2) = '__'

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name

WHILE @@FETCH_STATUS = 0 
    BEGIN
        DECLARE @newname AS VARCHAR(50)
        SET @newname =  SUBSTRING(@name,2,99) 
        SELECT @newname
        EXEC sp_rename @name, @newname 

        FETCH NEXT FROM @Cursor INTO @name
    END

CLOSE @Cursor
DEALLOCATE @Cursor