In case you missed it, I wrote a post about Disaster Recovery planning on my employers blog this week. You can read the full article here: http://www.rdx.com/Blog/dba_tips/2014/03/disaster-recovery-planning/
This is a quick one today as a follow-up to my previous post on using the SSMS Template explorer. While I do like the convenience of using the template explorer to store frequently used scripts, one of my biggest complaints was the fact that all the scripts are buried several directories down under the C:\users directory (windows 7).
Carl Demelo shared an awesome way to move your template directory to a different directory on SQL Server Central. This solution will only work on Windows 7 using the new shell command mklink.
I tried it out to move my template directory to a network drive that gets backed up on a regular basis. It worked perfect, thanks Carl!
The topic for this month’s TSQL Tuesday, hosted by Allen White, is an invitation to share your tricks. Before I get into my trick to share, I wanted to mention that Allen is a pretty awesome speaker too. I got to see his session “Gather SQL Server Performance Data with Powershell” at the SQL Saturday in Columbus earlier this year. Allen is really excited about SQL Server and the cool things you can do with Powershell.
Now for my trick, I have a view that was created to help write more dynamic rolling sql queries and reports in my organization. I’m not sure who the original author of the script is, as it has been passed around and modified several times. I thought I would share it here in the hopes that it helps someone else someday. If you’re the original author or know who is, please let me know so I can give you due credit.
This view defines a bunch of different date parameters compared to the current date, including:
You can take a quick look at the result returned from this view. After creating the view (script included below), run:
SELECT * FROM vw_date_ranges
With these columns defined, you can easily query a database table looking for rows based off of a date by cross joining this view and the adding the date columns to the where clause. For example, if you want to see all orders for “This Week”, you can run a query similar to:
SELECT SalesOrderID,
OrderDate,
SalesOrderNumber,
PurchaseOrderNumber,
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
CROSS JOIN vw_date_ranges
WHERE Sales.SalesOrderHeader.OrderDate > vw_date_ranges.SUNDAY_WEEK_BEGIN
Or if you want to see all orders for “Last Month”, you can run a query similar to:
SELECT SalesOrderID,
OrderDate,
SalesOrderNumber,
PurchaseOrderNumber,
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
CROSS JOIN vw_date_ranges
WHERE Sales.SalesOrderHeader.OrderDate >= vw_date_ranges.PREVIOUS_MONTH_BEGIN
AND Sales.SalesOrderHeader.OrderDate < vw_date_ranges.MONTH_BEGIN
Using this method helps me keep my sql query clean, and provides a rolling date range on queries and reports.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vw_date_ranges] (TODAY_BEGIN, TODAY_END, YESTERDAY_BEGIN, YESTERDAY_END, DAY_BEFORE_YESTERDAY_BEGIN, DAY_BEFORE_YESTERDAY_END, SUNDAY_WEEK_BEGIN, SUNDAY_WEEK_END, MONDAY_WEEK_BEGIN, MONDAY_WEEK_END, PREVIOUS_SUNDAY_WEEK_BEGIN, PREVIOUS_SUNDAY_WEEK_END, PREVIOUS_MONDAY_WEEK_BEGIN, PREVIOUS_MONDAY_WEEK_END, MONTH_BEGIN, MONTH_END, YESTERDAYS_MONTH_BEGIN, YESTERDAYS_MONTH_END, PREVIOUS_MONTH_BEGIN, PREVIOUS_MONTH_END, SECOND_PREVIOUS_MONTH_BEGIN, SECOND_PREVIOUS_MONTH_END, THIRD_PREVIOUS_MONTH_BEGIN, THIRD_PREVIOUS_MONTH_END, FOURTH_PREVIOUS_MONTH_BEGIN, FOURTH_PREVIOUS_MONTH_END, TWELTH_PREVIOUS_MONTH_BEGIN, TWELTH_PREVIOUS_MONTH_END, PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, PREVIOUS_SIXTH_MONDAY_WEEK_END, PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, PREVIOUS_SIXTH_SUNDAY_WEEK_END,NEXT_MONTH_BEGIN,NEXT_MONTH_END) AS select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) TODAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()+ 1 ), 0)) - .000011574 TODAY_END, DATEADD(dd, DATEDIFF(dd,0,getdate()), -1) YESTERDAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate() ), 0)) - .000011574 YESTERDAY_END, DATEADD(dd, DATEDIFF(dd,0,getdate()), -2) DAY_BEFORE_YESTERDAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate() ), -1)) - .000011574 DAY_BEFORE_YESTERDAY_END, -- Relative Dates - Weeks DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) + 6.999988426 SUNDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 7.999988426 MONDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) PREVIOUS_SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) + 6.999988426 PREVIOUS_SUNDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) PREVIOUS_MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) + 7.999988426 PREVIOUS_MONDAY_WEEK_END, -- Relative Dates - Months dateadd(mm,datediff(mm,0,getdate()),0) MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)) - .000011574 MONTH_END, dateadd(mm,datediff(mm,0,getdate() - 1),0) YESTERDAYS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() - 1 )+1, 0)) - .000011574 YESTERDAYS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-1, 0) PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)) - .000011574 PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0) SECOND_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-1, 0)) - .000011574 SECOND_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-3, 0) THIRD_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0)) - .000011574 THIRD_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-4, 0) FOURTH_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-3, 0)) - .000011574 FOURTH_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-12, 0) TWELTH_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-11, 0)) - .000011574 TWELTH_PREVIOUS_MONTH_END, -- Added 2/23/09 These will be used in a claim audit report that looks at data from 6 weeks prior. DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, 0) as PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, 0) + 7.999988426 as PREVIOUS_SIXTH_MONDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, -1) as PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, -1) + 6.999988426 as PREVIOUS_SIXTH_SUNDAY_WEEK_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )+1, 0) NEXT_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )+2, 0)) + .000011574 NEXT_MONTH_END
Today I am continuing from my introduction to SSMS Template Explorer. Part 2 today is a quick look at creating your own templates. I mentioned in the previous post that if you delete one of the Microsoft provided templates, it will be recreated when you next launch SSMS. Another thing of note: if you make a change to any of the default templates, those changes will keep when you next launch SSMS. In other words, changes do not get overwritten. My personal preference is to make a new template and not editing the Microsoft supplied template even though any edits that you make stick in the template.
Now that your template is created, the next logical step is to double-click it to open it. If you do that, you will see a blank query open up in the main window. This is not exactly what you want. Instead, right-click on your template and then click Edit.
Now when the blank query opens in the main window, it should have the template name in the query tab at the top. By going this route, when you save the query your script will get saved into the template file.
The first thing I usually do is start with a quick header. I will put some general information about what the template is for, the source if I copied it from a blog post somewhere, and the shortcut key to specify values for the template because I can never seem to remember them.
-------------------------------- -- Template Header -- It's here so I remember what I am using this piece of script for -- Created by Matt Nelson, 9/15/2011 -- -- Press CTRL + SHIFT + M to fill in variables (because I can never remember the key combination) --------------------------------
I can add my script now that my header is in place. There is a special syntax that you can use to use the CTRL+SHIFT+M shortcut to fill in blanks. I am going to steal Microsoft’s Backup Database template here to show you the syntax
BACKUP DATABASE sysname, Database_Name> TO DISK = N'sysname, Database_Name>.bak' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N'sysname, Database_Name>-Full Database Backup', SKIP, STATS = 10; GO
When you want to make a variable placeholder in your script you will put a 3 part variable enclosed between the greater than and less than symbols: <Parameter,Type,Value> like: sysname, Database_Name> .
Once you get your script all set up, don’t forget to save it. The next time you want to run it, all you have to do is double-click on it in Template Explorer.
I have used several different methods for collecting SQL scripts (both that I have written, and have borrowed from others) to reuse. A few that come to mind include: 1. saving the scripts in individual .sql files somewhere on my hard drive, 2. storing the scripts as an individual page in Microsoft Onenote. 3. saving scripts right in SSMS Template Explorer.
While I still have a collection of scripts in all 3 places for various purposes, I have been moving more and more towards using template explorer.
The biggest reason for my change is because whenever I need to run a script against a database server I am already opening up SSMS. It is then easier to open up template explorer than it is to then click on File->Open, and then browse for a specific script.
From here you can expand any of the categories to see a list of applicable scripts. Microsoft gives us a decent collection of templates to get us started and you can add your own scripts to the collection (covered in a future blog post). Find a script template that you want to use and double click it. SSMS will open that template up as a new query in the main window. WARNING: Make sure that the script window is connected to the appropriate server\instance before executing any scripts. (not that I have ever done that….)
For this example, I expanded the Backup category, and then double clicked on “Backup Database”
Now wait a minute, there’s a lot of funky stuff there. “BACKUP DATABASE” won’t run.
Here comes the magic of Template Explorer. Press CTRL + SHIFT + M on your keyboard. Behold the Specify Values prompt box.
Fill in the database name, and the file location where you want the backup saved to and press OK. Like Magic, the template place markers are removed from the script and replaced with the values that you specified a moment ago. The script is now ready to run as long as you double check to make sure you are running it against the appropriate server\instance.
Coming soon in a future post, making your own template. [EDIT: here is part 2]