Archive for category SQL Stuff

Presentations from Dev Up 2016

I had the honor of being selected to present two sessions at Dev Up (formerly St. Louis Days Of .NET). The first was about deploying a SQL Server AlwaysOn Availability Group in Azure.  The slide deck can be found here: Deploying SQL Server in Azure – Configuring High Availability Cluster The second session was about Azure […]

Leave a comment

How do you calculate someone’s age in years, months and days?

In the Microsoft SQL Server Group on LinkedIn (http://lnkd.in/qYvG5R), a question was asked: How do you calculate someone’s age in years, months and days. I gave it a shot and… well… failed. Here’s my updated code. It will take care of cases where the birth date is Feb 29th or any birthdate on the 29th-31st […]

Leave a comment

How to create a Gantt-like Chart in SQL Server Reporting Services

Intro and Background I was recently asked for some help with a Dynamics CRM Online engagement.  The customer wanted a Gantt-like chart that showed the span of active opportunities. Dynamics CRM Online really isn’t important to my post, except in one major detail. Dynamics CRM Online doesn’t allow access to the underlying data.  There is […]

3 Comments

Ever needed to write a text file from within SQL Server?

Well, there are a three of ways of doing this:  Extended Stored Procedures, OLE Automation and SQLCLR. While it is out of the scope of this post, Extended Stored Procedures and OLE Automation are things that have been retained in the SQL Server product in order to maintain a measure of backwards compatibility.  Microsoft often […]

, , , ,

1 Comment

Concatenating Text using FOR XML

In my recent post about using CTE’s to concatenate text (see https://marcjellinek.wordpress.com/2011/07/10/cte-to-concatenate-column-values-in-multiple-rows-into-a-single-string/), I was very proud of myself and my genius. Then I read Majunath Bhat’s post “Grouping data from multiple rows into a single row” over on BeyondRelational (see http://beyondrelational.com/blogs/manjunathcbhat/archive/2011/08/07/grouping-data-from-multiple-rows-into-single-row.aspx) With some small changes, I now see the beauty in using FOR XML PATH […]

, ,

3 Comments

Backing up all databases on SQL Server

I was reading a blog post by Paresh over on LinkedIn (see his post here) and his recommendation for automagically backing up all databases within a SQL Server instance.  It inspired me to share my backup stored procedure. What does mine do that Paresh (and many others) do not do?  Here’s what I generally do […]

, ,

Leave a comment

CTE to concatenate column values in multiple rows into a single string

UPDATE:  While using PIVOT and CTEs to concatenate strings is cool, I’ve found a better way using FOR XML… see https://marcjellinek.wordpress.com/2011/08/20/concatenating-text-using-for-xml/ ORIGINAL ARTICLE: Using SQL Server, one thing I’ve always done is used PIVOT to concatenate strings.  The one thing about that solution is you have to impose a maximum number of rows that will […]

, , , ,

4 Comments

Excel and PowerPivot – Missing the Point

Steve Gray posted a question about Excel 2010 and PowerPivot on the LinkedIn Microsoft Business Intelligence Group.  See the thread at http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=12822397&gid=59185&commentID=29629258&trk=view_disc Steve wanted to know the business-level benefits of Excel and PowerPivot for the SME end-user. Here’s my response with some editing.  My original comments can be found in the thread referenced above. The […]

3 Comments