How to add/remove stored procedure from auto execution in SQL

Using sp_procoption, you can set a stored procedure for auto execution whenever SQL instance start. This is useful if you want to perform a task regularly and need to have it running as a background process.

exec sp_procoption @ProcName = ['stored procedure name'],
@OptionName = 'STARTUP',
@OptionValue = [on|off]
 

A simple and useful usage is to auto-map the network drive, so that SQL database can be restored from backup files in network drive.

CREATE PROCEDURE sp_AutoMapNetwork AS
BEGIN
EXEC cmd_shell  'NET USE {device} [password]'
END

Fixing website styles with GreaseMonkey / TamperMonkey

There may be a case that you visit a website with bad-looking design, but you can’t change it. You do not own that website.
Take a look at this www.runscope.com which provide API testing service. Here is the screenshot in Chrome 39 under Windows 7 64-bit. Their default font is “Source Sans Pro” which somehow look bad:

Then with just a little effort: install TamperMonkey then add the script below, the problem is solved.

// ==UserScript==
// @name         Change font style of RunScope
// @namespace    http://diophung.com/
// @version      0.1
// @description  Replace RunScope ugly font
// @author       Dio Phung
// @match        https://www.runscope.com/*
// @grant        none
// ==/UserScript==

//Change the ugly font of RunScope
document.body.style.fontFamily = "PT Sans";

Here is how the new www.runscope.com looks like: the font is much easier to read.

Script to restore MS SQL database from a physical path

In case we want to automate the deployment of a MS SQL database, here is the script.

It will restore a database from a backup file, and store into the folder of your choice.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Dio Phung
-- Create date:   05/01/2015
-- Description:   Restore DB from a physical path
-- =============================================
USE [master]
IF EXISTS(SELECT * from sys.[objects] AS o WHERE o.[type]='P' AND o.[name]='sp_RestoreDbFromPath')
BEGIN
    DROP PROCEDURE sp_RestoreDbFromPath
END

GO

CREATE PROCEDURE sp_RestoreDbFromPath(
    @db_name nvarchar(max), -- The name of the db
    @db_source_fullpath NVARCHAR(max), -- path to the backup    
     @db_dest_folder nvarchar(max) -- folder to store db)
AS
BEGIN
    
    SET NOCOUNT ON;
    DECLARE @Table TABLE
        (
         LogicalName VARCHAR(128)
        ,[PhysicalName] VARCHAR(128)
        ,[Type] VARCHAR
        ,[FileGroupName] VARCHAR(128)
        ,[Size] VARCHAR(128)
        ,[MaxSize] VARCHAR(128)
        ,[FileId] VARCHAR(128)
        ,[CreateLSN] VARCHAR(128)
        ,[DropLSN] VARCHAR(128)
        ,[UniqueId] VARCHAR(128)
        ,[ReadOnlyLSN] VARCHAR(128)
        ,[ReadWriteLSN] VARCHAR(128)
        ,[BackupSizeInBytes] VARCHAR(128)
        ,[SourceBlockSize] VARCHAR(128)
        ,[FileGroupId] VARCHAR(128)
        ,[LogGroupGUID] VARCHAR(128)
        ,[DifferentialBaseLSN] VARCHAR(128)
        ,[DifferentialBaseGUID] VARCHAR(128)
        ,[IsReadOnly] VARCHAR(128)
        ,[IsPresent] VARCHAR(128)
        ,[TDEThumbprint] VARCHAR(128)
    )

    DECLARE @LogicalNameData VARCHAR(128),@LogicalNameLog VARCHAR(128)
    INSERT  INTO @table EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @db_source_fullpath + ''' ')

    SET @LogicalNameData = ( SELECT LogicalName FROM   @Table WHERE  Type = 'D' )
    SET @LogicalNameLog = ( SELECT  LogicalName FROM   @Table WHERE   Type = 'L')

    DECLARE @RestoreSql  nvarchar(max)
    SET @RestoreSql = (
    'RESTORE DATABASE ['  + @db_name +'] FROM  DISK = N'''+ @db_source_fullpath+  ''' 
    WITH  FILE = 1,  
    MOVE N'''+ @LogicalNameData+ ''' TO N'''+ @db_dest_folder + '\'+@db_name+'.mdf'',  
    MOVE N'''+ @LogicalNameLog+ '''  TO N'''+ @db_dest_folder + '\'+@db_name+'_log.ldf'',   
    NOUNLOAD,  REPLACE,  STATS = 10
    ')

    SELECT @RestoreSql
    EXEC(@RestoreSql)
END
GO