Most of the time subscriptions run fine, but what happens when they fail – how you figure out what is going wrong? Profiler might help you figure it out, depending on where in the process the subscription is failing, but it’s not the first place to look. It’s one of those times when you hope for a cool answer, but in the end it’s what you would expect, a log file hidden away on disk that at least gives you a starting point. We’ll show you where, and mention an interesting bit of maintenance you should be doing as well.
Profiler is a great tool, but it’s easy to get overwhelmed by the volume of results on a busy server. It’s also not easy to aggregate the results because of literals passed in as parameters. You can do it, but it’s work. Instead, we recommend you try a great free application called ClearTrace that will give you a terrific view of the results in a way that is easy to understand.
It’s a small tip, but it might prove useful one day, showing you the trick to building a custom subject line for each row in the subscription. It’s also a very quick refresher on building data driven subscriptions.
Here’s a quick how-to on how to add users to Reporting Services and then how to set permissions on a folder. Did you know that by default if you add someone to the top level home folder they will have access to all the sub folders? We’ll show you how to break the default inheritance and talk a little about best practices for managing users and permissions.
DSN’s (data source names) are used to store connection string information in either the registry or the file system. When you upgrade to a new server or move a job or process to a different server, you may have to move related DSN’s as well. The process isn’t hard, just remember that moving the DSN doesn’t move any required libraries or client code, you have to track those down and get installed on the new machine as a separate step.
So you have taken the time to set up a baseline capture using a data collector set, do you know how to view it? Loading it back into the graphical Perfmon UI is a great way to see what performance looked like over a longer period, and it lets you do that by zooming in on an area, and then you can easily save that view as an image – handy for sending to the boss.
Did you know that if you rename a view using SSMS you can wind up in a situation that causes a different view to get over written later in the future? That’s right, change the name of a view by appending something like ‘_old’ and later on when someone runs sp_refreshview you will change the original view. Unexpected and incredibly bad, watch the lesson to see how it happens.
It’s easy to create a view and realize after working with it some, or tweaking it’s definition, that you have named it badly. Good names are worth having, so you just select the view in SSMS, click F2, edit the name, and you are done, brand new name. Right? Well, you can do that, but you are setting yourself up for a really unexpected problem later on. Today we’ll show you why renaming in SSMS is a bad idea, and then in Part 2 we’ll show you how it can really cause pain.
A DSN (data source name) is a simple way to externalize the information required for an application to connect to a data source. For SQL Server, that means we need to provide the name (or IP address) of the server and credentials, and usually we’ll specify the target or default database. Now we have our application pull the connection from the DSN at run time, giving us an easy way to change the name of the server or database or the credentials at any time without rebuilding the application.
If you have made a compressed backup using SQL 2008 Enterprise Edition or SQL 2008 R2 Standard Edition you have got it made when it comes to restores – no change in syntax is required to restore from a compressed backup. Not only that, even if you used the Enterprise Edition you can restore the backup to an instance running Standard Edition. But wait, there’s more. On most systems the time it takes to restore the compressed backup will be FASTER than from an uncompressed backup. That’s as close to a free lunch as we’re likely to get in the database world.