IIS 7 401 Error - Unauthorized: Access is denied due to invalid credentials

I thought this one was going to drive me crazy. I set up a web application on a Windows Server 2008 using IIS 7 and ASP.NET 4. I set it up to use Window Authentication but I just kept getting a 401 error - which made no sense. Everything on the network was windows-based. There should have been no problem finding my credentials. I was finally lucky enough to come across this on the web:

http://social.technet.microsoft.com/Forums/en-US/winserversecurity/thread/c9239a89-fbee-4adc-b72f-7a6a9648331f/

Below are the steps to stop this from happening:

• Open IIS and select the website that is causing the 401
• Open the "Authentication" property under the "IIS" header
• Click the "Windows Authentication" item and click "Providers"
• The issue should be that Negotiate is above NTLM. Move the NTLM to the top spot and that should fix it.

REPLACE() VS RTRIM() and LTRIM()

   I recently had a VERY interesting experience while cleaning up some data. I had about 8GB of SQL Server table data - all nvarchar(max) stuff. The tables needed to be compared to find the differences so the data really did need to be cleaned up. For a while I used an UPDATE statement that contained the REPLACE() function in it. It worked well, but took over an hour to complete. I began to think that there HAD to be a better way to do this. All I was really concerned about was trimming white space off the front and end of the field. I thought I would try a combination of the RTRIM() and LTRIM() functions just to see what would happen like so:

UPDATE table
SET field1 = LTRIM(RTRIM(field1))


   This one simple change shaved 40 minutes off my job! Why? Because REPLACE() has to crawl across each field character by character to find each what needs to be replaced. The TRIM functions are only concerned about white space at the beginning and end of the field and so, in this case, they are much faster. There is nothing quite like using the right tool for the job. Many thanks to a very wise DBA for explaining what was going on.

Delete All Tables but Keep the Database

I needed to delete all the tables from a database this afternoon but keep the database itself. I managed to dig up this little snippet by Googling and it worked like a charm:


EXEC sp_MSforeachtable 'DROP TABLE ?'


First Normal Form (1NF)

A table is considered to be in 1NF if:

1. There is no top-to-bottom ordering to the rows
2. There is no left-to-right ordering to the columns
3. There are no duplicate rows
4. Every row-and-column intersection contains exactly one value^*
5. All columns are regular (no hidden components)

A table without a unique key (meaning that duplicate rows could exist) or a table with a nullable column (a violation of condition 4) would not be in 1NF.


^*Condition 4, which does not allow repeating groups, is thought by many to be the defining feature of 1NF.

DB Mirror Failover Script

I had to manually failover about 25 principals to their mirror partners. After about 20 mouse clicks I thought "There HAS to be a better way" and began to Google. Behold:

ALTER DATABASE [Database Name] SET PARTNER FAILOVER

Be mindful - this can ONLY be done from the principal, not the mirror. If the principal is unavailable, the mirror can be forced to start up, but there is a possibility of data loss:

ALTER DATABASE [Database Name] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Credit where credit is due: http://www.sqlservercentral.com/Forums/Topic829941-1549-1.aspx

Stored Procedure to get File Info from the Master DB

I just realized that I had not published this before. Here is the sp I use to get all the info I need regarding files from available DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: K Griffith
-- Create date: 04 Feb 2011
-- Description: Returns the space consumed on the DB Server or generic info about each
-- DB on the server depending on the parameter passed. Only
-- returns DBs NOT participating in mirroring.
-- =============================================
CREATE PROCEDURE [dbo].[sp_FileSizes]

@TYPE NVARCHAR(10)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ID INT
DECLARE @MAX INT
DECLARE @DB_ID INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)
DECLARE @SQL1 NVARCHAR(500)

SET @ID = 5
SET @MAX = (SELECT MAX(database_id) FROM sys.databases)

CREATE TABLE #temp(DBName nvarchar(300), Name nvarchar(300), Available decimal(18,2), Used decimal (18,2),
Type nvarchar(5), FileID int)

WHILE @ID <= @MAX 
 BEGIN 
  SET @DB_ID = (SELECT D.database_id 
                FROM sys.database_mirroring M 
                INNER JOIN sys.databases D 
                  ON M.database_id = D.database_id 
                WHERE M.mirroring_role_desc IS NULL 
                  AND D.database_id = @ID) 
  IF @DB_ID IS NOT NULL 
    BEGIN 
      SET @DB_NAME = (SELECT NAME 
                      FROM sys.databases
                      WHERE database_id = @ID)


      SET @SQL = 'USE [' + @DB_NAME + ']' 
      SET @SQL1 = ' INSERT INTO #temp (DBName, Name, Available, Used, Type,  
                     FileID) 
                  SELECT ''' + @DB_Name + ''' AS DBName, Name, 
                   size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'')  
                   AS int)/128.0 AS AVAILABLE, 
                   CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0  
                   AS USED, 
                     type = CASE Type 
                              WHEN 1 THEN ''Log'' 
                              ELSE ''Data''  
                             END, 
                   file_id 
                 FROM sys.database_files' 
              
      EXEC (@SQL + @SQL1) 
    END 
    SET @ID = @ID + 1 
   END 
     IF @TYPE = 'SPACE' 
       BEGIN 
         SELECT SUM(USED)/1024 AS [Total Space in GB] 
         FROM #temp 
       END 
     ELSE 
         SELECT * 
         FROM #temp 
         WHERE Type = @TYPE 
         ORDER BY Type ASC, Available DESC 


     DROP TABLE #temp 
  END

Retrieving a list of DBs that are NOT participating in mirroring

I have a stored procedure that I use on all my servers that queries the master table and retrieves all the data I need regarding each DB siting on the server. I can then decide which files need shrinking, which DB needs more space, etc. I was recently tasked with mirroring. My sp started to fail because a database on the mirrored server acting as a mirror is not available. I changed my query to only grab the IDs of DBs NOT participating in mirroring an viola! My sp worked again. Here's the query:

SELECT D.database_id
FROM sys.database_mirroring M
INNER JOIN sys.databases D
ON M.database_id = D.database_id
WHERE M.mirroring_role_desc IS NULL

I wish I was smart enough to have come up w/ this on my own, but such is not the case. Here's the "credit where credit is due": http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/ded375ce-1f09-4070-b588-3d3cb91ed41a

Guide to Mirroring Databases on SQL Server 2008 Enterprise

Guide to Mirroring Databases on SQL Server 2008 Enterprise

  One of the first things to make sure of is that the drive structure is the same on the principal server and the mirror server. For example, if data files are stored at S:\SqlData and log files are kept at L:\SqlLogs on the principal server, this exact same drive structure must be mimicked on the mirror server.
  Additionally, mirroring was attempted between a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise as the principal and a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise R2 as the mirror. The initial mirroring could be established and the failover could occur from the principal (SQL Server 2008 Enterprise) to the mirror (SQL Server 2008 R2 Enterprise). However, when a failover back to the principal from the mirror was attempted, the mirroring broke and could not be reestablished. The take away here is to make sure that both servers are running the exact same version of SQL Server (SQL Server 2008 Enterprise, for example).
  Lastly, keep in mind that mirroring is done at the database level as opposed to something like clustering which is done at the server level. Therefore, each database must be set up for mirroring individually.

1. Select a database for mirroring on the principal server. Right click on the database. Select ‘Tasks’ then ‘Back Up…’ Select the location to where you would like to back up the database. The backup will need to end up on the mirroring server and eventually be restored there.
2. On the mirror server in SSMS, right click on Databases underneath the server name and select ‘Restore Backup’. In the Restore Database window that opens, after you have selected a database name and the backup to be restored, click on ‘Options’ in the top left hand corner of the window. Make sure that the MDF and LDF files are named the same as they are on the principal. Also, check the ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’ option.
3. Go back to the principal server, select the same database, pick ‘Tasks’ and then ‘Back Up…’ again. On the Backup type dropdown, select ‘Transaction Log’. Make sure that the transaction log backup is in a location that can be accessed from the mirror so that it can be restored to the mirror.
4. Right click on the database on the mirror. The database should show as ‘(Restoring…)’ Select ‘Tasks’ -> ‘Restore’ -> ‘Transaction Log…’ Select the transaction log backup that you created from the principal. Select ‘Options’ in the ‘Restore Transaction Log’ window and click the radio button that states ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’. Then click the ‘OK’ button’
5. Go back to the principal server and right click on the database that you have selected for mirroring. Click ‘Tasks’ -> ‘Mirror…’ When the Database Properties window opens, click the ‘Configure Security’ button and go through the wizard.
6. If you wish to include a witness server, select the ‘Yes’ radio button in the second screen. Continue to go through the wizard. The databases that are being mirrored in this instance are all on servers on a private network behind a firewall, so DO NOT check the box for ‘Encrypt data sent through this endpoint’. Additionally, data encryption can cause failures if things are not set up properly. Unless it is absolutely necessary, do not encrypt the data.
7. The wizard will build endpoints on the principal, mirror, and witness servers. The default port for mirroring is 5022. On a Windows 2003 Server, if the Windows Firewall is not in use, no additional configuration should be necessary. However, if the Windows Firewall is in use on the server, the port may have to be specifically opened. By default on a Windows 2008 Server, all ports are locked down so 5022 will have to be specifically opened on a 2008 Servers.
8. If all goes well, there should be success messages beside each of the servers in the mirroring scheme if the endpoints get configured correctly. When the wizard concludes, select ‘Start Mirroring’ to finalize the mirroring set up. If no error messages appear, look at both the principal and mirrored databases in SSMS. The principal database should display a message stating ‘(Principal, Synchronized)’ next to the database name. The mirrored database should display a message that states ‘(Mirror, Synchronized / Restoring…)’ next to the database.

Additional Information
  If the mirrored databases are going to be used in an ASP.NET application, this information will need to be placed in the web.config file when the connection string is defined. The parameter is called 'Failover Partner' and it follows the Data Source parameter:


<add name="SqlCS" connectionString="Data Source=SqlPrincipal; Failover Partner=SqlMirror; Initial Catalog=master; Persist Security Info=False; User ID=user; Password=password123" providerName="System.Data.SqlClient" />


  Once again, Database Mirroring occurs at the database level, NOT the server level. This is important because the logins are created at the server level. Additionally, SQL Server uses a GUID (like 0xB7898239D38FA34D84F30B7404F5C67B) called an SID to identify a login, NOT the login name. So if we create the login ‘user123’ on both the principal and mirror servers and assign the same permissions to the mirrored database, when the database fails the login ‘user123’ will not work because the SIDs don't match on the servers. The following article presents a method to resolve this issue:
http://support.microsoft.com/kb/918992
On the principal server, open a new query window in SSMS and run the following query:

----------------------------------------- BEGIN QUERY ----------------------------------------------
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 QUERY -------------------------------------------------

This script will create 2 stored procedures in the master database: sp_hexidecimal and sp_help_revlogin. After the script has executed, run the following statement in a new query window:

EXEC sp_help_revlogin

The output will give you the ability to create logins with identical SIDs on the mirrored database. Here is a sample output from the script:

---- Login: User123
CREATE LOGIN [User123] WITH PASSWORD = 0x0100F6FF96F04564026C472234A73D7FD9B3CE467E6B9E066BBC
HASHED, SID = 0xCC1C8D1C6BF52640844476D7FFADB862, DEFAULT_DATABASE = [master],
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Run the above script on the mirror server and assign the same permissions to the user ID to the database that exist on the principal server and everything should work correctly.

Filtering by Time in a Query


SELECT CONVERT(VARCHAR(5),[fldDateTime], 108) AS Time, [fldDateTime]
FROM tblDateTimes
WHERE ((CONVERT(VARCHAR(5),[fldDateTime], 108) <= '06:00') OR (CONVERT(VARCHAR(5),[fldDateTime], 108) >= '18:00'))


Finding Duplicates with T-SQL


SELECT UserID, COUNT(UserID) AS Total
FROM tblUsers
GROUP BY UserID
HAVING COUNT(UserID) > 1


Randomly Ordering a ResultSet in SQL Server

-- I found this T-SQL function to be quite handy. It returns the records in a random order every time.

SELECT *
FROM myTable
ORDER BY NEWID()

Getting Access to the Response Object from a Regular C# Class (and NOT a code-behind)

When I attempt to call Response.Write() or Response.Redirect() from a genuine C# class (NOT a code-behind), I get an interesting error:

"Response is not available in this context."

However, call it this way and it should work fine:

HttpContext.Current.Response.Write("Hello, World!");

Query to Retrieve Uptime from a SQL Server


SELECT DATEDIFF(D, CrDate, GETDATE()) 'Days On Line'
FROM SysDatabases
WHERE Name = 'TempDb'


Getting Column Names from a Table in SQL Server


-- All table info
SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'tblName'



-- Column Info
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'tblName'


Html/Meta Object in C#

Don't forget to import System.Web.UI.HtmlControls;

//Waits for 10 seconds and then forwards to Google.com
HtmlMeta meta = new HtmlMeta();
meta.HttpEquiv = "Refresh";
meta.Content = "10; URL=http://www.google.com";
Page.Header.Controls.Add(meta);


Moving System Databases in SQL Server 2008

Great article on this. Follow it to the letter from start to finish though, or you could wind up with a non-functioning SQL Server:
Moving System Databases

Change the Default Database File Path


USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
REG_SZ, N'S:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog',
REG_SZ, N'L:\SQLLog'
GO

Default Database File Path

Code: 0x80012017 Description: The package path referenced an object that cannot be found

From the command line run this:

REGSVR32 "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll"

Make sure that the path in quotations is where the DTS.dll is stored.

Maintenance Plan Failure

Great Articles Explaining JOINs

http://www.sqlservercentral.com/articles/T-SQL/67941/

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Windows Server Uptime


Start -> Run -> cmd.exe
systeminfo | find "System Up Time"


INSERT INTO WHERE NOT EXISTS

-- If @UserID and @ServerID already exists
-- in table [tblSysAdmins], the values
-- will not be inserted

INSERT INTO [tblSysAdmins] ([UserID], [ServerID], [Authorize])
SELECT @UserID, @ServerID, @Authorize
WHERE NOT EXISTS
(SELECT *
FROM tblSysAdmins
WHERE [UserID] = @UserID
AND [ServerID] = @ServerID )

Query to get Users on SQL Server 2005

CREATE TABLE #TEMP(
UserName nvarchar(200),
GroupName nvarchar(50),
LoginName nvarchar(200),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID)


    IF @DB_NAME IS NOT NULL 
      BEGIN 
        SET @SQL = 'INSERT INTO #TEMP 
        EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
        EXEC (@SQL) 
      END 


  SET @ID = @ID + 1 
 END


 SELECT UserName 
 FROM #TEMP 
 WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
 GROUP BY UserName


 DROP TABLE #TEMP

Query to get Users from SQL Server 2008

CREATE TABLE #TEMP(
UserName nvarchar(100),
RoleName nvarchar(50),
LoginName nvarchar(50),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID) 


     IF @DB_NAME IS NOT NULL 
       BEGIN 
         SET @SQL = 'INSERT INTO #TEMP 
         EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
         EXEC (@SQL) 
       END 
   
     SET @ID = @ID + 1 
 END 


SELECT UserName 
FROM #TEMP 
WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
GROUP BY UserName 


DROP TABLE #TEMP

Query to Retrieve Job Info from msdb Database


SELECT
-- Format date
((SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 1,4) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 5,2) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 7,2)) + ' ' +

-- Format time
(
-- Hours
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12 THEN CONVERT(VARCHAR(20), (H.run_time/10000) -12)
WHEN CONVERT(VARCHAR(20), H.run_time/10000) = 0 THEN '12'
ELSE CONVERT(VARCHAR(20), H.run_time/10000)
END + ':' +

-- Minutes
CASE LEN(CONVERT(VARCHAR(20), H.run_time/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time/100%100)
ELSE CONVERT(VARCHAR(20), H.run_time/100%100)
END + ':' +

--Seconds
CASE LEN(CONVERT(VARCHAR(20), H.run_time%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time%100)
ELSE CONVERT(VARCHAR(20), H.run_time%100)
END

+ ' ' +

-- AM or PM
CONVERT(VARCHAR(3),
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12
THEN ' PM'
ELSE ' AM'
END

))) AS [Last Run Time],

S.NAME AS [Job Name], H.Message,

-- Format Status
CASE H.run_status
WHEN 1 THEN 'Success'
ELSE 'Failure'
END AS Status,

-- Format Run_Duration
(CASE LEN(CONVERT(VARCHAR(20), H.run_duration/10000))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/10000)
ELSE CONVERT(VARCHAR(20), H.run_duration/10000)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/100%100)
ELSE CONVERT(VARCHAR(20), H.run_duration/100%100)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration%100)
ELSE CONVERT(VARCHAR(20), H.run_duration%100)
END) AS [Run Duration]

FROM [msdb].[dbo].[sysjobhistory] AS H
INNER JOIN [msdb].[dbo].[sysjobs] AS S
ON S.job_id = H.job_id
WHERE S.enabled = 1
AND H.step_id = 0
ORDER BY H.RUN_DATE desc, H.RUN_TIME DESC


T-SQL's REPLACE() Function

DECLARE @VAR AS VARCHAR(50)
SET @VAR = 'DEF LEPPARD IS THE GREATEST ROCK BAND EVER!'
SELECT @VAR
-- USE W/ SET
SET @VAR = REPLACE(@VAR, 'DEF LEPPARD', 'METALLICA')
SELECT @VAR
-- USE W/ SELECT
SELECT REPLACE(@VAR,'METALLICA','LED ZEPPELIN')

-- LOOKS LIKE REPLACE() CAN ONLY BE USED IN EITHER A
-- SELECT STATEMENT OR WHEN SETTING A VARIABLE
-- http://msdn.microsoft.com/en-us/library/ms186862.aspx

Escaping or Inserting Single Quotes into a String in T-SQL

Just in case anyone ever needs to do this. This gave me a headache for about 30 minutes this morning:


DECLARE @MYVAR AS VARCHAR(100)
DECLARE @ESCAPE AS VARCHAR(100)
SET @MYVAR = 'PLACING SINGLE QUOTES INTO A STRING'
SET @ESCAPE = 'Single quote''s are escaped with double single quotes like these: '''''
SELECT @MYVAR AS MYVAR
SET @MYVAR = '''' + @MYVAR + ''''
SELECT @MYVAR AS MYVAR
SELECT @ESCAPE AS VAR

Adding a Constraint to a SQL Server DB field to ensure that the field contains a particular string

  This was an interesting problem. What if, for some strange reason, you wanted to make sure that a field contained a certain string or it didn't get inserted? This can be handled pretty easily on the front end, but just for fun, let's try doing it on the back end:

-- Unrealistic, I'm sure. Let's just say that we ONLY want people
-- with Gmail email addresses adding their emails into our table.

ALTER TABLE myTable
WITH CHECK ADD
CONSTRAINT [ValidEmail]
CHECK
(
Email LIKE '%@gmail.com'
)
GO

Randomly Order a Result Set in SQL Server

SELECT *
FROM myTable
ORDER BY NEWID()

SQL Server sp for Starting a Job

EXEC msdb.dbo.sp_start_job 'My_Job'

Query to List Recovery Model for all DBs on a SQL Server

SELECT DBID, [name],databasepropertyex([name],'Recovery') as [Recovery]
FROM master.dbo.sysdatabases
ORDER BY [name];