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:

View source
SELECT DISTINCT B.Name 
FROM syscomments A INNER JOIN sysobjects B 
ON A.id = B.id 
WHERE A.text LIKE '%CREATE TABLE #%' 
ORDER BY

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:

View source
CREATE TABLE #temp_appdailyUserCount ( 
app_id INT NOT NULL, 
usage_date datetime NOT NULL, 
Username nvarchar(256) NOT NULL ) 

After:

View source
CREATE TABLE #temp_appdailyUserCount ( 
app_id INT NOT NULL, 
usage_date datetime NOT NULL, 
Username nvarchar(256) COLLATE database_default NOT NULL ) 
 

website security

Disclaimer

Any suggested action/fix or otherwise stated or referenced on this website is presented with the following in mind:

You are responsible on your own when using any supplied information
• Information is provided “AS IS” with no warranties and confers no rights!
Always test before implementing/using tools/suggested procedures - and always take a backup.

You have been warned!