sql solace

Wednesday, 30 May 2007

Source Code 'Prettifier'

I cannot promise to always use it, but theres a new tool on the simple talk site to make source code more presentable for display.

http://www.simple-talk.com/prettifier/

Tuesday, 29 May 2007

USP_KillUserProcess

CREATE PROCEDURE [dbo].[usp_KillUserProcesses] @user varchar(100)
AS

BEGIN
 
SET NOCOUNT ON

 -- variable declaration and initial values
 DECLARE @chv_killprocessestring varchar(8000)
 DECLARE @ins_initialprocesses smallint
 DECLARE @ins_remainingprocesses smallint

 SET @ins_initialprocesses = 0
 SET @ins_remainingprocesses = 0
 SET @chv_killprocessestring = ''

 -- Fetch number of processes to kill 
 SELECT  @ins_initialprocesses = COUNT(*)
 FROM master..sysprocesses 
 WHERE   loginame = @user
 AND  SPID <> @@spid

 -- Check there are processes to kill
 IF @ins_initialprocesses > 0 
 
 BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
  FROM   master..sysprocesses 
   WHERE   loginame = @user
  AND  SPID <> @@spid

  -- Kill processes (run command created above) >
  EXEC (@chv_killprocessestring)

  -- Fetch number of processes remaining >
  SELECT  @ins_remainingprocesses = COUNT(*)
  FROM master..sysprocesses 
   WHERE   loginame = @user
   AND  SPID <> @@spid

  IF @ins_remainingprocesses = 0
  BEGIN
  -- Report on processes killed if killed them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses)) + ' connection(s)'
  END

  IF @ins_remainingprocesses <> 0
  BEGIN
  -- Report on processes remaining if could not end them all >
  PRINT LTRIM(RTRIM(UPPER(@user))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses-@ins_remainingprocesses)) + ' connection(s), '+ RTRIM(CONVERT(VARCHAR(10), @ins_remainingprocesses)) + ' processes remaining.'
  END
 END 

IF @ins_initialprocesses = 0 
 BEGIN
  -- Report there are no processes for that user>
  PRINT @user + ' - No processes to kill'
 END
END

USP_KillHostProcesses

CREATE PROCEDURE [dbo].[USP_KillHostProcesses] @HOSTNAME varchar(100)
AS

SET NOCOUNT ON

-- variable declaration and initial values
DECLARE  @chv_killprocessestring VARCHAR(8000)  
DECLARE  @ins_initialprocesses SMALLINT
DECLARE  @ins_remainingprocesses SMALLINT  

SET @ins_initialprocesses = 0  
SET @ins_remainingprocesses = 0  
SET @chv_killprocessestring = ''

-- Fetch number of processes to kill

SELECT @ins_initialprocesses = COUNT(* )
FROM   MASTER..SYSPROCESSES
WHERE  HOSTNAME = @HOSTNAME
AND  SPID <> @@spid
select *  FROM   MASTER..SYSPROCESSES

-- Check there are processes to kill
IF @ins_initialprocesses > 0
BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME

  -- Kill processes (run command created above) >
  EXEC( @chv_killprocessestring)
  
  -- Fetch number of processes remaining >
  SELECT @ins_remainingprocesses = COUNT(* )
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME
  
  IF @ins_remainingprocesses = 0
    BEGIN
      -- Report on processes killed if killed them all >
      PRINT LTRIM(RTRIM(UPPER(@HOSTNAME))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses)) + ' process(s)'
    END
  
  IF @ins_remainingprocesses <> 0
    BEGIN
      -- Report on processes remaining if could not end them all >
      PRINT LTRIM(RTRIM(UPPER(@HOSTNAME))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses - @ins_remainingprocesses)) + ' process(s), ' + RTRIM(CONVERT(VARCHAR(10),@ins_remainingprocesses)) + ' processes remaining.'
    END
END

IF @ins_initialprocesses = 0
BEGIN
  -- Report there are no processes for that DB >
  PRINT @HOSTNAME + ' - No processes to kill'
END

GO

Sunday, 27 May 2007

USP_FixOrphans - Fix Orphanned Users Procedure (for ALL databases)

Script to fix orphanned users after a restore operation.
This builds on USP_FixUsers and works for all databases.

Save it to master database or common tools db.

Usage : EXEC Usp_FixOrphans 'databasename' or EXEC Usp_FixOrphans '/all'

ALTER PROCEDURE USP_FixOrphans(@chvparameterdbname VARCHAR(255))
AS
/*


Fix Orphanned Users


Stored Procedure


Procedure to reattach users after restores



1
27/05/2007
sqlsolace
Original Version



*/
SET NOCOUNT ON

-- check for parameters
IF @chvparameterdbname IS NULL
OR LTRIM(RTRIM(@chvparameterdbname)) = ''
BEGIN
PRINT 'USP_FixOrphans : Parameter missing : must provide a database name or /all'

RETURN
END

SET @chvparameterdbname = LTRIM(RTRIM(@chvparameterdbname))

-- check for reconnecting all dbs
IF @chvparameterdbname = '/all'
BEGIN
PRINT 'USP_FixOrphans : Executing for all databases'

SET @chvparameterdbname = ''
END
ELSE
BEGIN
PRINT 'USP_FixOrphans : Executing for ' + @chvparameterdbname
END

IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
BEGIN
DROP TABLE ##SERVERLOGINS
END

IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
BEGIN
DROP TABLE ##DBUSERS
END

IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
BEGIN
DROP TABLE ##DBLIST
END

IF OBJECT_ID('tempdb..##DBResults') IS NOT NULL
BEGIN
DROP TABLE ##DBRESULTS
END

DECLARE  @DBName VARCHAR(128);

DECLARE  @SQLCmd VARCHAR(2000);

DECLARE  @NumberOfDBs INT;

DECLARE  @chvOrphanName VARCHAR(255);

DECLARE  @chvCurrentDatabaseName VARCHAR(255);

-- Get the SQL Server logins
-- Create login table
CREATE TABLE ##SERVERLOGINS (
[SID]        VARBINARY(85)   NULL,
[LOGIN_NAME] VARCHAR(128)   NULL);

-- Populate login table
INSERT INTO ##SERVERLOGINS
SELECT SID,
CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
FROM   MASTER.DBO.SYSLOGINS;

-- Create list of databases
CREATE TABLE ##DBLIST (
[DBNAME] VARCHAR(128))

IF @chvparameterdbname = ''
BEGIN
-- perform for all dbs on server
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    DBID > 4
ORDER BY NAME;
END
ELSE
BEGIN
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    NAME = @chvparameterdbname
ORDER BY NAME;
END

SELECT @NumberOfDBs = COUNT(* )
FROM   ##DBLIST

-- Create the output table for the Database User ID's
CREATE TABLE ##DBUSERS (
[DATABASE_USER_ID] VARCHAR(128),
[SERVER_LOGIN]     VARCHAR(128),
[DATABASE_ROLE]    VARCHAR(128),
[DATABASE_NAME]    VARCHAR(128));

CREATE TABLE ##DBRESULTS (
[DATABASE_NAME]   VARCHAR(128),
[TOTAL_USERS]     SMALLINT,
[USERS_OK]        SMALLINT,
[USERS_ORPHANNED] SMALLINT,
[USERS_FIXED]     SMALLINT,
[USERS_NOLOGIN]   SMALLINT);

-- Declare a cursor to loop through all the databases on the server
DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
INSERT INTO ##DBRESULTS
(DATABASE_NAME,
TOTAL_USERS,
USERS_OK,
USERS_ORPHANNED)
SELECT ##DBLIST.DBNAME,
ALLCOUNT,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS ALLCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
GROUP BY DATABASE_NAME) ALLUSERS
ON ALLUSERS.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME

--select * from ##DBResults

-- cursor through users we can repair
DECLARE CURREPAIRORPHANEDUSERS CURSOR FORWARD_ONLY FOR
SELECT DATABASE_NAME,
DATABASE_USER_ID
FROM   ##DBUSERS
INNER JOIN ##SERVERLOGINS
ON ##DBUSERS.DATABASE_USER_ID = ##SERVERLOGINS.LOGIN_NAME
WHERE  SERVER_LOGIN = '* Orphaned *'

OPEN CURREPAIRORPHANEDUSERS

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName

WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN
IF @chvOrphanName = 'dbo'
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGEDBOWNER ''Sa'''
END
ELSE
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGE_USERS_LOGIN ''update_one'',' + '''' + @chvOrphanName + ''',' + '''' + @chvOrphanName + ''''
END
END

PRINT @SQLCmd

EXEC( @SQLCmd)

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName
END

CLOSE CURREPAIRORPHANEDUSERS

DEALLOCATE CURREPAIRORPHANEDUSERS

-- repopulate the ##DBUsers table so can compare what the script has achieved
DELETE ##DBUSERS

DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
UPDATE ##DBRESULTS
SET    USERS_FIXED = NEWRESULTS.OKCOUNT - USERS_OK,
USERS_NOLOGIN = NEWRESULTS.ORPHANNEDCOUNT
FROM   ##DBRESULTS
INNER JOIN (SELECT ##DBLIST.DBNAME AS DBNAME,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME) NEWRESULTS
ON ##DBRESULTS.DATABASE_NAME = NEWRESULTS.DBNAME

SELECT DATABASE_NAME,
TOTAL_USERS,
USERS_OK        AS INITIAL_USERS_OK,
USERS_ORPHANNED AS INITIAL_USERS_ORPHANNED,
USERS_FIXED,
USERS_NOLOGIN   AS USERS_NO_MATCHING_LOGIN
FROM   ##DBRESULTS

GO

Wednesday, 23 May 2007

USP_KillSleepingProcesses - Procedure to kill sleeping processes

USP_KillSleepingProcesses - Procedure to kill sleeping processes
Pass the database name as the parameter or '/all' for all dbs.

exec USP_KillSleepingProcesses 'WebDb' -- or '/all'
GO

ALTER PROCEDURE [Utils].[usp_KillSleepingProcesses] @database varchar(100)
AS

SET NOCOUNT ON

-- Check validity of supplied database name
IF DB_ID(@database) > 4 OR LOWER(LTRIM(RTRIM(@database))) = '/all'
BEGIN
  -- variable declaration and initial values
  DECLARE  @chv_killprocessestring VARCHAR(8000)  
  DECLARE  @ins_initialprocesses SMALLINT
  DECLARE  @ins_remainingprocesses SMALLINT  

  SET @ins_initialprocesses = 0  
  SET @ins_remainingprocesses = 0  
  SET @chv_killprocessestring = ''
  
  -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
       -- avoid system processes
       AND spid > 50
        END
      ELSE
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  DBID = DB_ID(@database)
                      AND STATUS = 'sleeping'
        AND  SPID <> @@spid
  -- avoid system processes
  AND spid > 50

        END
  
  -- Check there are processes to kill
  IF @ins_initialprocesses > 0
    BEGIN
      -- Build command string of processes to kill >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
           AND spid > 50
        END
      ELSE
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  DBID = DB_ID(@database)
                 AND STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
     AND spid > 50

        END
      
      -- Kill processes (run command created above) >
      EXEC( @chv_killprocessestring)
      
      -- Fetch number of processes remaining >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
        -- avoid system processes
       AND spid > 50
  END
   ELSE
  BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  DBID = DB_ID(@database)
       AND  STATUS = 'sleeping'
    AND  SPID <> @@spid
    -- avoid system processes
    AND  spid > 50
  END
      
      IF @ins_remainingprocesses = 0
        BEGIN
          -- Report on processes killed if killed them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses)) + ' process(s)'
        END
      
      IF @ins_remainingprocesses <> 0
        BEGIN
          -- Report on processes remaining if could not end them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses - @ins_remainingprocesses)) + ' process(s), ' + RTRIM(CONVERT(VARCHAR(10),@ins_remainingprocesses)) + ' processes remaining.'
        END
    END
    
  IF @ins_initialprocesses = 0
    BEGIN
      -- Report there are no processes for that DB >
      PRINT @database + ' - No processes to kill'
    END
END

ELSE

BEGIN
  -- Supplied database name not valid or was a system db, hence report that >
  PRINT 'Invalid Database name or System Database supplied'
END
GO

Tuesday, 22 May 2007

USP_KillAllProcesses - Procedure to Kill connections to (processes running in) a database

I thought this would be useful to run before restore jobs.
It uses sysprocesses to find and kill any open processes for the supplied database name or all if you pass '/all'

ALTER PROCEDURE [Utils].[usp_KillAllProcesses] @database varchar(100)
AS
SET NOCOUNT ON
-- Check validity of supplied database name
IF db_id(@database) > 4 OR LOWER(LTRIM(RTRIM(@database))) = '/all'
BEGIN
 
 -- variable declaration and initial values
 DECLARE @chv_killprocessestring varchar(8000)
 DECLARE @ins_initialprocesses smallint
 DECLARE @ins_remainingprocesses smallint

 SET @ins_initialprocesses = 0
 SET @ins_remainingprocesses = 0
 SET @chv_killprocessestring = ''

 -- Fetch number of processes to kill 
 -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    AND spid <> @@spid
    -- avoid system processes
    AND spid > 50
  END
ELSE
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE   dbid = db_id(@database) 
    -- to avoid the sql process
    AND status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    AND spid <> @@spid
    -- avoid system processes
    AND spid > 50
  END

 -- Check there are processes to kill
 IF @ins_initialprocesses > 0 
 
 BEGIN
  -- Build command string of processes to kill >
IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
     FROM   master..sysprocesses 
     WHERE  status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END
ELSE
  BEGIN
     SELECT @chv_killprocessestring = coalesce(@chv_killprocessestring,',' ) + 'KILL ' + convert(varchar, spid)+ '; '
     FROM   master..sysprocesses 
     WHERE  dbid = db_id(@database)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END


  -- Kill processes (run command created above) >
  EXEC (@chv_killprocessestring)

      -- Fetch number of processes remaining >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT  @ins_remainingprocesses = COUNT(*)
     FROM    master..sysprocesses 
     WHERE   status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END
   ELSE
  BEGIN
     SELECT  @ins_remainingprocesses = COUNT(*)
     FROM master..sysprocesses 
     WHERE   dbid = db_id(@database)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END


  IF @ins_remainingprocesses = 0
  BEGIN
  -- Report on processes killed if killed them all >
  PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses)) + ' connection(s)'
  END

  IF @ins_remainingprocesses <> 0
  BEGIN
  -- Report on processes remaining if could not end them all >
  PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10), @ins_initialprocesses-@ins_remainingprocesses)) + ' connection(s), '+ RTRIM(CONVERT(VARCHAR(10), @ins_remainingprocesses)) + ' processes remaining.'
  END
 END 

IF @ins_initialprocesses = 0 
 BEGIN
  -- Report there are no processes for that DB >
  PRINT @database + ' - No processes to kill'
 END
END

ELSE

BEGIN 
 -- Supplied database name not valid or was a system db, hence report that >
 PRINT 'Invalid Database name or System Database supplied'    
END
GO

Monday, 21 May 2007

{Off Topic} Internet Usage Summary

I was bemused by this proxy server summary >

category_name	connections
Adult/Sexually Explicit	4121
Advertisements & Popups	1083293
Alcohol & Tobacco	1563
Arts	6326
Blogs & Forums	39031
Business	303674
Chat	18648
Company & Intranet	354799
Computing & Internet	463939
Criminal Activity	119
Downloads	25369
Education	18288
Entertainment	114408
Fashion & Beauty	1858
Finance & Investment	289405
Food & Dining	7432
Gambling	13935
Games	19954
Government	56587
Hacking	43
Health & Medicine	9693
Hobbies & Recreation	12207
Hosting Sites	5093
Illegal Drugs	48
Infrastructure	658618
Intimate Apparel & Swimwear	4591
Intolerance & Hate	59
Job Search & Career Development	20044
Kids Sites	1289
Motor Vehicles	94384
News	192035
Peer-to-Peer	243
Personals & Dating	10650
Philanthropic & Professional Orgs.	6037
Phishing & Fraud	66
Photo Searches	17370
Politics	1638
Proxies & Translators	1273
Real Estate	136073
Reference	587250
Religion	1250
Ringtones/Mobile Phone Downloads	279
Search Engines	627024
Sex Education	23
Shopping	250154
Society & Culture	14953
Spam URLs	87
Sports	76104
Spyware	10192
Streaming Media	289529
Tasteless & Offensive	1098
Travel	167507
Uncategorized	1281753
Violence	101
Weapons	394
Web-based E-mail	373831