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… Continue reading Preventing Failed SSRS Subscriptions: Which user created your subscriptions?

Solution! SSIS Error: Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender

While running a 2012 SQL Server Integration Services package, the package ended up failing with the following message: Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender". The quickest and… Continue reading Solution! SSIS Error: Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender

Who Has The DAC?: Or How I Found Out I Had The Dedicated Admin Connection On SQL Server

Here's a quick little snippet of T-SQL script that will hopefully prove handy to someone (and that I'll end up googling at some point and find my own blog post). Are you trying to determine who is currently using the dedicated admin connection (aka DAC) on a particular SQL Server? Did SSMS give you a… Continue reading Who Has The DAC?: Or How I Found Out I Had The Dedicated Admin Connection On SQL Server

Error when trying to use DAC connection in SQL Server

So, one of my local databases got stuck in single-user mode. Easy solution? Connect to my instance using SQL Server's Dedicated Admin Connection (aka DAC) and flip the database back to multi-user. Easy! Wait. What? TITLE: Connect to Database Engine ------------------------------ Cannot connect to ADMIN:localhost\SQL2012. ------------------------------ ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while… Continue reading Error when trying to use DAC connection in SQL Server

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… Continue reading SQL Job Steps And Run_Duration: The query that formats run_duration correctly and doesn’t make you want to shoot your eye out

Resolving Analysis Services “The provider ‘SQLNCLI.1’ is not registered”

I know. While everyone else is moving on to SQL 2012 here I am upgrading a SSAS 2005 cube to SQL Server 2008 R2 Analysis Services. I can hear Garth imploring me to "Live in the now man!" So as I went about the mundane task of upgrading a cube from a really old version… Continue reading Resolving Analysis Services “The provider ‘SQLNCLI.1’ is not registered”

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… Continue reading SQL Server Impact Analysis Query

SQL Query: To Find Which Columns An Index Is On

Do you need to write a SQL Server query to find out which columns an index is on? Do you have an index name but need to use T-SQL to determine the column names? Not a problem! The query below will qive you exactly what you want. The example uses an AdventureWorks index as the… Continue reading SQL Query: To Find Which Columns An Index Is On

SQL Query: List All Indexes And Their Columns

Looking to write a SQL query which will list out all the indexes in your database as well as the columns in the index? Well my friend you've come to the right place. The query below will give list out the following information: Schema Table Name (or view) Index Name Is Primary Key Is Unique… Continue reading SQL Query: List All Indexes And Their Columns

Escaping From An Underscore In A SQL Server Wildcard / LIKE Search

There are two different ways to perform a wildcard (aka LIKE) search in SQL Server to find a word with an "_" underscore. Both options are outlined at the end of the article after a reference to the cult classic Mystery Science Theater 3000: Escape From The Bronx.