SQL : Find & Disconnect Database Sessions

When taking a database offline you may find the offline process takes a long time or may fail due to existing connections. You can use the following SQL to identify user sessions that are connected to a particular database:

{code lang:sql showtitle:false lines:false hidden:false}USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID(‘APPV’){/code}

Using the SPID from the output above kill any identified session by using the simple SQL below:

{code lang:sql showtitle:false lines:false hidden:false}kill <session ID>{/code}

ConfigMgr 2012 : App-V Management Server Co-Existence

One question I had for the ConfigMgr rollout was around how the migration from App-V Management Server to ConfigMgr was going to work. Specifically my questions were:

  1. Could the environments co-exist?
  2. Do we have to uninstall/reinstall the App-V Client with different settings?
  3. Will we lose the App-V client cache? With many remote users losing the cache was potentially a nightmare.
  4. What happens if I re-publish an App-V application via ConfigMgr that was originally published via App-V Management Server will it re-download?

After testing I have answered my questions.

Continue reading “ConfigMgr 2012 : App-V Management Server Co-Existence”

ConfigMgr 2012 : Collection by Domain Name

In a multi-domain environment you may wish to create collection (s) that are based on domain name; use the following query to do just that:

{code lang:sql showtitle:false lines:false hidden:false}select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup = “NETBIOS NAME”{/code}

APPV : Move SQL Database – Collation Issues

I’ve recently had to move an App-V Management Server’s SQL database to a new environment. Following the guide here I encountered an issue once completed – all clients could not refresh the publishing server or stream applications! The error I received is illustrated below:

App-V Error

Error code: 4513CDC-160650A-200001F4

After enabling verbose logging on the AppV Management Server I identified the following errors:

[2012-07-16 12:39:24.082] <SERVER NAME> 3920 924 SWExecDcRefreshProc – “Default Provider Policy” Administrator – 5 65535 “Failed to move to second result set on statement.”
[2012-07-16 12:39:24.082] <SERVER NAME> 3920 924 SWConstructDcRefreshXml – “Default Provider Policy” Administrator – 5 65535 “Failed to create XML.”
[2012-07-16 12:39:24.082] <SERVER NAME> 3920 924 RTSPHandlerEx::HandleGetAppList – “Default Provider Policy” Administrator – 5 65535 “Failed to construct <APPLIST>. Status[21887760]”
[2012-07-16 12:39:24.082] <SERVER NAME> 3920 924 SW_RTSPHandler::SendResponse – “Default Provider Policy” Administrator – 5 65535 “Response: [RTSP/1.0 500 Internal Server Error]

The client was reporting the following errors:

[07/12/2012 17:48:04:416 MIME ERR] {tid=244:usr=<USER ID>}
Failure on Desktop Configuration Server request to URL {rtsp:<SERVER FQDN>:554/} with header {Host: <SERVER FQDN>
Content-Type: text/xml
} (rc 1690650A-200001F4).

Seeing as the above wasn’t all that helpful I took a look at what was going on at a database level using SQL Profiler; it became apparent that there was a collation mismatch issue. The previous instance had been set to SQL_Latin1_General_CP1_CI_AS, whereas the new instance was set as the default Latin1_General_CI_AS.

After reviewing the stored procedure that was failing in the trace – sp_SFTgetPorterDCRefreshXML – it was clear that it was using a TEMP TABLE to perform comparisons. Temp tables sit in a system database and therefore use the system collation, not the database collation, unless you modify the declaration of your table to ensure that any varchar, nvarchar, text, ntext columns use the dataabse collation, not the system collation.

I used the following SQL to identify all stored procedures that created temporary tables:

{code lang:sql showtitle:false lines:false hidden:false}SELECT DISTINCT B.Name
FROM syscomments A INNER JOIN sysobjects B
ON A.id = B.id
WHERE A.text like ‘%CREATE TABLE #%’
ORDER BY{/code}

This generated a list of 16 Stored Procedures, although it turned out that I only needed to change 4 – Microsoft had correctly defined all of the other temp tables to use the database collation (consistency is overrated right?):

  1. sp_SFTaddpackageversion
  2. sp_SFTgetAlewifeDCRefreshXML
  3. sp_SFTgetPorterDCRefreshXML
  4. sp_SFTgetselectedapps

To ‘fix’ each stored procedure look for ‘CREATE TABLE #’ and modify as outlined below. Note you do not need to change numeric or date/time declarations, just text based ones:

Before:

{code lang:sql showtitle:false lines:false hidden:false}CREATE TABLE #temp_appdailyUserCount (
app_id int NOT NULL,
usage_date datetime NOT NULL,
Username nvarchar(256) NOT NULL ) {/code}

After:

{code lang:sql showtitle:false lines:false hidden:false}CREATE TABLE #temp_appdailyUserCount (
app_id int NOT NULL,
usage_date datetime NOT NULL,
Username nvarchar(256) COLLATE database_default NOT NULL )
{/code}

ConfigMgr : OSD Install Updates Download Hangs

I knew it had been too long since my last issue with ConfigMgr 2012! I’ve just redeployed the Configuration Manager 2012 environment my previous articles were based on only to encounter a new problem!

The issue is that now during Operating System Deployment during the ‘Install Updates’ step, downloading the updates appears to hang randomly as illustrated below.

BuildCapture UpdatesHang-ReSize

          Downloading xx of xx Updates (xx% complete)…

The solution was fairly simple. After reviewing the DataTransferService.log file under C:\Windows\CCM\Logs I found the following errors:

<![LOG[Error sending DAV request. HTTP code 600, status ”]LOG]!><time=”08:37:54.017-120″ date=”07-16-2012″ component=”DataTransferService” context=”” type=”3″ thread=”2572″ file=”util.cpp:629″>

<![LOG[Error retrieving manifest (0x800704cf).  Will attempt retry 6 in 960 seconds.]LOG]!><time=”08:37:54.017-120″ date=”07-16-2012″ component=”DataTransferService” context=”” type=”2″ thread=”2572″ file=”dtsjob.cpp:1161″>

<![LOG[Failed to send request to /NOCERT_SMS_DP_SMSPKG$/4e90bf14-4e96-47e1-b32e-8a8e9fcc28df at host <DP FQDN>, error 0x2efe]LOG]!><time=”08:37:54.345-120″ date=”07-16-2012″ component=”DataTransferService” context=”” type=”2″ thread=”728″ file=”ccmhttpget.cpp:1801″>

<![LOG[[CCMHTTP] ERROR: URL=https://<MP FQDN>:443/NOCERT_SMS_DP_SMSPKG$/4e90bf14-4e96-47e1-b32e-8a8e9fcc28df, Port=443, Options=480, Code=12030, Text=ERROR_WINHTTP_CONNECTION_ERROR]LOG]!><time=”08:37:54.345-120″ date=”07-16-2012″ component=”DataTransferService” context=”” type=”1″ thread=”728″ file=”ccmhttperror.cpp:291″>

So I copied the URL highlighted above into a browser window and got ‘page cannot be dispalyed’…! In fact it turned out that no HTTPS pages could be displayed, despite the HTTPS binding being configured in IIS. I removed the HTTPS binding on the Default Website and recreated it… problem solved.

Exchange 2010 : Export SMTP Email Addresses to CSV

Use the following PowerShell script to export a list of email addresses into a CSV file. The file will containt displayName, primarySMTPAddress and emailAddresses (proxyAddresses).

{code lang:ini showtitle:false lines:false hidden:false}$tbldata = @()
$arrmbx = get-mailbox -resultsize unlimited

foreach ($mbx in $arrmbx ) {
    $mailstring = ”    
    $record = @{}
    [array]$SmtpProxyAddresses = $mbx.emailaddresses | Where {$_.prefixstring -like ‘smtp’} | sort IsPrimaryAddress -Descending
    
    foreach ($mailadd in $SmtpProxyAddresses) {
            $mailadd = $mailadd -replace “smtp:”
            If ($mailstring -eq ” ) {
                $mailstring = $mailadd
            }
            ELSE {
                $mailstring += “,” +$mailadd
            }
    }
    
    $record.displayName = $mbx.name
    $record.primarySMTP = $mbx.primarySMTPaddress
    $record.emailAddress = $mailstring
    $tbldata += new-object PSObject -Property $record    
}

$tbldata | Export-Csv “Email Addresses.csv” -NoTypeInformation
{/code}