Upgrade 3.5.3 to 4.1.0

[stefvanhooijdonk]

Also see my previous post:
http://support.sourcegear.com/viewtopic.php?t=8856

Again this upgrade fails with the error that the db is in single user mode.

Why put the db in single user more? WHY?
Now the upgrade is still failing and I have to wait for the DB restore to go back to 3.5.2. This takes a LONG time for 14 gigabyte of data.

It there an installer available that will LEAVE the db in Multi user mode?

p.s. this could very well be our latest upgrade of vault cause this is not the quality we are looking for. An upgrade as this should be easy !!

[lbauer]

The database is put into single user mode while the Vault installer is upgrading the database, since you don't want any other connections during that time.

However, if there is problem with the upgrade, the single user mode is not set back to multi-user mode.

The main issue here is, why did the upgrade fail?

It appears that in more than one attempt, you have not been able to upgrade, is that correct?

Are you running the installer as a machine or Domain admin? Does that account have admin access to your SQL Server?

Could you post the Vault installer log -- Vault_install.log from the %temp% directory of the user who installed Vault? Or email it to support@sourcegear.com ATTN: Linda

[stefvanhooijdonk]

Log started at 29 Feb 2008 - 21.59.25

Custom action assembly location: C:\inetpub\wwwroot\VaultService\bin\CustomActionExe.exe
Custom Action version: 4.1.0

FileVersionInfo: File: C:\inetpub\wwwroot\VaultService\bin\CustomActionExe.exe
InternalName: CustomActionExe.exe
OriginalFilename: CustomActionExe.exe
FileVersion: 4.1.0.16216
FileDescription:
Product:
ProductVersion: 4.1.0.16216
Debug: False
Patched: False
PreRelease: False
PrivateBuild: False
SpecialBuild: False
Language: Language Neutral


The commandline args as passed in to us:

/TARGETDIR='C:\inetpub\wwwroot\VaultService\'
/TARGETDIR2='C:\inetpub\wwwroot\Fortress\'
/PARAMS=
/DROPDB=
/UPGRADE=
/ADMINPW=
/SQLSERVER=
/WINAUTH=1
End commandline args.

Found local name of NT AUTHORITY\NETWORK SERVICE for Network Service account.
Validate the target dir for shadow folders
See if we need to do an upgrade or an install...Configuring your new Vault Installation
Entered CreateCryptoRegistryValues.
CreateCryptoRegistryValues completed successfully.
Requesting the IIS Process User...Found local name of NT AUTHORITY\NETWORK SERVICE for Network Service account.
Requesting Vault Admin user password... Hashing the admin pw
OK
Connecting to the SQL Server...ConnectToDatabase() completed successfully.
Warning...Custom User Issue - Using windows auth to another machine. Ensure a user account with proper permissions is used.
dialog result for uninstall is: OK
OK
Verifying the SQL Server requirements... User is trying to install to Microsoft SQL version 9.00.3042.00
OK
Checking for an existing databases...Found.
Asking for database's fate...Keep existing.
Upgrading the existing source control database...
This may take a long time. Do NOT stop this process!
Checking the installed database version... 3.5.2.2
OK
Putting sgvault database in single-user mode.
getting the collation of the current source control database
the Vault database has the collation SQL_Latin1_General_CP1_CI_AS
Executing commands in master_ddl.sql
Read master_ddl.sql from our resources.
substituting collation for master_ddl.sqlwith collation SQL_Latin1_General_CP1_CI_AS
Creating SourceGear master database schema...OK
DoSqlCommandsFromFile completed successfully.
Upgrading existing database user information with a new install.
Login begin used is: tamtam\sourcecontrol
tamtam\sourcecontrol exists. Nothing to do.
GrantLogin completed successfully.
Granting login to 'DFT-DFS-001\ASPNET'
The user, DFT-DFS-001\ASPNET, could not be added as a SQL Server login. If you are running IIS6 in IIS5 isolation mode you must manually grant the DFT-DFS-001\ASPNET user access to sgvault database as _dbOwner.

Note, on some database collations check the case of the user's name.
Grant database access to tamtam\sourcecontrol...Checking for database access on tamtam\sourcecontrol within the sgvault database.
Checking for database access on tamtam\sourcecontrol within the sgmaster database.
OK
Executing commands in master_upgrade.sql
Read master_upgrade.sql from our resources.
Upgrading SourceGear master database schema...OK
DoSqlCommandsFromFile completed successfully.
Executing commands in vault_upgrade.sql
Read vault_upgrade.sql from our resources.
Upgrading SourceGear source control database schema...OK
DoSqlCommandsFromFile completed successfully.
Executing commands in master_sp.sql
Read master_sp.sql from our resources.
Upgrading SourceGear master stored procedures...OK
DoSqlCommandsFromFile completed successfully.
Executing commands in vault_sp.sql
Read vault_sp.sql from our resources.
Upgrading SourceGear source control stored procedures...Error executing SQL command in vault_sp.sql-------
CREATE PROCEDURE [dbo].[spgetlabelstructurewithsecurity] ( @userid [int], @repid [int], @labelid [bigint], @repdefaulteol [smallint] ) WITH ENCRYPTION
AS
BEGIN

-- Copyright 2004-2005 SourceGear LLC. --
-- All rights reserved. See Vault License Agreement --
-- for more information. --

SET NOCOUNT ON

CREATE TABLE #resulttree
( treelevel [int] NOT NULL,
parentpathhash [binary](16) NOT NULL,
folderobjverid [bigint] NOT NULL,
objverid [bigint] NOT NULL,
objid [bigint] NOT NULL,
objversion [bigint] NOT NULL,
name [nvarchar](256) COLLATE DATABASE_DEFAULT NOT NULL,
currentname [nvarchar](256) COLLATE DATABASE_DEFAULT NOT NULL,
objprops [smallint] NOT NULL,
pinnedfromobjverid [bigint] NOT NULL DEFAULT 0,
moddate [datetime] NOT NULL,
txbegin [datetime] NOT NULL,
filelength [bigint] NOT NULL DEFAULT 0,
filecrc [int] NOT NULL DEFAULT 0,
filedeltalength [bigint] NOT NULL DEFAULT 0,
mergeable [smallint] NOT NULL DEFAULT 0,
eolid [smallint] NOT NULL DEFAULT 0,
fullpathhash [binary](16) NOT NULL,
securityrights [int] NOT NULL DEFAULT -1,
PRIMARY KEY (treelevel, parentpathhash, fullpathhash, objid)
)

DECLARE @tblsecurityrights TABLE
( objid [bigint] NOT NULL,
type [nchar](1) COLLATE DATABASE_DEFAULT NOT NULL,
securityrights [int] NOT NULL /* usecurityrights */,
PRIMARY KEY (objid, type)
)

DECLARE @tblnofolderrights TABLE
( treelevel [int] NOT NULL,
parentpathhash [binary](16) NOT NULL,
fullpathhash [binary](16) NOT NULL, objid [bigint] NOT NULL,
PRIMARY KEY (treelevel, parentpathhash, fullpathhash, objid)
)

DECLARE @userid_param [int],
@repid_param [int],
@labelid_param [bigint],
@repdefaulteol_param [smallint],

@fileprop [uobjectproperties],

@rowsaffected [int],
@securityrights [int],

@sub_treelevel [int],
@sub_parentpathhash [binary](16),
@sub_fullpathhash [binary](16),
@sub_objid [bigint],

@treelevel [int],
@leaflevel [int]

SELECT @userid_param = @userid,
@repid_param = @repid,
@labelid_param = @labelid,
@repdefaulteol_param = @repdefaulteol,

@fileprop = 0x02

-- get the label skeleton
--INSERT INTO #resulttree ( treelevel, parentpathhash, folderobjverid, objverid, objid, objversion, name, currentname, objprops, moddate, txbegin, fullpathhash, securityrights )
--EXEC dbo.spgetlabelskeletonwithsecurity @userid = @userid_param, @repid = @repid_param, @labelid = @labelid_param

-------------------------------------------------
--
--
-- it was determined that having a stored procedure call another stored procedure impeded
-- performance. the stored procedure spgetlabelskeletonwithsecurity is expanded here
-- get the entire label skeleton

-- a user who is not admin.
IF ( @userid_param > 1 )
BEGIN
INSERT INTO #resulttree ( treelevel, parentpathhash, folderobjverid, objverid, objid, objversion,
name, currentname, objprops, moddate, txbegin, fullpathhash, securityrights )
SELECT treelevel, parentpathhash, folderobjverid, objverid, objid, objversion,
name, currentname, objprops, moddate, txbegin, fullpathhash, -1 FROM dbo.ufngetlabelskeleton( @repid_param, @labelid_param ) ORDER BY treelevel, parentpathhash, fullpathhash, objid

-- get user security rights
INSERT INTO @tblsecurityrights (objid, type, securityrights)
SELECT DISTINCT objid, type, securityrights + CASE WHEN ( type = N'U' ) THEN 100 ELSE 0 END FROM dbo.ufngetusersecurityrightsincludingdeleteditems(@repid_param, @userid_param)

-- assign main security rights to tree
UPDATE #resulttree SET #resulttree.securityrights = r.securityrights FROM @tblsecurityrights r
WHERE (#resulttree.treelevel >= 0) AND (#resulttree.objid = r.objid)
OPTION (KEEPFIXED PLAN)
IF ( @@ROWCOUNT > 0 )
BEGIN
-- apply security settings down the tree (inherited rights)
-- note, USER rights are the RCA value + 100,
-- by subtracting 10, we can make sure USER
-- rights overwride GROUP rights, but not other
-- USER rights. Also GROUP rights cannot overwride
-- either USER or GROUP rights.
SET @treelevel = 1
SELECT @leaflevel = MAX(treelevel) + 1 FROM #resulttree WHERE (treelevel >= 0)
WHILE ( @treelevel < @leaflevel )
BEGIN
UPDATE rt SET rt.securityrights = rtprev.securityrights FROM
#resulttree rt INNER JOIN
#resulttree rtprev ON ((rtprev.treelevel=(@treelevel-1)) AND (rtprev.fullpathhash=rt.parentpathhash) )
WHERE (rt.treelevel = @treelevel) AND
(rtprev.securityrights >= 0) AND
( (rt.securityrights = -1) OR (rt.securityrights < (rtprev.securityrights - 10)) )
OPTION (KEEPFIXED PLAN)
SET @treelevel = @treelevel + 1
END -- WHILE
END

-- fill in any default rights / reassign user rights
SELECT @securityrights = 1 --Assume that the user has read to the root of the label -- securityrights FROM @tblsecurityrights WHERE (objid = 0) AND (type = N'D')
UPDATE #resulttree SET securityrights = CASE WHEN (securityrights = -1) THEN @securityrights ELSE (securityrights - 100) END WHERE (treelevel >= 0) AND ((securityrights = -1) OR (securityrights >= 100))
OPTION (KEEPFIXED PLAN)

-- prune off folders where nothing can be seen.
INSERT INTO @tblnofolderrights (treelevel, parentpathhash, fullpathhash, objid)
SELECT treelevel, parentpathhash, fullpathhash, objid FROM #resulttree WHERE (treelevel >= 0) AND (securityrights = 0)

DECLARE curprune CURSOR LOCAL FAST_FORWARD FOR SELECT treelevel, parentpathhash, fullpathhash, objid FROM @tblnofolderrights WHERE (treelevel >= 0) ORDER BY treelevel DESC
OPEN curprune
FETCH FROM curprune INTO @sub_treelevel, @sub_parentpathhash, @sub_fullpathhash, @sub_objid
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- is there a sub folder for this folder, which may have rights, (it exists) do not delete
IF ( NOT EXISTS (SELECT treelevel FROM #resulttree WHERE (treelevel = (@sub_treelevel + 1)) AND (parentpathhash = @sub_fullpathhash) AND (securityrights > -1)) )
BEGIN
-- remove the folder
DELETE FROM #resulttree WHERE (treelevel = @sub_treelevel) AND (parentpathhash = @sub_parentpathhash) AND (fullpathhash = @sub_fullpathhash) AND (objid = @sub_objid) AND (securityrights = 0)
OPTION (KEEPFIXED PLAN)
END
FETCH NEXT FROM curprune INTO @sub_treelevel, @sub_parentpathhash, @sub_fullpathhash, @sub_objid
END
CLOSE curprune
DEALLOCATE curprune
END
ELSE
BEGIN
INSERT INTO #resulttree ( securityrights, treelevel, parentpathhash, folderobjverid, objverid, objid, objversion,
name, currentname, objprops, moddate, txbegin, fullpathhash )
SELECT 7, treelevel, parentpathhash, folderobjverid, objverid, objid, objversion,
name, currentname, objprops, moddate, txbegin, fullpathhash FROM dbo.ufngetlabelskeleton( @repid_param, @labelid_param ) ORDER BY treelevel, parentpathhash, fullpathhash, objid
END
--
--
-------------------------------------------------


-- add files where the skeleton folders have security
INSERT INTO #resulttree ( securityrights, treelevel, parentpathhash, folderobjverid, objverid, objid, objversion, name, currentname, objprops, moddate, txbegin, filelength, filecrc, filedeltalength, mergeable, eolid, fullpathhash )
SELECT rt.securityrights, lv.treelevel, lv.parentpathhash, rt.objverid, lv.objverid, lv.objid, lv.objversion, lv.name, n.name, o.objprops, v.moddate, t.txbegin, COALESCE(ovfi.filelength, 0), COALESCE(ovfi.filecrc, 0), COALESCE(fi.filedeltalength, 0), COALESCE(v.mergeable, -1), COALESCE(v.eolid, @repdefaulteol_param), lv.fullpathhash
FROM #resulttree rt
INNER JOIN dbo.tblfsobjectlabelviews lv ON (lv.labelid = @labelid_param) AND (lv.treelevel = (rt.treelevel + 1)) AND (lv.parentpathhash = rt.fullpathhash) AND (lv.fullpathhash = 0x0)
INNER JOIN dbo.tblfsobjectversions v ON (v.objverid = lv.objverid)
INNER JOIN dbo.tblfsobjects o ON (o.repid = @repid_param) AND (o.objid = lv.objid) AND ((o.objprops & @fileprop) = @fileprop)
INNER JOIN dbo.tblfsobjectversions v2 ON ((v2.objid = o.objid) AND (v2.objversion = o.lastobjversion))
INNER JOIN dbo.tblfsobjectversionnames n ON (n.vernameid = v2.vernameid)
LEFT OUTER JOIN dbo.tblfsobjectversionfileinfos ovfi ON (ovfi.objverid = lv.objverid)
LEFT OUTER JOIN dbo.tblfileinfo fi ON (fi.fileinfoid = ovfi.fullfileinfoid)
INNER JOIN dbo.tbltransactions t ON (t.txid = v.origintxid)
WHERE (rt.treelevel >= 0) AND (rt.securityrights > 0)

SELECT treelevel, parentpathhash, folderobjverid, objverid, objid, objversion, name,
currentname, objprops, pinnedfromobjverid, moddate, txbegin, filelength,
filecrc, filedeltalength, mergeable, eolid, fullpathhash, securityrights FROM #resulttree WHERE (securityrights > -1) ORDER BY treelevel

-- not really necessary to drop temp tables
-- within a stored proc. only here for use
-- with testing within plain T-SQL
-- DROP TABLE #resulttree

SET NOCOUNT OFF

RETURN @@ERROR

END
--------
Database 'sgvault' is already open and can only have one user at a time.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280
Database 'sgvault' is already open and can only have one user at a time.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280ExitCode on exit: -1280