Need to convert returned values.

I have an application which collects WMI info from servers and stores it in
SQL. The vendor reports for displaying this leave much to be desired. I've
developed numerous replacement reports using SRS, but I'm having trouble
converting the data they collect because of the way it's formatting and
stored....can anyone help?
Sample:
Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
I want to drop all of the [-] and [+] and just leave a space between labels
and values.
Any help woul be greatly appreciated.
RCITGUYMaybe in your SQL statment you could try something like this:
SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
"RCITGuy" wrote:
> I have an application which collects WMI info from servers and stores it in
> SQL. The vendor reports for displaying this leave much to be desired. I've
> developed numerous replacement reports using SRS, but I'm having trouble
> converting the data they collect because of the way it's formatting and
> stored....can anyone help?
> Sample:
> Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> I want to drop all of the [-] and [+] and just leave a space between labels
> and values.
> Any help woul be greatly appreciated.
> RCITGUY
>|||I have to apologize for sounding stupid...but where should I place this in my
existing SQL select statement? "column holding data is
"WMIConfiguration.Configuration"
___________________________________________________________________
SELECT ComputerGroup.Name, Computer.Name AS Server,
WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
Computer.Address, Computer.PhysicalMem,
Computer.PageSize
FROM Computer INNER JOIN
WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID INNER JOIN
ComputerGroup ON Computer.GroupID =ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
_____________________________________________________________
"Aiwa" wrote:
> Maybe in your SQL statment you could try something like this:
> SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> "RCITGuy" wrote:
> > I have an application which collects WMI info from servers and stores it in
> > SQL. The vendor reports for displaying this leave much to be desired. I've
> > developed numerous replacement reports using SRS, but I'm having trouble
> > converting the data they collect because of the way it's formatting and
> > stored....can anyone help?
> >
> > Sample:
> > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> >
> > I want to drop all of the [-] and [+] and just leave a space between labels
> > and values.
> >
> > Any help woul be greatly appreciated.
> > RCITGUY
> >|||Something like this:
SELECT ComputerGroup.Name,
Computer.Name AS Server,
WMIConfiguration.ObjectType,
REPLACE(REPLACE(WMIConfiguration.Configuration,'[+]','
'),'[-]',' ') AS Configuration ,
Computer.OSVer,
Computer.Address,
Computer.PhysicalMem,
Computer.PageSize
FROM Computer
INNER JOIN WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID
INNER JOIN ComputerGroup ON Computer.GroupID = ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
"RCITGuy" wrote:
> I have to apologize for sounding stupid...but where should I place this in my
> existing SQL select statement? "column holding data is
> "WMIConfiguration.Configuration"
> ___________________________________________________________________
> SELECT ComputerGroup.Name, Computer.Name AS Server,
> WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
> Computer.Address, Computer.PhysicalMem,
> Computer.PageSize
> FROM Computer INNER JOIN
> WMIConfiguration ON Computer.ComputerID => WMIConfiguration.ComputerID INNER JOIN
> ComputerGroup ON Computer.GroupID => ComputerGroup.GroupID
> WHERE (ComputerGroup.Name = @.Company)
> _____________________________________________________________
> "Aiwa" wrote:
> > Maybe in your SQL statment you could try something like this:
> > SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> >
> > "RCITGuy" wrote:
> >
> > > I have an application which collects WMI info from servers and stores it in
> > > SQL. The vendor reports for displaying this leave much to be desired. I've
> > > developed numerous replacement reports using SRS, but I'm having trouble
> > > converting the data they collect because of the way it's formatting and
> > > stored....can anyone help?
> > >
> > > Sample:
> > > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> > >
> > > I want to drop all of the [-] and [+] and just leave a space between labels
> > > and values.
> > >
> > > Any help woul be greatly appreciated.
> > > RCITGUY
> > >