Reporting - Default dates

When writing SSRS reports, we always have the need to set default dates for reports.  Usually prior week, or prior month.  Sometimes they are current week to date, or current week to month.  Whatever your default reporting needs are, here is a list that should get you started.



DECLARE @Date datetime = GETDATE()

-- Start of Month
SELECT DATEADD(m, DATEDIFF(m, 0, @Date), 0) [Start of Month]
-- End of Month
SELECT DATEADD(d,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) [End of Month]
-- Start of Week
SELECT DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)) [Start of Week]
-- End of Week
SELECT DATEADD(d,-1,DATEADD(WEEK,1,DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)))) [End of Week]

-- Start of Prior Month
SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)) [Start of Prior Month]
-- End of Prior Month
SELECT DATEADD(d,-1,DATEADD(m,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)))) [End of Prior Month]
-- Start of Prior Week
SELECT DATEADD(WEEK,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [Start of Prior Week]
-- End of Prior Week
SELECT DATEADD(d,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [End of Prior Week]

SQL Agent Jobs without notifications

All of our SQL Agent Jobs should notify someone if a failure occurs.  Below is a query to find all SQL Agent Jobs that are enabled, and do NOT have an email notification.  Quickly dissecting the script below, we are looking for [notify_level_email] NOT IN (1, 2, 3).

1 = When the job succeeds
2 = When the job fails
3 = When the job completes

List of all failed SQL Agent Jobs

Some SQL Agent Jobs run multiple time a day (I have see a job running every 10 seconds in Production).  SQL Agent only gives the the current state of all the jobs.  Here is how you can find all failures with their date and time.  From here, tracking down the root cause should just be a bit of investigative work.

SELECT  sysjobs.name
,sysjobhistory.run_date
,sysjobhistory.run_time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobhistory
ON sysjobhistory.job_id = sysjobs.job_id
WHERE sysjobhistory.run_status = 0
AND sysjobhistory.step_id <> 0
ORDER BY sysjobhistory.run_date DESC, sysjobhistory.run_time  DESC

Redgate's SQL Search

I have been reluctant to use Redgate's SQL search.  Instead I have been scripting out the database and running a search.  I have used many clunky add-ins with little to no value add, which has put me off to trying many others.  Encouraged to give SQL Search a try by a friend, I quickly realized I had been missing out.

With a simple menu, I can quickly decide what I would like to search for.  Check a few boxes and start typing my search, right from SSMS.

Results are generated quickly, it didn't take long for me to see a time saving.  Gaining results quickly also kept me focused on the task at hand without loosing my concentration.  I highly recommend Redgate's SQL Search to ANYONE using SQL Server.

Custom Color for Production Connection

Have elevated permissions in production and want a visual aid to help quickly identify your production environments?

Backup Solution

New Year, Same old story.  SQL Server backups are a necessity for every SQL Server Instance.  While there are many ways to accomplish this task, my favorite by far is Ola Hallengren's SQL Server Maintenance Solution.  The script comes complete with Database backups, rebuild indexes, integrity checks and even log cleanup.

Select from all tables on an Instance

We have all inherited less than desirable SQL Server Instances.  DBAs and Developers will both need to go through the countless databases, tables, SPs, etc. to get a handle of the situation.  In such a fragile situation, we need to ensure we keep our data integrity and prevent our developers from accident updates while trying to trouble shoot an issue.   Here is a nice Server Level Permission set to allow Developers to select from any table in any database on the instance.

Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

db_executer

SQL Server comes with several predefined database roles which fit the needs of most day to day user needs.  A big miss that I have found is the ability to execute stored procedures.  Developers need the right to execute the SPs they are creating in the DEV and QA environment.  Many service accounts also need execute rights.  All that needs done is to create the Role, grant execute rights, and add members to a role.

CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
EXEC sp_addrolemember 'db_executor','User or Group'