ConfigMgr and SQL – NTFS allocation unit size

No Gravatar

It’s been many years since I read that SQL databases should use an NTFS volume formatted with at 64KB file allocation unit size (block size). So long that I didn’t remember why or if it is still considered best/good practice. It appears that it is according to Microsoft and the foremost authority on SQL with ConfigMgr.

keep reading at

December 23, 2016

Posted In: ConfigMgr, Scripting, T-SQL

A Collection of ConfigMgr 2012 Collection Queries

No Gravatar

Tommy Gunn started a great post on this same topic at SystemCenterCentral.  I’m adding my own here.

*note: these are WQL queries for ConfigMgr/SCCM collections, but all will translate to T-SQL queries for reporting.

Computers which are joined to a specific domain or workgroup

Computers which are members of a domain security group

(notice the double backslash)


more to come…

June 13, 2014

Posted In: ConfigMgr, T-SQL

SQL Reports Subscription Ownership

No Gravatar

We have a number SCCM Reporting Services subscriptions for our automated reporting.  Yesterday I had a former team member’s ID removed from SCCM access and today most of the automated reports did not run.

When a report subscription is created the owner is the logged on user.  If that user ever loses access to SCCM/SSRS then the subscription will fail to run.  I decided the best course of action was to reassign ALL subscriptions to the SCCM service account.  That won’t prevent problems with new subscriptions, but it will prevent the problem with existing ones.  I could run the t-SQL on a schedule in SQL itself to handle that.  The only downside is that there will be no results when clicking the “My Subscriptions” link.

Thanks to Jeremiah Clark for the explanation and some t-SQL:

[cc lang=’sql’ line_numbers=’false’]
USE ReportServer
Declare @NewUserName [nvarchar](260); Set @NewUserName = ‘NewDomain\NewUser’
Declare @NewUserID [uniqueidentifier]

— Show Subscriptions for a User
select U.UserName [OwnerUserName], S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = @NewUserName

— Show UserID for a User
select @NewUserID=U.UserID From Users AS U where U.UserName = @NewUserName
Select @NewUserID [NewUserID], @NewUserName [NewUserName]

— Show records for Reassigning the User (Owner)
Select @NewUserID [NewUserID], U.UserID, U.UserName, S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName

— Reassign the User (Owner)
Update S Set S.OwnerID = @NewUserID From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName

May 1, 2013

Posted In: ConfigMgr, ConfigMgr 2007, SQL Reporting, T-SQL

Case Sensitive comparison in SQL

No Gravatar

The default collation that Microsoft SQL uses (and requried collation for SCCM) is case-insensitive.  Here are some tips for performing a case-sensitive comparison despite the case-insensitve collation.

Convert data to binary

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable
WHERE CAST(Value1 AS varbinary(255)) = CAST(Value1 AS varbinary(255))[/cc]

Using the COLLATE clause to dictate the case sensitiveness of the query

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable
WHERE Value1 COLLATE SQL_Latin1_General_CP1_CS_AS = Value2 COLLATE SQL_Latin1_General_CP1_CS_AS[/cc]

Using BINARY_CHECKSUM function

[cc lang=’sql’ line_numbers=’false’]SELECT * FROM MyTable

Thanks to Vyas for most of the info and some other idea:

February 15, 2013

Posted In: T-SQL

SCCM Report of Operating Systems and Types

No Gravatar

Based on Sherry Kissinger’s blog here is the summary report for a list of Processors, OS Type, OS Name, and Computer count

[cc lang=’sql’ line_numbers=’false’]
select pr.datawidth0 [Processor bits]
, pr.addresswidth0 [OS bits]
, sys.Operating_system_Name_and0 [OS Name]
,count(sys.resourceid) [Computers]
from v_r_system sys join v_gs_processor pr on sys.resourceid=pr.resourceid
group by pr.datawidth0, pr.addresswidth0, sys.Operating_system_Name_and0
order by pr.datawidth0, pr.addresswidth0, sys.Operating_system_Name_and0

Among other things, this will let you know if you need KB2793312B (see MyITforum), Cumulative Update 1 for SCM 2012 RTM, or even Cumulative Update 2.


January 8, 2013

Posted In: ConfigMgr, ConfigMgr 2007, T-SQL