Code Snippets
Having a Numbers and/or Dates Table in your database often makes queries much easier to put together without a bunch of mental gymnastics.
DECLARE @EndNum INT = 1000000;
SELECT TOP (@EndNum + 1)
Num = IDENTITY(INT, 0, 1)
INTO #Numbers
FROM
(SELECT TOP (1000) NotUsed = 1 FROM sys.columns) AS [1K] -- up to 1K
CROSS JOIN
(SELECT TOP (1000) NotUsed = 1 FROM sys.columns) AS [1M] -- up to 1M
CROSS JOIN
(SELECT TOP (1000) NotUsed = 1 FROM sys.columns) AS [1B] -- up to 1B
;
ALTER TABLE #Numbers
ADD CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Num);
SELECT MinNum = MIN(Num),
MaxNum = MAX(Num),
NumRows = COUNT(*)
FROM #Numbers;
DECLARE @EarliestDateYouNeed DATE,
@LatestDateYouNeed DATE;
SELECT @EarliestDateYouNeed = '2000-01-01',
@LatestDateYouNeed = GETDATE();
SELECT Num,
DATEADD(MONTH, Num, '1900-01-01') Month
FROM #Numbers
WHERE Num
BETWEEN DATEDIFF(MONTH, 0, @EarliestDateYouNeed) AND DATEDIFF(MONTH, 0, @LatestDateYouNeed);
Backup and Restore
BACKUP DATABASE MyDB
TO
DISK = N'D:\MyDB.bak'
WITH
NOFORMAT
, NOINIT
, NAME = N'MyDB-Full Database Backup'
, SKIP
, STATS = 10;
GO
USE master;
GO
RESTORE FILELISTONLY
FROM DISK = N'D:\Temp\backup.bak'
WITH
FILE = 1;
GO
USE master;
GO
RESTORE DATABASE myDB
FROM DISK = N'D:\Temp\backup.bak'
WITH
REPLACE
, FILE = 1
, MOVE N'myDB'
TO N'D:\MSSQL\data\myDB.mdf'
, MOVE N'myDB_log'
TO N'D:\MSSQL\log\myDB_log.ldf'
, STATS = 5;
GO
USE master;
GO
RESTORE DATABASE myDB
FROM DISK = N'D:\Temp\backup.bak'
WITH
FILE = 1
, MOVE N'myDB'
TO N'D:\MSSQL\data\myDB.mdf'
, MOVE N'myDB_log'
TO N'D:\MSSQL\log\myDB_log.ldf'
, STATS = 5;
GO
USE master;
GO
SELECT
requests.session_id AS SPID
, requests.command
, query.text AS Query
, requests.start_time
, requests.percent_complete
, DATEADD(SECOND, requests.estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests AS requests
CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) AS query
WHERE requests.command IN ( 'BACKUP DATABASE', 'RESTORE DATABASE' );
GO
Stored Procedure Template
USE DBName;
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.MyStoredProc
@Parameter1 NVARCHAR(4000) = NULL
, @Parameter2 BIT = 1
, @Parameter3 BIT = 0
, @Parameter4 TINYINT
, @Parameter5 DATETIME2(0)
, @Parameter6 VARCHAR(50)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
/* Queries go here! */
END;
GO
SQL Agent Job Scheduling
USE msdb;
GO
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
IF NOT EXISTS (
SELECT syscategories.name
FROM msdb.dbo.syscategories
WHERE syscategories.name = N'Database Maintenance'
AND syscategories.category_class = 1
)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB' ,
@type = N'LOCAL' ,
@name = N'Database Maintenance';
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
END;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Ssrs Wake-Up Call Job' ,
@enabled = 1 ,
@notify_level_eventlog = 2 ,
@notify_level_email = 0 ,
@notify_level_netsend = 0 ,
@notify_level_page = 0 ,
@delete_level = 0 ,
@description = N'Frequently Ssrs Wake-Up Call with a PowerShell Script.' ,
@category_name = N'Database Maintenance' ,
@owner_login_name = N'xxx\xxxxxx' ,
@job_id = @jobId OUTPUT;
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId ,
@step_name = N'Ssrs Wake-Up Step Reports-d' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 1 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 1 ,
@retry_interval = 0 ,
@os_run_priority = 0 ,
@subsystem = N'PowerShell' ,
@command = N'[string] $url = "https://xxxxx.xxxx.xxxx.xxxx/Reports";
[System.Net.WebClient] $wc = New-Object System.Net.WebClient;
$wc.UseDefaultCredentials = $true;
$result = $wc.DownloadString($url);
$wc.Dispose();' ,
@database_name = N'master' ,
@flags = 0 ,
@proxy_name = N'Run SQL Automation';
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId ,
@start_step_id = 1;
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId ,
@name = N'Ssrs Wake-Up Call Schedule' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 4 ,
@freq_subday_interval = 15 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 0 ,
@active_start_date = 19900101 ,
@active_end_date = 99991231 ,
@active_start_time = 1500 ,
@active_end_time = 235959;
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId ,
@server_name = N'(local)';
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF ( @@TRANCOUNT > 0 )
ROLLBACK TRANSACTION;
EndSave:
GO
Excel modules??? What are these doing here!
Sub GetSheets()
Path = "C:\[PATH TO FILES]"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next
wbkSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If
Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub