App-V: Upgrade from Softgrid Server 4.1 to App-V Server 4.5 Fails with Error 25119
You may know the issue: You are trying to upgrade from the Softgrid VAS 4.1 to Microsoft Application Virtualization (App-V) 4.5 Management Server and it fails with the following error:
“The installation program could not upgrade the configuration data store. Please see the installation log file for more information”
This is a generic database upgrade issue. there have been many causes of this as the error just means the database upgrade failed.
NOTE: There are several potential causes of this. Please take note that you will need to confirm this issue by examining specifically the softgrid-server-setup.log file. Please also refer to the Microsoft Knowledge base as many other possible causes have been documented.
In this particular instance, the database used split schemas where half of the stored procedures ran under a different schema than dbo.
The Softgrid-Server-setup.txt log reveals failure and further SQl Profiler Traces confirmed that it all went south as soon as it hits the stored procedures with user-defined schemas.
[2010-09-13 15:03:28] (11796:12004) SQL state: ``01000'', Native: 0, Text: ``[Microsoft][ODBC SQL Server Driver][SQL Server]<<< sp_SFTrepldel_... >>>''. [2010-09-13 15:03:28] (11796:12004) SQL state: ``42S02'', Native: 3701, Text: ``[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'sp_SFTreplins_ACCOUNT_AUTHORITIES', because it does not exist or you do not have permission.''. [2010-09-13 15:03:28] (11796:12004) ::SQLExecDirectW error 0xffffffff. [2010-09-13 15:04:32] (11796:12004) Failed to execute SQL statement `` if exists (select * from sysobjects where type = 'P' and name = 'sp_SFTreplins_ACCOUNT_AUTHORITIES') drop proc [sp_SFTreplins_ACCOUNT_AUTHORITIES] [2010-09-13 15:04:32] (11796:12004) Directory ``C:\Program Files\Softricity\SoftGrid Server\schema\upgrade\4.1\'' failed!
In this case, the non dbo schemas were owned by “Softricity_User.” What you do is use the following script to create a stored procedure called spMigrateStoredProcedures.sql.
NOTE: If you use this script just make sure to replace “Softricity_User” with the account specific to your customer’s database. After you use this script to create the stored procedure, execute it prior to upgrade. PLEASE BACK UP YOUR DATABASE BEFORE DOING SO.
CREATE PROCEDURE [dbo].[spMigrateStoredProcedures] AS DECLARE @FromSchema varchar(100) DECLARE @ToSchema varchar(100) SET @FromSchema = N'Softricity_User' SET @ToSchema = N'dbo' DECLARE @Done bit DECLARE @LASTNAME varchar(1000) SET @Done=0 -- insert top of tree SET @LASTNAME = 'ZZZZZZ' WHILE @Done=0 BEGIN select top 1 @LASTNAME=[name] from sys.sql_modules sm inner join sys.objects so on sm.object_id = so.object_id where so.schema_id = schema_id(@FromSchema) and so.name like N'sp%' and [name] < @LASTNAME order by [name] desc IF @@rowcount=0 begin SET @Done=1 end else begin exec ( 'print ''moving '+@LASTNAME+'''') exec ('ALTER SCHEMA '+@ToSchema+' TRANSFER '+@FromSchema+'.' + @LASTNAME ) end END