Set SINGLE_USER Mode

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Set MULTI_USER Mode

ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO

Date Functions

Today

SELECT GETDATE() 'Today'

Yesterday

SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'

Last Day of Current Week`

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'

First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'

First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'

Last Day of Current Month

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'

First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'

Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'

First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'

Last Day of Current Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'

First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'

Last Day of Last Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

DATETIME to Midnight

SELECT @MyDate = DATEADD(dd,0, DATEDIFF(dd,0,@MyDate))

Start Date

DATEADD(dd,0, DATEDIFF(dd,0,@StartDate))

End Date

DATEADD(millisecond, -10, DATEADD(dd,0, DATEDIFF(dd,0,@EndDate)))

Execute a large SQL script

osql –E –S [server] –d [database] –I [script] –o [log]

Shrink logs

DBCC SHRINKFILE([database log], 2)
BACKUP LOG [database] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([database log], 2)

Fix orphaned accounts

USE [database]
EXEC sp_change_users_login ‘auto_fix’, ‘[username]’
EXEC sp_change_users_login ‘report’

Find Duplicates

SELECT LineAccountID, COUNT(LineAccountID)
FROM dbo.AccountActuals
GROUP BY LineAccountID, MonthYear
HAVING (COUNT(LineAccountID) > 1)

Value Separated List

DECLARE @email NVARCHAR(MAX)

SELECT @email = COALESCE(@email + ';' + email, email)
FROM ictickets.horse_res
WHERE (NOT email IS NULL OR LEN(email) > 0)

select @email

CSV from query

Use ‘NOCOUNT’ option.
Use a select statement to add a header and ‘UNION ALL’ to make it the first line in the output.
Uses COALESCE to assign default values to ‘NULL’ columns with null values.

SET NOCOUNT ON;
GO
SELECT 'OrderID,Tracking Number' AS [csv]

UNION ALL

SELECT  COALESCE(CAST([shipment].OrderID AS VARCHAR(10)), '') + ',' +                     COALESCE([shipment].TrackingNumber, '')  AS [csv]
FROM ...

osql -S [server instance] -d [database] -U [username] -P [password] -i [input query] -o [output file] -n -h-1 -r -w 700

UPDATE from SELECT

UPDATE o
SET o.StatusID = 5
FROM
  dbo.tblSFCOrder AS o
  INNER JOIN tblSFCOrderTxn ort
    ON o.OrderID = ort.OrderID
    AND o.ClientNumber = 1243
    AND o.StatusID = 4

TRANSACTION
BEGIN TRY
   BEGIN TRANSACTION
   [Statement]
   COMMIT
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0
      ROLLBACK
   [Handle Error]
END CATCH

RESTORE BACKUP

USE [master]
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [MyDatabase]
FROM DISK = 'C:\mydatabase.bak'
WITH MOVE 'MyDatabase' TO ‘..\Data\MyDataqbase.mdf',
MOVE 'MyDatabase_log' TO '..\Data\MyDatabase.ldf'
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO

Get only Date from DateTime

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Kill All Active Connections to Database

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
--do you stuff here
ALTER DATABASE [YourDatabase] SET MULTI_USER

Start MS SQL Server in Single-User Mode

1. Stop SQL Server and SQL Server Agent
2. [SQL Server Home]\binn\sqlservr –s[instance] –m

Add Missing Windows Account to MS SQL Server

Start MS SQL Server in single-user mode.
sqlservr.exe -m -s <instancename>
SQLCMD –S localhost –E
CREATE LOGIN [MACHINE\username] FROM WINDOWS
GO
EXEC sp_addsrvrrolemember ‘MACHINE\username’,’sysadmin’
GO
SHUTDOWN

Drop Constraints

ALTER TABLE [table] NOCHECK CONSTRAINT ALL

Add Constraints

SELECT
'ALTER TABLE ' + [table] + ' CHECK CONSTRAINT ALL'
FROM sysobjects so
WHERE xtype = 'u'

SSRS

Alternating Row Background Color

=iif(RowNumber(Nothing) Mod 2,"Khaki","White")

Page 1 of 5

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

Created On 12/20/2010 9:34 AM

="Created on: " & Format(Globals.ExecutionTime, "F")

MySQL

Backup database

mysqldump –u root -p [db_name] > [backup-file.sql];

Run SQL script (a.k.a. restore database)

mysql –u root –p [db_name] < [backup-file.sql];

Create Database

create database [db_name];

Create User and Grant Access to Database

grant usage on [db__name].* to [username]@localhost identified by ‘[password]’;
grant all privileges on [db_name].* to [username]@localhost;