Category Archives: Tips

Community Resources

Leave a reply

After helping to plan several SQL Saturday events, planning Pittsburgh SQL Server User Group meetings, planning a Wanna Be A DBA track, and chatting with others in the community; I’ve come to realize that people often ask me how and where they can learn more about SQL Server. I start rattling off a list of places that they can go, and they start scribbling like mad to keep up with all the resources that I mention.
This page is to document good resources for beginners and seasoned pros alike. It is by no means a comprehensive list. I will continue to add more resources to this page in the future, as I find other places of value.  Updates can be found at http://nelsonsweb.net/resources.  Send me a message if there’s anything else that you think is worth adding to this list as well.  I welcome your feedback!

Events

SQL Saturday (www.sqlsaturday.com)
There’s nothing better than a full day of free training!  There is typically a small charge for lunch.  All of the training is free, thanks to many sponsors; and all the speakers also donate their time as well.  Visit sqlsaturday.com frequently to see upcoming events near you.  If you attend a SQL Saturday, make sure that you visit the sponsors and thank them.  None of the events would be possible without them.
Pittsburgh SQL Saturday is usually in October

Local User group meetings (www.sqlpass.org   http://pittsburgh.sqlpass.org)
There are 280+ local SQL user groups across the world.  Check out sqlpass.org to find a group near you.  The Pittsburgh, PA group typically meets monthly on the last Tuesday of the month.

PASS Virtual Chapters (http://sqlpass.org/PASSChapters/VirtualChapters.aspx)
PASS has a collection of “Virtual Chapters”.  these groups have a variety of subject matters, and are produced in many languages as well!  Go to the site and join the groups that interest you.  They will send you a web link when they schedule an upcoming meeting.

24 hours of PASS ( www.24hoursofpass.com/)
PASS puts on this event a few times a year.  There are online webinars that run for a 24 hour period.  Register online, and they will send you a meeting link to watch the presentations that you are interested in.  They also typically record all the sessions and make them available online later.

Training

SQLServerCentral Stairway Series (http://www.sqlservercentral.com/stairway/)
The Stairways series are a great place to go to get an overview on a specific topic.  There are series for everything from database design to business intelligence to powershell.  All of the lessons within the series are written by a leading industry expert in that subject.

Pragmatic Works Training on the ‘T’s (http://pragmaticworks.com/Training/FreeTraining)
Pragmatic Works offers free training every Tuesday and Thursday at 11 AM EST!  Most of the sessions are BI related, but you will sometimes see some general administration sessions as well.  All of the previous sessions were recorded and are available to watch later at the same site.  Pragmatic Works also runs paid in-person training in various cities and they have some cool paid tools for SSIS that you may want to check out.

Brent Ozar (http://www.brentozar.com/first-aid/events/)
Brent Ozar and his team also offer free training events weekly.

Free tools

Notepad++ (notepad-plus-plus.org/)
This is my preferred text editor.  I wanted to mention this tool in particular because it has a free plugin called Poor Mans TSQL Formatter.   You can copy a query into Notepad++, and then click on the plugin to format SQL code.  The plugin will reformat the code into a more standardized format for you for easier reading.

sp_Blitz (http://www.brentozar.com/blitz/)
Brent Ozar has a great set of scripts to give you a health check overview of your servers.  After running the scripts, you will see lots of potential red flags, with links for more information about the problems and what you can do to fix them.

sp_whoisactive (Download)
Adam Mechanic built a great stored procedure that is sp_who2 on steroids!  Running the basic stored procedure will give you tons of information about what SPID’s are currently hitting your instance, including CPU, tempdb and I/O usage, how long sessions have been executing, blocking sessions and lots more.  You can also add in additional parameters to get tons more information, including query plans.  And one of my favorite options lets you save teh output into a database table for further review and analysis.
Adam also wrote a 30 day blog post series to go into details of the additional options that you can use.  http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx

Ola Hallengren Backup and Smart Reindexing scripts (https://ola.hallengren.com/)
No list is complete without mentioning Ola’s alternative to native maintenance plans.  These stored procedures fix several common issues to make database backups and reindexing better.

SQL Server Builds (http://sqlserverbuilds.blogspot.com/)
This site is a great resource if you want to check what the latest service pack is for any version of SQL Server.  This site has information about every single patch and service pack, and direct links to download each one from Microsoft.

Blogs

I don’t know that I can recommend any other blogs to you except for this one.  🙂
In all seriousness, there are lots of blogs that I do follow.  Most are either people who I know, or people who I respect in the community.  At some point I may get around to listing some or all of them here.  In the meantime, a good place to get started is SQLServerCentral.  They aggregate lots of other blogs together in one place for you here: http://www.sqlservercentral.com/blogs/

 

Powershell: delete files older than X days

Leave a reply

On a recent project, I needed to delete archive folders that were older than a specified number of days. The thing that made this a little more challenging is that there were Daily, Weekly, and Monthly folders (similar to the screenshot below); each of which had a different retention period.

I found several scripts to delete folders and files older than a specified number of days, but these scripts would delete all the contents of the specified folder. I needed to be able to filter out the Daily, Weekly, or Monthly folders separately to handle their retention period.

This script is can be customized. Change the “-filter” to include the folder names that you want to delete, and change the number of days in the addDays () command.
Another really handy option is to use the -whatif option at the end of the script. This will print out in the powershell window what will be deleted, but it will not delete the files. This will let you test the delete without actually deleting the folders/files. The first delete example below includes the -whatif option so that you can see where it goes.

$thedirectory = "C:\test\ImportFolder\Archive"
# use "-whatif" to show what will be deleted without actually deleting anything
cd $thedirectory
get-childitem $thedirectory -filter "*daily*" |? {$_.psiscontainer  -and $_.lastwritetime -le (get-date).adddays(-35)} |% {remove-item $_ -force -recurse -whatif}
get-childitem $thedirectory -filter "*weekly*" |? {$_.psiscontainer  -and $_.lastwritetime -le (get-date).adddays(-15)} |% {remove-item $_ -force -recurse}
get-childitem $thedirectory -filter "*monthly*" |? {$_.psiscontainer  -and $_.lastwritetime -le (get-date).addmonths(-25)} |% {remove-item $_ -force -recurse }

This post is part of the blogging phenomenon known as TSQL Tuesday. This month’s blog party is hosted by Wayne Sheffield, who is writing a series of blog posts in the month of February all about powershell.  I couldn’t pick just 1 script to share today, so here is my second post on the topic for day.

SSIS execute task only on Tuesday

4 Replies

I’ve started working with SSIS a lot more lately.  I am going to attempt to document here some of the quirks that took me a little while to figure out along the way.

In this tidbit, I have a multi-step SSIS package that needs to be run on a daily basis.  However, one step of the process should only be run on a specific day of the week (lets say it should only be run on Tuesday).

My screenshots are all taken in Visual Studio 2010.  The process is the same for 2008 R2.

The process:

  1. Create the necessary steps.  For this simple example, I created 3 Execute SQL tasks.  They each run the query: “SELECT 1”.
  2. Drag the green arrows to link each of the tasks in order.  By default, the links will all be success constraints.
  3. Double click on the line between Task 1 and Task 2.  This will open the Precedence Constraint Editor.
    1. Change Evaluation operation to: “Expression and Constraint”
    2. In the expression block, type: “DATEPART( “dw”, getdate()) ==3″
      • Using the DATEPART(“dw” ) function, Sunday=1 and Saturday=7.  Since we only want Tuesday, we choose ==3.
    3. Press OK
    4. You will notice that the line between Task 1 and Task 2 now has an “fx” symbol on top of it.
  4. At this point, the package will run and Task 2 will only run on the specified day.  However there is an issue with the current setup: Task 3 will begin executing as soon as Task 1 completes.  It will not wait  until Task 2 completes on Tuesdays.  We need to modify that link as well to create a fork in the path.
  5. Double click on the link between Task 1 and Task 3.
    1. Change Evaluation operation to: “Expression and Constraint”
    2. In the expression block, type: “DATEPART( “dw”, getdate()) !=3″
    3. Under multiple constraints, change to the option: “Logical OR. One constraint must evaluate to True”
      • Changing to Logical OR is required.  Since we created the fork at Task 1, only one of the two lines going into Task 3 will evaluate as successful completion.
    4. Press OK
    5. You will notice that the line between Task 1 and Task 3 turns into a dashed line and it also has an “fx” symbol on top of it.
  6. You can now run your package to test that the steps work properly.  To test the design, I changed my formula’s ==3 and !=3 to a different day of the week to make sure that Task 2 got bypassed correctly on its off day.

Import Active Directory users into SQL Server

1 Reply

I needed to import a list of all Active Directory user accounts into a table in SQL Server for a recent project. This project also gave me a perfect opportunity to learn a little bit of powershell. Below chronicles the script that I built. I’m going to skip over a lot of the powershell basics information, as that is available from other sources. For this project, I needed to populate a table with these fields from Active Directory: Display Name, NT username, email address, and office phone.
I used the powershell Get-ADUser cmdlet to get the information out of Active Directory
Before doing anything else, you need to open a powershell command window (Start–>Run–>powershell.exe) and import the Powershell ActiveDirectory module:

PS C:\> Import-Module activedirectory

After importing the module, you can learn more about the Get-ADUser cmdlet by using some of these commands

Get-Help Get-ADUser
Get-Help Get-ADUser -examples
Get-Help Get-ADUser -detailed

Examples are great, but I learn better by seeing real results, so lets run a quick query to see what information we get.

Get-ADUser -filter * -ResultSetSize 1
#Note, I included “-ResultSetSize 1” so that I was not overwhelming the domain controllers while testing.

Awesome, I can now see user accounts from Active Directory! The output that I got showed me some of the information that I needed, but I am still missing some pieces (primarily email address and phone number). The “-Properties” option will let you pick additional fields to include in the output. I got a little stuck here briefly, because the Get-ADUser cmdlet names for the properties do not all match the Active Directory field names. To figure out what the appropriate field names were, I ran this:

Get-ADUser -filter * -ResultSetSize 1 -Properties *

Cool, now I can put the fields together to get a shortened list of only what I am looking for:

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone
# Note this will return additional fields (DistinguishedName,Enabled,ObjectClass, SID,…)

I got a little bit stuck here too, because I was getting too much information. When I got to the point of exporting this data to a CSV file and importing it into SQL Server (coming later), I got hung up because some of the fields did not always have information for my organization. The solution came by using a pipe (SHIFT + \ key) and the Select-Object cmdlet. This let me filter for only the specific columns that I wanted out of Active Directory.

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName

I now see only the 4 columns that I care about. On a larger scale test, I realized that I was returning accounts that I did not want to see (like disabled accounts, Administrative accounts, etc.) I used the –Filter option to include some search criteria here.
Filtering in powershell is a little different than what I am used to. For example, “=” is “-eq” in powershell and “not equal to” or “<>” is “-notlike” in powershell. You can also combine multiple filters by including the entire set in curly brackets { }, individual parameters in parenthesis (), and using the “-and” operator. The Asterisk is the wildcard variable.
For example:

-Filter {(Name -notlike "*(Administrator)") -and (Name -notlike "Matt*") -and (Enabled -eq "True") }
# I also threw in there where Name is not like Matt*

Now that I have only the fields that I want, and I filtered out the users that I don’t want to see, I can start working on importing it into SQL Server. I could have used powershell to insert the records directly into SQL, but I was concerned about latency issues and spamming the domain controllers into a denial-of-service attack. I was working with more than 50,000 Active Directory accounts. I definitely did not want to hold up the domain controllers if there was an issue with the SQL server during the process. Because of this, I decided to export the data as a CSV comma delimited file and then use SSIS to import the data.
Exporting the data to a csv file uses another pipe (SHIFT + \ key) and the export-csv cmdlet. Make sure to put in your appropriate file path to export to

#Make sure you put your file path between the < >
 | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Putting everything together.

Make sure to put in your appropriate file path to export to.
I also took out the “-ResultSetSize” option so that all records were returned.

#Make sure you put your file path between the < >
Get-ADUser -Filter {(Name -notlike "*(Administrator)")  -and (Enabled -eq "True") }  -Properties SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Once the data was exported to a CSV comma delimited file, I am using SSIS to import it into SQL server. The powershell script and SSIS package are both scheduled to run daily overnight when things should be slower on the servers.

Running SSMS template explorer from network drive

Leave a reply

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!

T_SQL Tuesday #25: Sharing Tricks

1 Reply

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:

  • 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

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.

Download the script here


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

Fixing orphaned users

Leave a reply

Whenever I restore a production database to a development server, I often end up with orphaned users. You can see in the screen shot below that username matt has no login associated with it.

An account becomes orphaned when there is a user account with security rights in the database that is not linked to a user account that can log in to the server.  This happens because SQL logins with the same username have different SID’s on different servers.  It is not a problem for Active Directory logins because the SID is stored in Active Directory.

Once an account becomes orphaned, it is a pretty easy fix:

USE <database name>
ALTER USER <username> WITH LOGIN = <username>

But it’s still a pain to remember to run this code to reset the logins.  I usually forget, and the scratch my head for a minute to figure out why the application won’t connect to the development database anymore.

Fix it once and for all

Microsoft has a handy script to fix orphaned users (link).  Download and run the script on your production server to create two stored procedures: sp_hexadecimal and sp_help_revlogin.

Then run:

EXEC sp_help_revlogin

Run the output from this procedure against your development server to create the appropriate logins with the correct username/password and SID.

Ever since I ran this, I have not had any more orphaned users when restoring databases back to dev.

Creating a new template

Leave a reply

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.

Create the template file

  1. First right-click on the root node in Template Explorer labeled “SQL Server Templates”
  2. Select New, and then Folder
  3. Name your folder.  I generally start my folder names with a period so that they get sorted at the top of the list.  for example: “.Admin”
  4. Once you create your folder, Right click on that folder name.
  5. Select New, and then Template
  6. Name your template whatever makes sense to you.

Edit the template file

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> .

  • Parameter is basically the variable name.
  • Type is a placeholder for the of variable.  There are no constraints around the type like there are with a database table column.  I generally leave this blank.
  • Value can also be blank, or you can make it a sample for yourself.

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.

SSMS Template explorer

3 Replies

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.

Lets get started
  1. Open SSMS
  2. Open the Template explorer by either using the key combination CTRL + ALT + T  (my preferred method), or clicking on View->Template Explorer.
  3. The template explorer will then open on the right hand dock in SSMS (kinda like how it shows up in my screen shot here!).

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.

.
A couple gotchas
  • All of the Template Explorer scripts are saved on my PC in:
    C:\Users\<user profile>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql
    This is running SSMS 2008 R2 on Windows 7.
  • This save path for the templates does not get backed up on my local PC.  It also does not travel if I use SSMS on another PC.
    • So far my solution has been to use a Remote Desktop Connection back to my main work PC if I am using another PC in the office.
    • Every now and then I will manually copy the directory up to a network location that does get backed up so that I can get my templates back if my PC dies.  Does anyone have a better solution?
  • If you delete a template or folder that Microsoft provided, it will be recreated whenever you restart SSMS.  I found that it is best to leave the Microsoft provided templates alone and add my own.

Coming soon in a future post, making your own template. [EDIT: here is part 2]