Migration VaultServer_3_5_1 to VaultServer_3_5_2 or 4_1_0_16

If you are having a problem using Vault, post a message here.

Moderator: SourceGear

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Migration VaultServer_3_5_1 to VaultServer_3_5_2 or 4_1_0_16

Post by sbarrette » Thu Mar 06, 2008 10:42 am

OK
Configuring your new Vault Installation
Checking for IIS Version...Requesting Vault Admin user password...OK
Connecting to the SQL Server...OK
Verifying the SQL Server requirements...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...OK
Grant database access to NT AUTHORITY\NETWORK SERVICE...OK
Upgrading SourceGear Vault database schema...'fk_tblcheckoutlists_tblfsobjects_objid' is not a constraint.
Could not drop constraint. See previous errors.
Vault Setup is exiting due to a failure or cancellation. Error Code = -1280

Is that you have a solution for this problem

lbauer
Posts: 9736
Joined: Tue Dec 16, 2003 1:25 pm
Location: SourceGear

Post by lbauer » Fri Mar 07, 2008 8:27 am

Run this SQL Query on your Vault database and let us know the results:

select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
CU2.TABLE_NAME AS PKTABLE, CU2.COLUMN_NAME PKCOLUMN from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UNION ALL
select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
(SELECT name AS PKTABLE FROM sys.objects WHERE object_id = (SELECT
object_id FROM sys.indexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME)) AS
PKTABLE,
(SELECT INDEX_COL(N'sgvault.dbo.tblfsobjects', (SELECT index_id FROM
sys.indexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME), 1)) AS PKCOLUMN
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND CU2.TABLE_NAME IS NULL
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
Linda Bauer
SourceGear
Technical Support Manager

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Tue Mar 11, 2008 10:58 am

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.indexes'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.indexes'.

this result to execute query

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

updated query

Post by drmccue » Wed Mar 12, 2008 8:10 am

My apologies: I created the query Linda sent to you. I tested it on SQL Server 2005, but not SQL Server 2000. Based on the results you posted, it appears that your Vault database is installed on SQL Server 2000, so please try this revised query and post the results for us to examine. Thank you.

Dan McCue
SourceGear

============================

select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
CU2.TABLE_NAME AS PKTABLE, CU2.COLUMN_NAME PKCOLUMN from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UNION ALL
select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
(SELECT name AS PKTABLE FROM sysobjects WHERE id = (SELECT
id FROM sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME)) AS
PKTABLE,
(SELECT INDEX_COL(N'sgvault.dbo.tblfsobjects', (SELECT indid FROM
sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME), 1)) AS PKCOLUMN
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND CU2.TABLE_NAME IS NULL
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Wed Mar 12, 2008 3:06 pm

Look picture for result
Attachments
vault.jpg
vault.jpg (164.47 KiB) Viewed 12814 times

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Wed Mar 12, 2008 3:08 pm

Env.

2 server

1 IIS (w2k3 SP2)
2 SQL (w2k3 SP2) (SQL2000 Ent. 8.00.2187 SP4)

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

need to add foreign key definitions to tblcheckoutlistitems

Post by drmccue » Thu Mar 13, 2008 8:48 am

Thank you for sending the query results. There are supposed to be 3 foreign keys defined for table [tblcheckoutlistitems], but your Vault database has none defined for that table. Please run the SQL script below to recreate the foreign keys on that table, and then attempt your upgrade again.

Best regards,
Dan McCue
SourceGear

=================================

USE [sgvault]
GO

ALTER TABLE [dbo].[tblcheckoutlistitems] ADD
CONSTRAINT [fk_tblcheckoutlists_tblfsobjects_objid] FOREIGN KEY
(
[objid]
) REFERENCES [dbo].[tblfsobjects] (
[objid]
) ,
CONSTRAINT [fk_tblcheckoutlists_tblfsobjects_folderobjid] FOREIGN KEY
(
[folderobjid]
) REFERENCES [dbo].[tblfsobjects] (
[objid]
),
CONSTRAINT [fk_tblcheckoutlists_tblfsobjectversions] FOREIGN KEY
(
[objverid]
) REFERENCES [dbo].[tblfsobjectversions] (
[objverid]
)
GO

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Thu Mar 13, 2008 11:11 am

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'fk_tblcheckoutlists_tblfsobjects_objid'. The conflict occurred in database 'sgvault', table 'tblfsobjects', column 'objid'.


Result

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

Post by drmccue » Thu Mar 13, 2008 12:39 pm

OK, something's not right here. EITHER that foreign key exists, in which case it should show up in the query that we sent which should identify foreign keys, OR it doesn't exist, in which case you should be able to create it.

Here's another idea. I'd like you to try the following query, which is the same as the last query (not the ALTER TABLE statement, but the one before it) but this one makes absolutely certain we're running against the sgvault database.

Thanks,
Dan McCue
SourceGear

===============

USE [sgvault]
GO

select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
CU2.TABLE_NAME AS PKTABLE, CU2.COLUMN_NAME PKCOLUMN from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UNION ALL
select CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
(SELECT name AS PKTABLE FROM sysobjects WHERE id = (SELECT
id FROM sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME)) AS
PKTABLE,
(SELECT INDEX_COL(N'sgvault.dbo.tblfsobjects', (SELECT indid FROM
sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME), 1)) AS PKCOLUMN
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.TABLE_NAME = 'tblcheckoutlistitems'
AND CU2.TABLE_NAME IS NULL
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Fri Mar 14, 2008 1:23 pm

Same result see picture
Attachments
vault2.jpg
vault2.jpg (210.57 KiB) Viewed 12719 times

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

New approach

Post by drmccue » Fri Mar 14, 2008 3:14 pm

Here's what we know:

1 - There are no foreign keys defined for tblcheckoutlistitems.

2 - When you tried to create the foreign keys I instructed you to create, we received a conflict error, which implies that the foreign key named in the conflict does exist.

Since the conflict is not on any foreign key defined for table tblcheckoutlistitems, let's try to find where that foreign key IS defined.

Please try the query below for me, and return the results. This slightly different query should tell us which table has a foreign key named 'fk_tblcheckoutlists_tblfsobjects_objid' defined.

I apologize for the slow pace of this investigation, but it can be difficult to troubleshoot a database when an object (like a foreign key) is not where it is supposed to be. If this query does not yield useful results, we may need to set up a remote session where I can physically see your database.

Thanks,
Dan McCue
SourceGear

===============================

USE [sgvault]
GO

select CU.TABLE_NAME, CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
(SELECT name AS PKTABLE FROM sysobjects WHERE id = (SELECT
id FROM sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME)) AS
PKTABLE,
(SELECT INDEX_COL(N'sgvault.dbo.tblfsobjects', (SELECT indid FROM
sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME), 1)) AS PKCOLUMN
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.CONSTRAINT_NAME = 'fk_tblcheckoutlists_tblfsobjects_objid'
AND CU2.TABLE_NAME IS NULL
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Mon Mar 17, 2008 2:09 pm

Hello,

I have no problem with remote session, If you want see database. Let me kwon how to proceed.

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

Post by drmccue » Tue Mar 18, 2008 2:02 pm

Sorry, I was out sick yesterday. A remote session will be the next step after running this query. If this does not yield useful results, I'll need to see your database. Until then, please run this query and post the results:

USE [sgvault]
GO

select CU.TABLE_NAME, CU.CONSTRAINT_NAME, CU.COLUMN_NAME, RC.UNIQUE_CONSTRAINT_NAME,
(SELECT name AS PKTABLE FROM sysobjects WHERE id = (SELECT
id FROM sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME)) AS
PKTABLE,
(SELECT INDEX_COL(N'sgvault.dbo.tblfsobjects', (SELECT indid FROM
sysindexes WHERE name = RC.UNIQUE_CONSTRAINT_NAME), 1)) AS PKCOLUMN
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
CU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND CU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON RC.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU2 ON
RC.CONSTRAINT_CATALOG = CU2.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = CU2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = CU2.CONSTRAINT_NAME
WHERE CU.CONSTRAINT_NAME = 'fk_tblcheckoutlists_tblfsobjects_objid'
AND CU2.TABLE_NAME IS NULL
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

Thanks,
Dan McCue
SourceGear

sbarrette
Posts: 9
Joined: Thu Mar 06, 2008 10:32 am

Post by sbarrette » Wed Mar 19, 2008 1:01 pm

this result
Attachments
vault3.jpg
vault3.jpg (204.16 KiB) Viewed 12587 times

drmccue
Posts: 24
Joined: Thu Jun 21, 2007 3:21 pm

remote session next

Post by drmccue » Wed Mar 19, 2008 2:54 pm

I don't understand why these queries are all returning no data. I'll need to be able to see what's happening.

Please send an email to support@sourcegear.com with your email, your phone number, and reference this forum post:

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

Also, please include times (and dates if relevant) that you would be available to have a remote session.

We'll be in touch with you after receiving that to set up the remote session.

Thanks,
Dan McCue
SourceGear

Post Reply