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
WHERE BINARY_CHECKSUM(Value1) = BINARY_CHECKSUM(Value2)[/cc]

Thanks to Vyas for most of the info and some other idea: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

February 15, 2013

Posted In: T-SQL

SCCM 2012 SP1 refresh / KB2801987

No Gravatar

The original ISO for Microsoft System Center 2012 Configuration Manager SP1 contained a nasty little bug that prevented the ConfigMgr client from installing.

The problem was that the authenticode signature for MicrosoftPolicyPlatformSetup.msi, a required component of the client agent, was expired.

I discovered a few others had experienced the same thing (Client push fails with Authenticode Signature error).

Thankfully Microsoft only wasted about 2 weeks fixing the problem.  You can read additional details about the situation at myITforum, the official KB article, and Windows-Noob.  If you downloaded the original ISO, trash it and download the updated one.

Below is a table of the original ISO and the updated ISO from http://www.microsoft.com/licensing/servicecenter  per my experience.

Edition RTM
Description ISO of Original release of SCCM 2012 with SP1 integrated
Released ~ 2013/01/01
File Name SW_DVD5_Sys_Ctr_2012w_SP1_Config_Mgr_EndPt_Protection_MultiLang_-2_X18-84756.ISO
File Size 948,314,112
MD5 Hash 53e35591d07de3add537de11056afdc2
CRC32 73c129a0
SHA1 ab930f8fc127d00c510a5fd73038cf16dcfd2290
SHA-256 deda950a915d6a3548722f55ca51af3139bd9c69307b25088777f53908d27255
SHA-384 966842e033d56de87c5b5195d0fac75d3da8e53ec1f689fc1ab3d1e552b7ad6f21e86ddadfbc399c058ade725b143718
SHA-512 ab28694d0495d49620de70d287db972831f2903d1061b20d2324ef603ead56f19a8d019a52abc037bf448970ec53c63bd05013caf2454a5b9ad5889af6042b34

 

Edition CU0 / Refresh 1 / Cumulative Update 0 / ~whatever~
Description ISO of Refresh release of SCCM 2012 with SP1 integrated which contains KB2801987
Released ~ 2013/01/15
File Name SW_DVD5_SysCtr_2012_w_SP1_Conf_Mgr_and_EndPt_Prot_for_Windows-MultiLang_X18-58783.ISO
File Size 948,322,304
MD5 Hash 069c24f62a51b7296d54c1529808d64e
CRC32 06a2806c
SHA1 877c0b77607e7a580683d8b05d8da7dc548f3bb3
SHA-256 f7b1b5932e7fcd8f32cd1632c0a7a0c55334b9d0600a7a12d40ad8454e907a12
SHA-384 ba59c36e4c4e829dc8168e15ca9489867f1b05ddf4a12cefcf77072e99db0df1e76586af07a52d49a71957ab11b4b374
SHA-512 c71f29244ae84d14721e71ec7294e1450d11e7e1dfbedd284e5f82999176e0260252e5dac6219aae03f8c456c008b8b0655fd2a22d2ffc44a60c628a1d879f86

February 4, 2013

Posted In: ConfigMgr 2012

SCCM Report of Operating Systems and Types

No Gravatar

Based on Sherry Kissinger’s blog http://myitforum.com/cs2/blogs/skissinger/archive/2009/06/23/32bit-vs-64bit-report.aspx 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
[/cc]

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 2007, ConfigMgr 2012, T-SQL

WSUS database and content migration

No Gravatar

This is the simplistic process of moving the db and files for WSUS to a new drive or folder.

Move content

Using the command prompt, run the following

Move the database

Running Microsoft SQL Server Management Studio Express

  • connect to the the Database Engine using \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query as the server and use Windows Authentication
  • select the SUSDB database and detach it dropping any connections
  • move the SUSDB.mdf and SUSDB.ldf files to a new location using Windows Explorer
  • using SSMSE, attache the database (right-click on Databases, select attach)

*If you have trouble connecting to SQL, see this forum chain: http://channel9.msdn.com/Forums/TechOff/255490-How-to-connect-MICROSOFTSSEE

References:

  • http://blogs.technet.com/b/sus/archive/2008/05/19/wsus-how-to-change-the-location-where-wsus-stores-updates-locally.aspx
  • http://blogs.technet.com/b/sbs/archive/2009/09/23/how-to-move-wsus-content-and-database-files-to-a-different-partition.aspx

 

 

October 15, 2012

Posted In: WSUS