Disabling the annoying system beep!

The system beep is unaffected by windows volume control / sound card settings. If you're working late@ night and dont want to upset the missus, here is how to disable it >

Go to the dos prompt (START > RUN > type 'CMD' {Enter]
Run these 2 commands >
net stop beep [ENTER]
sc config beep start= disabled [ENTER]

Personally, i pasted those two command lines into a batch file and ran it on my machines (physical and virtual).

LDAP Error

Recording this as a memory jogger in case I see it again...

" Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "linkedservername".

Could not convert the data value due to reasons other than sign mismatch or overflow. "

Was retrieving data from Active Directory via a linked server in SQL.
Bottom line is that you cannot use OPENQUERY to get multivalued attributes or columns containing date information from AD.

Querying Active Directory Group membership from SQL

Querying Active Directory Group membership from SQL

Inspired from (& based on) code @ http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

This code looks at an LDAP v2 source eg; Windows 2000 and fetches users, groups and membership information.

You need to set up a linked server called 'ADSI' to get this to function.

Setting up the linked server -

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'

The code itself -

-- Configure AD Domain to search

DECLARE  @strDC           NVARCHAR(100)
SET @strDC = 'dc=domain,dc=co,dc=uk'

-- Declare Variables

DECLARE  @chvAlphaChars   VARCHAR(60),
         @chvSearch       VARCHAR(10),
         @chvSearchLevel1 VARCHAR(1),
         @chvSearchLevel2 VARCHAR(1),
         @chvSearchLevel3 VARCHAR(1),
         @intcountLevel1  INT,
         @intcountLevel2  INT,
         @intcountLevel3  INT,
         @intRowCount     INT,
         @strSQL          NVARCHAR(4000),
         @strADSISQL      NVARCHAR(4000),
   @Login     VARBINARY(85),
   @CN     VARCHAR(512),
   @CT     INT,
   @ADRoot    VARCHAR(255)

                        
-- Declare temp tables

IF EXISTS (SELECT *
     FROM   TEMPDB.DBO.SYSOBJECTS
     WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
  DROP TABLE #LDAP_AD_V2_USERS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
  [ROW_ID]          [INT]   IDENTITY ( 1,1 )   NOT NULL,
  [SID]             [VARBINARY](85)   NULL,
  [SAMACCOUNTNAME]  [NVARCHAR](256)   NULL,
  [CN]              [NVARCHAR](256)   NULL,
  [SN]              [NVARCHAR](256)   NULL,
  [DISPLAYNAME]     [NVARCHAR](256)   NULL,
  [GIVENNAME]       [NVARCHAR](256)   NULL,
  [TELEPHONENUMBER] [NVARCHAR](256)   NULL,
  [ADSPATH]         [NVARCHAR](256)   NULL,
  [HOMEDIRECTORY]   [NVARCHAR](256)   NULL,
  [MAIL]            [NVARCHAR](256)   NULL,
  [MEMBEROF]        [NVARCHAR](256)   NULL,
  [PRIMARYGROUPID]  [INT],
  [CREATETIMESTAMP] DATETIME)
ON [PRIMARY]

IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
     DROP TABLE #LDAP_AD_V2_GROUPS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
  [ID]          [INT]   IDENTITY ( 1,1 )   NOT NULL,
  [LOGIN]       [VARCHAR](512)   NULL,
  [EMAIL]       [VARCHAR](255)   NULL,
  [ACCTNAME]    [VARCHAR](512)   NULL,
  [DISTNAME]    [VARCHAR](512)   NULL,
  [CREATEDDATE] [DATETIME]   NULL,
  [CHANGEDDATE] [DATETIME]   NULL,
  [MGR]         [VARCHAR](512)   NULL,
  [SID]         [VARBINARY](85)  NULL)
ON [PRIMARY]

IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
     DROP TABLE #LDAP_AD_V2_USERGROUPS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
  [USERLOGIN]  [VARBINARY](85)   NOT NULL,
  [GROUPLOGIN] [VARBINARY](85)   NULL)
ON [PRIMARY]

-- POPULATION OF USERS

-- Search letters to cycle through             
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON

-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
  BEGIN
    -- get first level char
    SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)                           
    -- reset start on space
    SET @intcountLevel2 = 1                          
    -- second level loop
    WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
      BEGIN
        -- reset start on space
        SET @intcountLevel3 = 1
        -- third level loop
        WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
          BEGIN
            -- setup the string to search for. By using the trim function we can form each level depending on no records
            -- eg A 99, B 1000 > BA 9, BB 20 etc
            -- trim the spaces forming just A, B, C ; AA, AB for search etc
            SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
            SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
            SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
   SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
            SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp ' 
       + CHAR(13) + 'from  ''''LDAP://' + @strDC + '''''  ' 
       + CHAR(13) + 'where objectCategory = ''''Person''''  ' 
       + CHAR(13) + 'and   objectClass = ''''user'''' ' 
       + CHAR(13) + 'and   sAMAccountName = ''''' + @chvSearch + '*'''' '                                                                                                                                                                                                                                                                                                                                                                                                      
            SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp) ' 
       + CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp ' 
       + CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) ' 
       + CHAR(13) + 'order by sAMAccountName'
            
            EXEC SP_EXECUTESQL @strSQL
              
            SET @intRowCount = @@ROWCOUNT
                               
            -- prints what string is being searched for : no of inserts
            -- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)
            
            -- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
            IF @intRowCount < 1000
               AND @chvSearchLevel2 = ''
              SET @intcountLevel2 = @intcountLevel2 + 100
                                                      
            -- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
            IF @intRowCount < 1000
               AND @chvSearchLevel3 = ''
              SET @intcountLevel3 = @intcountLevel3 + 100
                                                      
            -- else over 1000 so increment third level
            SET @intcountLevel3 = @intcountLevel3 + 1
          END
          
        -- increment next second level char
        SET @intcountLevel2 = @intcountLevel2 + 1
      END
      
    -- increment next first level char
    SET @intcountLevel1 = @intcountLevel1 + 1
  END
  

-- POPULATION OF GROUPS

SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail ' 
       + CHAR(13) + 'FROM ''''LDAP://' + @strDC + '''''  ' 
       + CHAR(13) + 'WHERE objectCategory = ''''Group'''' '
                                                                                                                                                                                                                                                                                                                           
SET @strSQL  = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) ' 
      + CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName ' 
      + CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) ' 
      + CHAR(13) + 'order by sAMAccountName'

-- PRINT @strSQL
EXEC SP_EXECUTESQL  @strSQL
  

-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE
                 
IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#CT'))
  DROP TABLE #CT
  
-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))

-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR  FOR
SELECT   SID,
         DISTNAME
FROM     #LDAP_AD_V2_GROUPS
ORDER BY LOGIN

OPEN CURGROUPS

FETCH NEXT FROM CURGROUPS
INTO @Login,
     @CN

WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Empty the temp table
    TRUNCATE TABLE #CT
    
    -- Build a SQL statement to insert the SID values directly from the linked server into the temp table
    SET @strsql = 'INSERT #CT
    SELECT *
    FROM OPENQUERY (
    ADSI,
    ''SELECT objectSid
    FROM ''''LDAP://' + @strDC + '''''  
    WHERE objectCategory = ''''User''''
    AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'
    
    EXEC( @strsql)
    
    -- Select the number of records inserted. If this value is less than 1000 then there is no need
    -- to execute the OLE calls, and we simply copy the values into the correlation table.
    SELECT @CT = COUNT(* )
    FROM   #CT

    --PRINT @ct    
    --PRINT @strsql
    
    IF @CT <> 0
      BEGIN
        INSERT #LDAP_AD_V2_USERGROUPS
              (USERLOGIN,
               GROUPLOGIN)
        SELECT CT,
               @Login
        FROM   #CT
        WHERE  CT IS NOT NULL
      END
      
    FETCH NEXT FROM CURGROUPS
    INTO @Login,
         @CN
  END
  
CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT

/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/

-- Final Query to tie together the 3 tables and produce a report of group membership

SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
       #LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM     #LDAP_AD_V2_USERGROUPS
         INNER JOIN #LDAP_AD_V2_USERS
           ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
         INNER JOIN #LDAP_AD_V2_GROUPS
           ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
         #LDAP_AD_V2_USERS.SAMACCOUNTNAME

SQL 101 : Date Formatting

Casting a value as DateTime validates it -

-- SQL DateTime Validation
SELECT CAST('2006-03-31' AS datetime) -- Succeeds, 31 days in March
SELECT CAST('2006-04-31' AS datetime) -- Fails, only 30 days in April

SELECT CAST('2006-02-29' AS datetime) -- Fails, No 29th day in Feb 2006
SELECT CAST('2004-02-29' AS datetime) -- Succeeds, was a leap year
SELECT CAST('2002-02-29' AS datetime) -- Fails, No 29th day in Feb 2002
SELECT CAST('2000-02-29' AS datetime) -- Succeeds, was a leap year

When language is set to us_english, date format of 'mdy' expects the month to be provided before the day.

SET LANGUAGE us_english  -- Changed language setting to us_english.
SELECT CAST('2006-03-31' AS datetime) -- works
SELECT CAST('03-31-2006' AS datetime) -- works
SELECT CAST('31-03-2006' AS datetime) -- fails

When language is set to british, date format of 'dmy' expects the day to be provided before the month.

SET LANGUAGE british  -- Changed language setting to British.
SELECT CAST('2006-03-31' AS datetime)  -- fails
SELECT CAST('2006-31-03' AS datetime)  -- works
SELECT CAST('31-03-2006' AS datetime) -- works

In both of these cases, SQL correctly interprets the year, whether at the start or end of the string.
The failures generate -
Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Using set dateformat to override the language setting -

SET DATEFORMAT 'dmy'
-- example
SET LANGUAGE us_english  -- Changed language setting to us_english.
SET DATEFORMAT 'dmy'
SELECT CAST('31-03-2006' AS datetime) -- now succeeds.

Best Practice is to use a language neutral date format -

-- ISO 8601 format doesnt care about language >
SELECT CAST('2006-03-31T00:00:00' AS datetime)
-- Neither does removing the '-'
SELECT CAST('20060331' AS datetime)

More on language neutral date formats here - http://www.karaszi.com/SQLServer/info_datetime.asp

2 ways to get the current time & date -

SELECT CURRENT_TIMESTAMP
SELECT getdate()

FTP Server : Filezilla

I originally needed a ftp server for testing ftp functionality in an application. Filezilla Server was recommended to me as quick, easy and most importantly FREE!


Download it here > http://sourceforge.net/projects/filezilla/


They also do a free FTP client hence I'm ditching SmartFTP for Filezilla >


Yes, I know you can ftp from within I.E and the OS, but this is the real world, where usability and reliability rule :)

SQL : Make all columns NULLable

SELECT 'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' ' + data_type + '(' + CAST(CHARACTER_MAXIMUM_LENGTH as VARCHAR(5)) + ') NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'NO'
AND DATA_TYPE IN ('VARCHAR','CHAR')

Dynamic SQL - Passing a parameter using sp_execute

/* 
Dynamic SQL : Passing a parameter to sp_execute
Example uses AdventureWorks db
*/

exec sp_executesql N'SELECT 
      e.[EmployeeID]
      ,c.[Title]
      ,c.[FirstName]
      ,c.[MiddleName]
      ,c.[LastName]
      ,c.[Suffix]
      ,e.[Title] AS [JobTitle] 
      ,c.[Phone]
      ,c.[EmailAddress]
      ,c.[EmailPromotion]
      ,a.[AddressLine1]
      ,a.[AddressLine2]
      ,a.[City]
      ,sp.[Name] AS [StateProvinceName] 
      ,a.[PostalCode]
      ,cr.[Name] AS [CountryRegionName] 
      ,c.[AdditionalContactInfo]
     FROM 
      [HumanResources].[Employee] e
     INNER JOIN [Person].[Contact] c 
       ON c.[ContactID] = e.[ContactID]
     INNER JOIN [HumanResources].[EmployeeAddress] ea 
       ON e.[EmployeeID] = ea.[EmployeeID] 
     INNER JOIN [Person].[Address] a 
       ON ea.[AddressID] = a.[AddressID]
     INNER JOIN [Person].[StateProvince] sp 
       ON sp.[StateProvinceID] = a.[StateProvinceID]
     INNER JOIN [Person].[CountryRegion] cr 
       ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
         AND cr.[Name] = @Region',N'@Region varchar(20)','Germany'

Send email from TSQL

SQL 2005 Database Mail, Sending email from TSQL -
This script assumes you have set up database mail with an account name of 'SQL Administrator' and provided valid SMTP details.

-- declare variables

DECLARE @chvFrom VARCHAR(255)
DECLARE @chvTo VARCHAR(255)
DECLARE @chvSubject VARCHAR(255)
DECLARE @chvBody VARCHAR(8000)

-- set values

SET @chvFrom = 'SQLAdmin@mydomain.net'
SET @chvTo = 'recipient@mydomain.net'
SET @chvSubject = 'Test email from ' + @@SERVERNAME
SET @chvBody = 'test body text'

-- send the mail

EXEC msdb.dbo.sp_send_dbmail @profile_name='SQL Administrator', @recipients=@chvTo, @body=@chvBody,@subject=@chvSubject, @importance='High'

ISO Image Tools

Not so long ago, I was swimming in discs. Software and OS discs each on CD, neatly filed away in multiple folders. I was beginning to rival an MSDN subscription in terms of volume when I had a sort out, binned anything that had been superceeded and consolidating the rest to DVD.

The latest updates and installers are taking the form of ISO files i.e. image files of installation media. These are the FREE tools I rate for dealing with ISO files.

Daemon Tools - Emulates an optical drive so you can use an image without burning to disc.

UPDATE May 2010

Virtual Clonedrive is better and doesn't prompt you to install lots of add-ins / extras.

DoISO - Create ISO files from a given directory.

ImgBurn - Burns ISOs to disc and extracts images from discs. Especially good with 'difficult' discs. Used to be known as DVD Decrypter.

SQL 2005+ - TRY CATCH Error detection

Prior to SQL 2005, errors in TSQL code had to be tested for and captured by @@ERROR.
SQL 2005 implements the TRY CATCH syntax in the same way as javascript, c++ and subsequently the .NET languages.

For example, using AdventureWorks, I purposely attempt to delete a record I shouldnt, and receive an error -

DELETE Person.Address WHERE AddressID = 1

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.

@@ERROR only contains the error in the very next statement after the error, hence to both examine & display the value I have to assign it to a local variable -

TSQL Programming to catch the error -

DECLARE @ErrorResult INTEGER
DELETE Person.Address WHERE AddressID = 1
SET @ErrorResult = @@ERROR
IF @ErrorResult <>0
 BEGIN
  PRINT 'Error ' + CAST(@ErrorResult AS VARCHAR(10)) + ' : Could not Delete record'
 END

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.
Error 547 : Could not Delete record

Note : both the SQL Error and my message are returned.

Using TRY/CATCH however, the error can be caught and the script continues -

BEGIN TRY
   -- Attempt delete of referenced record.
    DELETE Person.Address WHERE AddressID = 1
END TRY
BEGIN CATCH
    -- Do alternate action.
    PRINT 'Could not Delete record'
END CATCH

Much tidier, only my error message is returned -

Error 547 : Could not Delete record

Accessing AD Users & Computers on a Member Server

Launch Microsoft Management Console
Start > Run > MMC [enter]

File > Add / Remove Snap-In
Click 'Add' to see available snap-ins.

Select 'Active Directory Users and Computers'
Click the 'Add', then 'Close' and finally 'OK'.

The functionality available will obviously depend on who you are logged in as.

Removing Database Backup History

The msdb database stores (amongst other things) the history of backups performed on the sql instance.
Once a backup has been archived, i.e. moved away from the location it was originally written to, there is no need to keep the record of the backup. Infact, these records build up, increasing the size of the msdb database (quickly if frequent transaction log backups are performed).

To see the date range of backup history you have, us this query -

use msdb
SELECT 
  a.name
, MIN(b.backup_finish_date) EarliestSuccessfulBackup
, MAX(b.backup_finish_date) LatestSuccessfulBackup
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name
ORDER BY a.name

To prevent this occuring, use system stored procedure sp_delete_backuphistory and pass it the date of the oldest record you want to keep.

use msdb
go
exec sp_delete_backuphistory '1/1/2006'

If a database is removed altogether, zap it's backup history like this -

exec msdb.dbo.sp_delete_database_backuphistory 'Adventureworks'

If backups are archived regularly, schedule a job to remove excess history records, like this -

-- Calculate date to be used when removing records,
-- This example deletes records over a month old.
DECLARE @dtOldest_date DATETIME
SET @dtOldest_date = dateadd(month, -1, getdate())
EXEC msdb..sp_delete_backuphistory @dtOldest_date

NB :SQL 2005+ addresses this by providing cleanup tasks in the Maintainence Plans (if you use them).