Copy SSMS Templates

If you use Templates in SQL Management studio to store your own code snippets, or team code templates, you may be reluctant to upgrade your machine or version of SSMS for fearing of losing access to them.

While there isn't an export / import function within management studio, you can manually copy over your template files for your new SSMS.

 

SQL stores these under the hidden “AppData” folder in the Users directory, so you may need to turn on "display hidden files/folders" to see them.

 

for SQL 2008 you can find the templates under:

C:\Users\GRUMPY.DBA\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

 

Note that SQL 2008 copies all the default templates here as well, but you should be able to find your own custom templates in here (especially as you are probably already using a '-' as the first character of your folders to force it to appear at the top of the Template Explorer already aren't you?!).

Now just copy them to the same location on your new machine.

 

Alternatively, if you have installed a funky copy of SSMS for SQL 2012, you need this location (as keeping it the same is too simple for Microsods):

C:\Users\GRUMPY.DBA\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql

 

Again you'll find a copy of all the RTM templates visible in SSMS 2012 (this time via the seemingly pointlessly renamed "Template Browser"), and you can just copy your custom templates / folders into here. 

 

Note you need to restart SSMS to get it to pick up the new folders, but it's definitely quicker than creating them all by hand one at a time.

What reports are being read in SSRS

If you want to check which reports are being run (or not) in SSRS, or details about which users are running reports, you can pull this information from the ReportServer database.

If you need to check you are on the right database, you can check the SSRS machine name and instance the ReportServer database relates to by running:

SELECT
    MachineName,
    InstanceName
FROM Keys
WHERE Client = 1

Now to find out who's being doing what ... and SQL keeps it all nicely for us in the table "ExecutionLog".

You need to join this back to Catalog to get the report names / paths, but you can then easily start to get information on the most frequently run reports, which users are running certain reports, or even which reports are slow to render.

E.g. to get details on how often a report is run:

SELECT c.Name, c.path , count(*) as 'ReportRun'
FROM ExecutionLog e 
JOIN Catalog c on e.ReportID = c.ItemID
GROUP BY c.Path, c.Name

to find out which users are running a certain report:

SELECT userName, COUNT(*) 
FROM ExecutionLog e 
JOIN CATALOG c ON e.ReportID = c.ItemID
WHERE c.Name = 'ReportName'
GROUP BY e.UserName

I'm sure you can find other details to pull out of this table that may prove useful.

NOTE!

By default the retention period for the ExecutionLog table is 60 days.  However this is also adjustable by updating the ConfigurationInfo table for the ExecutionLogDaysKept row.  Set the number of days, or use "0" to keep data indefinitely.

E.g.
UPDATE ConfigurationInfo 
SET VALUE = '-1' 
WHERE Name = 'ExecutionLogDaysKept'

NOTE - Ignore books online which says this should be set to 0 - if you look at the clean up proc in the ReportServer database, namely ExpireExecutionLogEntries, you can see it actually only leaves the data if the value is set to -1, and not 0.

I would also recommend you download the SSRS Reports Dashboard.  This plugs into SSMS and once installed, you just right click on a ReportServer database, and it gives you access to all sorts of info relating to the relevant SSRS instance.