ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO
SELECT GETDATE() 'Today'
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
SELECT @MyDate = DATEADD(dd,0, DATEDIFF(dd,0,@MyDate))
DATEADD(dd,0, DATEDIFF(dd,0,@StartDate))
DATEADD(millisecond, -10, DATEADD(dd,0, DATEDIFF(dd,0,@EndDate)))
osql –E –S [server] –d [database] –I [script] –o [log]
DBCC SHRINKFILE([database log], 2)
BACKUP LOG [database] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([database log], 2)
USE [database]
EXEC sp_change_users_login ‘auto_fix’, ‘[username]’
EXEC sp_change_users_login ‘report’
SELECT LineAccountID, COUNT(LineAccountID)
FROM dbo.AccountActuals
GROUP BY LineAccountID, MonthYear
HAVING (COUNT(LineAccountID) > 1)
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
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 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
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
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE [YourDatabase] SET MULTI_USER
[SQL Server Home]\binn\sqlservr –s[instance] –m
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
ALTER TABLE [table] NOCHECK CONSTRAINT ALL
SELECT
'ALTER TABLE ' + [table] + ' CHECK CONSTRAINT ALL'
FROM sysobjects so
WHERE xtype = 'u'
=iif(RowNumber(Nothing) Mod 2,"Khaki","White")
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
="Created on: " & Format(Globals.ExecutionTime, "F")
mysqldump –u root -p [db_name] > [backup-file.sql];
mysql –u root –p [db_name] < [backup-file.sql];
create database [db_name];
grant usage on [db__name].* to [username]@localhost identified by ‘[password]’;
grant all privileges on [db_name].* to [username]@localhost;