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:
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?):
- sp_SFTaddpackageversion
- sp_SFTgetAlewifeDCRefreshXML
- sp_SFTgetPorterDCRefreshXML
- 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}
