DB Update blows.

Thona · Post by **Thona** » Thu Mar 02, 2006 10:57 am

Ok, I am using the one user license of vault as my private repository. As I schedule out the sql 2000 version, time to upgrade.

I detached the database from sql server 2000 and attached it on 2005.

I run the install mechanism from the web (the latest one).

It blows.

Error:

DAtabase "sgvuault" is already open and can only have one user at a time".

The db gets turned into single user mode when the upgrade scipt starts.

Anyone an idea how to handle that? VREY inconvenient.

lbauer · Post by **lbauer** » Thu Mar 02, 2006 11:32 am

We could use more information on the steps you took.

You detached your sgvault database from SQL Server 2000 and then and re-attached your sgvualt database to a SQL Server 2005 installation?

Then you ran the Vault 3.1.7 installer. Is that correct? Did you get the error message

DAtabase "sgvuault" is already open and can only have one user at a time".

from the Vault installer?

Thona · Post by **Thona** » Thu Mar 02, 2006 12:09 pm

That is absolutly correct. Step by step. When the error happens, a first command has at least run. THe database is turned into single user only mode. I can run a trace to see which was the last command not failing, if you want.

ian_sg · Post by **ian_sg** » Thu Mar 02, 2006 12:42 pm

Odd. You can put the database back into multi-user mode by running this in Query Analyzer:

Code: Select all

ALTER DATABASE sgvault SET MULTI_USER

Then try the install/upgrade again. If it fails again, your installation log (%temp%\vault_install.log) would be helpful to figure out what's going on.

Thona · Post by **Thona** » Thu Mar 02, 2006 12:48 pm

Setting the db back does not work. As in: I already ahve the error, and on the next run it happens again.

The log is here:

Vault log started at 2 Mrz 2006 - 17.50.36

Custom action assembly location: C:\Inetpub\wwwroot\VaultService\bin\CustomActionExe.exe
Custom Action version: 3.1.7

FileVersionInfo: File: C:\Inetpub\wwwroot\VaultService\bin\CustomActionExe.exe
InternalName: CustomActionExe.exe
OriginalFilename: CustomActionExe.exe
FileVersion: 3.1.7.3719
FileDescription:
Product:
ProductVersion: 3.1.7.3719
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\VaultService\VaultShadowFolder\
/CUSTOMNAME=
/CUSTOMPW =
/PARAMS=
/DROPDB=
/UPGRADE=
/ADMINPW=
/SQLLOGIN=
/SQLPW=
/WINAUTH=
/SQLSERVER=
/IISPROCESS=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 Vault Admin user password... Hashing the admin pw
OK
Connecting to the SQL Server...ConnectToDatabase() completed successfully.
dialog result for uninstall is: OK
Checking if this machine is a domain controller
OK
Verifying the SQL Server requirements... User is trying to install Vault to Microsoft SQL version 9.00.1399.06
OK
Checking for an existing Vault database...Found.
Asking for database's fate...Keep existing.
Upgrading the existing Vault database...
This may take a long time. Do NOT stop this process!
Checking the installed database version... 3.0.0.3
OK
Putting sgvault database in single-user mode.
Upgrading existing database user information with a new install.
UpgradeExistingDB (revoke) failed for sgvaultuser:
User or role 'sgvaultuser' does not exist in this database.. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CustomActionExe.SetupForm.ExecuteSqlCommand(String cmd)
at CustomActionExe.SetupForm.UpgradeExistingDB(Boolean bOverwriteUserInfo)
Login begin used is: POWERNODES\NP00301BB942B9$
POWERNODES\NP00301BB942B9$ exists. Nothing to do.
GrantLogin completed successfully.
Grant database access to POWERNODES\NP00301BB942B9$...OK
Executing commands in vault_upgrade.sql
Read vault_upgrade.sql from our resources.
Upgrading SourceGear Vault database schema...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.
Caution: Changing any part of an object name could break scripts and stored procedures.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280ExitCode on exit: -1280

ian_sg · Post by **ian_sg** » Thu Mar 02, 2006 12:52 pm

If you can run the trace to see the last command to run, that would help.

Two other things to try:

1) Do you still have a SQL Server 2000 install to try the upgrade on?

2) It might be necessary to backup on 2000 and restore on 2005, rather than detach/attach.

Thona · Post by **Thona** » Thu Mar 02, 2006 12:56 pm

That seriously sucks. Really. Db upgrade should handle that.

Ok, I can get another copy of the database and get a sql server 2000 installed.

What is the proper procedure for that? I mean, step by step, so that that DOES run through?

dan · Post by **dan** » Thu Mar 02, 2006 2:06 pm

Note that a Vault upgrade only updates the Vault database. It doesn't upgrade a database from SQL 2000 to SQL 2005, which is probably where the problem is.

Ian's suggestion to backup on SQL 2000 and restore on SQL 2005 is a good one, rather than simply detach/attach. Consult general SQL documentation for how to do that, or SQL 2005 documentation for how to upgrade SQL databases from previous versions.

Post by **jclausius** » Thu Mar 02, 2006 2:29 pm

A quick question...

When you attached, what permissions did you grant to the sgvault database? If you are using Windows authentication, what rights did you give the database after the attachment?

It could be something as simple as looking at the security logins within the database, and making sure a Windows / SQL User has administrative / public rights to the database.

Thona · Post by **Thona** » Thu Mar 02, 2006 3:52 pm

I did not grant anything. I started the update with the sa account and it's password, assuming any script wout EITHER be smart enough to handle that itself, OR would be smart enough to give me a sensible error message.

Even after creatting a user sgvaultuser and giving it sdbo rights the whole thing just blows again, leaving the database in single user state.

The database, OTHERWISE, is fully operational. I can actually relaly go in and access the tables.

Look, I deal with databases every day. I have never had a problem like that. That seriously sucks - nothing against running automated update scripts, but the way you implement them is hilarious. If you would provide a script, I could at least see what is going on and see the real error message. I would not be left with something that just does not work. You know, as long as the user running the script has dbo permissions on the database, the rest should be your responsibility.

Ok, now let's get that under control.

Given that I have a detached database from a SQL Server 2000 and that I have only a SQL Server 2005 available, what are the exact steps to get the database updated?

Thona · Post by **Thona** » Thu Mar 02, 2006 4:08 pm

Ok, I am updating that on the original server now.

That, though, is only PART of the problem.

The database needs to move from the old server (sql server 2000) to a new machine (sql server 2005).

What is the proper way to do that?

Note that admin polivies actually deny a user name "sgvaultuser" - there are policies how database users have to be named.

Thona · Post by **Thona** » Thu Mar 02, 2006 4:21 pm

And an update - the update on the original server went flawless. Now I have to move that up to the new server.

ian_sg · Post by **ian_sg** » Fri Mar 03, 2006 9:43 am

http://support.sourcegear.com/viewtopic.php?t=924