Querying SCCM – Finding Manufacturer Counts and Percentages

Lately I’ve been doing a lot of work with System Center Configuration Manager (SCCM) 2012 in terms of reporting and custom collections. As part of this, I’ve had the need to write this particular short query to retrieve manufacturer details several times, which seemed to make for a good target to share. Goal: Find the number…

0

Discovering SQL Server 2005 Instances by Querying System Center Configuration Manager (SCCM)

SQL Server 2005 extended support will end on April 12, 2016. After this date, Microsoft will no longer be releasing security updates for this product. In the article, we will query the System Center Configuration Manager (SCCM) 2012 database to obtain the number and editions of SQL Server 2005 instances that are installed, along with…

0

Auditing the usage of “EXECUTE AS” in a database

A question was recently asked in the MSDN Transact-SQL Forum about detecting the usage of “EXECUTE AS” in a specific database. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a385b29-1181-47aa-9f80-062d1bed3cd4/remove-spaces-in-like-search?forum=transactsql The “EXECUTE AS” clause was introduced in SQL Server 2005 as a powerful execution context enhancement to grant permission to a function, stored procedure, or trigger, while maintaining strict control over permissions on…

0

EOMONTH() Equivalent in SQL Server 2008 R2 and below

  SQL Server 2012 introduced several new system functions pertaining to the date and time data types.  In my opinion, I’ve found the EOMONTH(), “end of month”, function to be one of the most useful.  The EOMONTH() function returns the final day of the calendar month, thus greatly simplifying a common calculation, taking into account…

10

TechNet Guru Competition – Show off your SQL Server skills!

  Over the past few months, I’ve had the opportunity to help judge a great new online competition on TechNet Wiki spanning many Microsoft technologies and including several SQL Server categories.  The contest requirements are very basic and contribute to the overall SQL Server community by increasing the amount of user contributed content on TechNet…

1

Language Translation in SQL Server using Bing Translator APIs & SQL CLR

Language Translation within SQL Server using Microsoft Translator public APIs and CLR Stored Procedures / Functions   Does SQL Server have a built-in way to translate my text data from English to Spanish, German to French, Italian to Russian?  The answer is no, SQL Server does not have this functionality built-in, but it is indeed possible to build by…

14

Bulk Database Migration from On-Premise SQL Server to SQL Azure – Step 1: Bulk .bacpac Export

I recently had a task to perform a bulk migration of hundreds of databases to Windows Azure SQL Database (formerly known as SQL Azure) from an instance of SQL Server 2012.  If you need to migrate a very small number of databases, the easiest way is likely through the SSMS export Data-Tier Application wizard.  You can…

4

Creating Complex Test Databases – Generating One Table for each of the 2,397 supported Collations

As a follow up to my series on complex test databases, this article will cover one of the more interesting test databases that I recently created.    Goal:  Create a database that includes one table for each supported collation.  Each table contains a single column with the various column level collations supported by SQL Server 2012 (nearly…

0

TSQL – Solve it YOUR Way – Finding a Person’s Current Age Based on Birth Date

Introduction: As part of the blog series TSQL – Solve it YOUR Way, today’s topic will cover a question asked recently in the T-SQL MSDN forums where the solution, at first, seems extremely obvious.  However, as solutions were proposed and flaws were discovered with the proposals, the interesting nature of the problem was revealed.  As is the theme…

12

TSQL – Solve it YOUR Way – Parsing Tips and Tricks – Returning Digits after the Decimal Point

Introduction: As part of the blog series TSQL – Solve it YOUR Way, today we will tackle a numeric parsing question.  While past topics include solutions that vary from 15-150 lines of TSQL code, all of today’s solutions will be very short.  A question was recently posted in the MSDN forums asking how to retrieve the digits after the…

7