Category Archives: SQL Server

Determining Blackbaud Ad Hoc Queries that use Site in Designation

We had to trace which among our hundreds of Blackbaud Enterprise CRM (BBEC) ad hoc queries were using Site from the Designation node. We also needed to figure out if they were being used as output or being used as filter.

Here’s the script that did the trick.
Read more

MSSQLTips: Windows Server Failover Clustering for the SQL Server DBA

You’re a SQL Server DBA and you want to learn Windows Server Failover Clustering? On April 20, 2015 MSSQLTips is hosting an online training, and you can learn from SQL Server MVP and MCM (Microsoft Certified Master) Edwin Sarmiento. Check out the details here:
http://www.mssqltips.com/sqlservertraining/3/windows-server-failover-clustering-for-the-sql-server-dba/

Installing SQL Server using PowerShell Desired State Configuration (DSC)

Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.

The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.

This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.

Read more

Microsoft Big Data Hackathon is happening in Vancouver, April 18-19, 2015

Microsoft Big Data Hackathon April2015

The Microsoft Big Data Hackathon is happening in Vancouver. (Register here)

When
April 18-19, 2015 (Saturday and Sunday)

Where  
Simon Fraser University – Morris J Wosk Centre for Dialogue (Map)
580 W Hastings St
Vancouver British Columbia V6B 1L6
Canada

How
You will need to register for the event. Register here.
To participate, you will need to be part of a 3-5 member team.

Read more

Data Sets for BI/Analytics/Visualization Projects

Just wanted to compile a list of datasets, or sources of datasets, that can be used for BI/Analytics/Visualization projects and explorations (not limited or specific to any tools). These come in different formats, and some may need to be cleaned up. Please do read the restrictions and EULAs that come with each of the links/sources.

This is a work in progress. I  am still updating this post, and will most likely re-arrange or re-categorize the links as I stumble across other data sets.

If you have any that you can share, I would love to add those to this list (and mention you shared it!) – please leave a comment below and I will add them to the list!

Last Updated : February 01, 2015

Canadian Data

US Data

Read more

Microsoft Second Shot through May 31, 2015

Microsoft Second Shot is back for 2015! If you’re planning to upgrade your Microsoft certifications (MCSE, MCSA, MCSD), you might want to take advantage of this insurance. You will be eligible for a free retake (if you don’t pass the first time) if you take your exam between January 5, 2015 and May 31, 2015.

Check all the details here:

https://www.microsoft.com/learning/en-us/second-shot.aspx

By the way, Microsoft has switched to Pearson as a testing provider. You may find that testing centres could be scarce in some locations at first, hopefully that picks up soon!

 

Getting Grand Total and Subtotals using SQL Server Window Functions

Did you know that you can use SQL Server’s window function in an aggregate, and partition by NULL to get a grand total?

Let me illustrate. In the example below I use my trusty ol’ pubs database. (Hey, it’s still a great database for simple examples. Great for people starting to learn SQL too).

Let’s get some basic numbers first so we know what we are expecting to get.

Using a basic query that gets some grand total, we get 493. This the total for the whole table.

SELECT
SUM(qty)
FROM
sales

sum_sales

Read more

« Older Entries Recent Entries »