SQL CommServ query giving the same data 96 times

Last post 02-20-2019, 11:30 AM by CharlesL. 2 replies.
Sort Posts: Previous Next
  • SQL CommServ query giving the same data 96 times
    Posted: 02-18-2019, 7:52 AM

    I'm running this query against the comserv database and I'm getting the correct results in a XML format.However I'm getting the same information 96 times in the same row and I only want the information once. Example query is below.

    select 
    vm.vmname
    ,vm.vmclientid
    ,vm.vmhost
    ,vm.subclient
    ,vm.backupset
    ,vm.virtualizationclient
    from [dbo].[CommCellVMBackupInfo] vm INNER JOIN APP_ClientGroupAssoc a ON vm.vmclientid = a.clientId
    inner join APP_Client c on vm.vmclientid = c.id
    WHERE a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
    for xml path;
    This is the correct information that I'm receiving but It is repeated 96 times and I only want the information once as displayed below
     
    <row>
    <vmname>sauth03seaxa</vmname>
    <vmclientid>3032</vmclientid>
    <vmhost>genp-vz-a-01.rjf.vm</vmhost>
    <subclient>Dev_GenP-vz-a_02</subclient>
    <backupset>GenP-vz-a</backupset>
    <virtualizationclient>Merlin71.rjf.vm</virtualizationclient>
    </row>

    Charles Lavender
  • Re: SQL CommServ query giving the same data 96 times
    Posted: 02-20-2019, 10:00 AM

    The reason is because the result set has duplicate rows of information from the CommCellVMBackupInfo view since the data selected is a subset of the information provided.

    Please add the DISTINCT Clause to your SELECT statement to remove all the duplicate rows from the result set.

  • Re: SQL CommServ query giving the same data 96 times
    Posted: 02-20-2019, 11:30 AM

    Thanks


    Charles Lavender
The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy