Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

2Aug/100
Terminate Connections to an SQL Database

Have you ever tried to restore a database to an SQL server with existing connections? Unless the connections are terminated, the database restore will fail with the error that you cannot obtain exclusive access. You can manually kill each connection, but with many users this can be time consuming. I've found the SQL server just doesn't do a good job at this. This script does a really good job of killing connections to a specific database.

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @USRCOUNT varchar(3),
  @PRINTUSRCOUNT VARCHAR (255), 
  @KILLSTATE varchar(32),
  @spid smallint 

-- Query for id number of all connections
use master
set @strsql = "select hostname,program_name,spid from sysprocesses where database='Database Name' order by hostname"

create table #tokill
(
  hostname varchar(50),
  program_name varchar(50),
  spid varchar(5)
)

-- Insert into temp table
INSERT INTO #tokill EXEC(@strsql)

-- Comment out the query below to hide connections
select * from #tokill

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(*)) FROM #tokill)
SET @PRINTUSRCOUNT = 'Attempting to disconnect ' + @USRCOUNT + ' Connections' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 10 seconds
WAITFOR DELAY '00:00:10' 

-- Begin cursor 
DECLARE CUSRKILL SCROLL CURSOR FOR 
SELECT SPID FROM #tokill 

OPEN CUSRKILL

FETCH FIRST FROM CUSRKILL INTO @spid 
SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
PRINT @KILLSTATE 
EXEC(@KILLSTATE ) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM Cusrkill into @spid 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
    PRINT @KILLSTATE 
    EXECUTE( @KILLSTATE ) 

    FETCH NEXT FROM Cusrkill INTO @spid 

  END 
END 
CLOSE Cusrkill 
DEALLOCATE Cusrkill 

--End cursor
PRINT 'Finished Killing IMA SPIDs'
DROP TABLE #tokill
26Jul/100
Query Table Sizes from a SQL Database

I pulled apart a few stored procedures and ended up with this code. Very nice way to query the row count and size for each table. Take a look at the code, it is simpler than you might think. To use, change the database name at the top of the script to any database you wish to query. I turned this into a really handy stored procedure. When I call the stored procedure I pass the database name. Possibilities are endless.

use master
go

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 

INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
        SET @table_name = REPLACE(@table_name, ']',''); 

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
        END
        
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname 
FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

The results look like this:

Looks great.

14Dec/090
Finding Duplicate Rows in SQL

I used this to solve a problem today.

// Finding duplicates in a table

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

// Find rows that occur exactly once

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1

// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause

select city_name
from areas
group by city_name
having count(*) > 1

// multiple columns in MySQL:
SELECT PL_CONTACT_FK, PL_PHONE_FK, COUNT(*) as DuplicateCount FROM PHONE_ALTERNATE GROUP BY PL_CONTACT_FK, PL_PHONE_FK HAVING DuplicateCount > 1;

2Aug/091
Transfer SQL Logins to Another SQL 2005 Server

After you move a database from one instance of SQL 2005 to another instance of SQL 2005, the users may not be able to log in to the database on the new server. Additionally, the users may receive the following error message: Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456).

This problem occurs because you did not transfer the logins and the passwords from the old instance of SQL 2005 to the new instance.

To transfer the logins and the passwords from one instance of SQL 2005 to another, follow these steps:

  1. On the old server, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
  2. Open a New Query, and then run the following script. The script will create a stored procedure that can be scheduled.
    -- this script is intended for a SQL 2005 server 
    -- the execute script will NOT work on a SQL 2000 server
    -- begin script, create sp_help_revlogin procedure
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
      DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
        @binvalue varbinary(256),
        @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    
    SELECT @hexvalue = @charvalue
    GO
     
    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
      DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary  varbinary (256)
    DECLARE @PWD_string  varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr  varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)
    
    DECLARE @defaultdb sysname
     
    IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name  'sa'
    ELSE
      DECLARE login_curs CURSOR FOR
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs
    
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE (@@fetch_status  -1)
    BEGIN
      IF (@@fetch_status  -2)
      BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group
    
          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
        END
        ELSE BEGIN -- SQL Server authentication
            -- obtain password and sid
                SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
     
            -- obtain password policy state
            SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
     
                SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
    
            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            END
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
            END
        END
        IF (@denylogin = 1)
        BEGIN -- login is denied access
          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        END
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        END
        PRINT @tmpstr
      END
    
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
       END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    -- end script

    After the stored procedure has been created, run the sp_help_revlogin stored procedure:

    EXEC sp_help_revlogin
  3. The results output a script that can be run on any SQL server to create the users. An example of the output is as follows. If you output the results to a script, you will first remove the [SQLSTATE 01000] from each line and execute:
    -- Login: UserName [SQLSTATE 01000]
    CREATE LOGIN [UserName] WITH PASSWORD = 0x00AAA0000000000AAAAAAAAAAAA000A00000000A00AAAA0AAAAAAA0A0AA HASHED, SID = 0xAA0000A00AA0A0AA0AAAAAAA00000AAA0A, DEFAULT_DATABASE = [DatabaseName], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF [SQLSTATE 01000]
  4. If the login already exists, the password can be repaired by altering the script:
    -- Login: UserName [SQLSTATE 01000]
    ALTER LOGIN [UserName] WITH PASSWORD = 0x0000A000AAAAAAA0AAA000A0A0AA HASHED

The stored procedures can also be scheduled and used for disaster recovery. I usually schedule this stored procedure as another task within the master database backup and output the results to a script.

Note: If there are any warnings or errors, correct them with instructions provided in Microsoft’s reference document. The link is listed above.

Reference: http://support.microsoft.com/kb/918992/

26May/090
How to Repair a MySQL Database

If a server is shutdown unexpectedly (power outage or hardware failure), one or more MySQL tables can be locked open and may need to be repaired.

Browse to the folder that contains the MySQL databases:

cd /var/lib/mysql/

Check your tables by running the following command:

myisamchk *.MYI

The easiest and safest method is to run the following command (-r means "recovery mode" and -q means "quick recovery mode"). This command will not touch the data within the database:

myisamchk -r -q tbl_name

A more difficult repair to run is as follows. This command will delete damaged data:

myisamchk -r tbl_name

Reference http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html for more information.

21Dec/080
Inactive Replication Subscriptions

If database replication has been disconnected for too long, the subscription may become inactive. The error message may read: "The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. The step failed."
To fix the problem, you can either delete the subscription and recreate, or reactivate the subscription. Obviously it is easier to reactivate the subscription. Here's how (run this script on the subscriber against the master database):


-- this script will reinitiate inactive subscriptions after they have expired.
-- This happens if the server was turned off for a long period of time (more than 72 hours)

select status,* from distribution..MSsubscriptions
update distribution..MSsubscriptions set status=2

After running the update statement, refresh the jobs and make sure the status on the REPL-Distribution job reads Executing: 2 (Run agent).