Tag Archives | script

Easy SQL Server Snapshot Script / Proc

Man!!!! Why is the code to create a database snapshot so ridiculously complicated? Actually, I guess I understand why but how about allowing it to be done through SSMS and then setting a bunch of the parameters as defaults for us? Come on Microsoft! Help a SQL brother out! Well luckily, where Microsoft fails, I’m […]

Change The Owner Of An SSRS Subscription: The Simple Way

So you need to change the owner of an SQL Server Reporting Services subscription? No problema. (Why would you need to change it you might ask? Perhaps because you have deactivated the user account that created the subscription and now “strange things are afoot at the Circle K”, and if you know that reference please […]

Preventing Failed SSRS Subscriptions: Which user created your subscriptions?

Check it out my friends. You need to keep an eye on which accounts are creating your SSRS subscriptions. Why? Because buried inside the ReportServer database (with no way to change it from the front end) SQL Server Reporting Services  is keeping track of this information. And when one of those user’s account is disabled strange […]

Get The MAX Date From A Cube Using MDX

Here’s the goal: populate a sql variable with the max date from one of the dimensions in a cube. It takes a little bit of unusual code but it’s relatively simple so pay attention! It’s probably important to note this code is assuming you are using a smart key for your date dimension (e.g. the […]

SQL Job Steps And Run_Duration: The query that formats run_duration correctly and doesn’t make you want to shoot your eye out

Ever tried to figure out how long a step for a particular SQL Server job takes? You can of course get it from the Management Studio, but we’re database people! We want to see the trend over time not just a point in time! We want to write queries! Hooray for data! You do a […]

SQL Server Impact Analysis Query

Ugh. Impact analysis is one my least favorite tasks. And despite the fact it’s gotten easier over the years with the addition of being able to check object dependencies natively within SQL Server Management Studio, I still find myself resorting to wildcard searches of object definitions for various reasons. (For example: dependencies on objects accessed […]

Last Restore Date And Time – SQL Server Query

“How old is this data?” “When was this database restored?” “How old is this backup?” These are common question in development environments.  A fast little t-sql query can tell you the following: The date and time of the last restore. The date and time the backup which was restored was originally taken. The user who […]