Migration VaultServer_3_5_1 to VaultServer_3_5_2 or 4_1_0_16

[sbarrette]

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]

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'

[sbarrette]

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]

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]

Look picture for result

[sbarrette]

Env.

2 server

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

[drmccue]

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]

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]

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]

Same result see picture

[drmccue]

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]

Hello,

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

[drmccue]

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]

this result

[drmccue]

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