SQL select command to create CLIP ID file

[PetBert]

Hello everybody,

I know it is not a very difficult question but I need some help....

I have been searching the knowledge base for the command but unfortunately without success.

We have a Centera as storage for Enterprise Vault 8.0 SP5 and a SQL server 2005 SP2. What I need to do is to create a text file containing the list of all CLIP IDs.

I found the Clips in the database VaultStore1 in the table dbo.Collection and there in the column RelativeFilenames.

Can anybody help me with the correct SQL command syntax to store the Clip IDs in a local text file?

Thanks a lot.

Regards Peter

[Wayne_Humphrey]

Please see:

http://www.mssqltips.com/tip.asp?tip=1633

[RahulG]

Technote explaining how to generate the Clip IDs for the Centera device:
http://www.symantec.com/business/support/index?page=content&id=TECH43224
The two queries included in the Technote allow you to get the Clip ID for an item that uses normal storage or collections. In your case you are looking for the entire list of Clip IDs from the various Vault Stores. To generate a list of all Clip IDs you just have to remove the AND clause contained in the WHERE statement of the query as the AND is used to filter the result to a single Clip ID.
Example:
SELECT StoreIdentifier
FROM SavesetStore, Saveset
WHERE Saveset.SavesetIdentity = SavesetStore.SavesetIdentity
Notice the line:
AND Saveset.IdTransaction = '<id transaction of saveset>'
is now missing from the query above. You will need to do the same for both queries in the Technote to get the list. These queries need to be run against all of your vault stores to generate a complete list. Run these queries in your SQL Query Analyzer and have the output go to a file as opposed to Text or the Grid. To do this Open the Query menu item and navigate to the menu option Results To. Select Results To File. This will allow you to run the query and save the results to a text file .
 

[torsque]

Maybe, you can get the result directly in Excel with a SQL connection. It will be easy. 

[PetBert]

Thanks for your help! You brought me already some steps further.

We are using collections. Following the Tech Notes and your comments I tried it with this:

USE VaultStoreDBNAME1

SELECT RelativeFileName 
FROM Collection, Saveset
WHERE Collection.CollectionIdentity = Saveset.CollectionIdentity

Unfortunately the result is not what I expected.

The number of items does not match the number of lines in the dbo.Collection table. It is about 8 times as much. 

What mistake am I doing?

[PetBert · Accepted solution]

Okay, I got it:

USE VaultStoreDBNAME1

SELECT RelativeFileName 
FROM Collection
WHERE Collection.RelativeFileName is not null

Seems this is showing the expected results.

Thanks a lot to everybody!