select xp_cmdshell 'DIR' info plus additional data on the same results row?

[bmacbmac]

Hi. I am trying to get a bunch of UNC path data sizes. I have the following so far:

create table #temp (path varchar(100), TotalSize varchar(100))


declare @cmd varchar(1000)='powershell "(Get-ChildItem ''\\server\Archive\Richland\RICH000001'' -recurse | Measure-Object -property length -sum).sum/1MB"'
declare @path varchar(100)='\\server\Archive\Richland\RICH000001'

insert into #temp
select @path

insert into #temp
exec xp_cmdshell @cmd

This works fine, however one row has the path and my next row has the total size. For example:
\\server\archive\Richland\RICH000001
7.899834

Is there a way I can get the path and total size on the same row? For example:
\\server\archive\Richland\RICH000001, 7.899834


Something like:

insert into #temp select @path, exec xp_cmdshell @CMD

Thanks for your time!

 

[fredericofonseca]

do
declare @cmd varchar(1000)='powershell "'\\server\Archive\Richland\RICH000001 ' + (Get-ChildItem '\\server\Archive\Richland\RICH000001'' -recurse | Measure-Object -property length -sum).sum/1MB"'

insert into #temp
exec xp_cmdshell @cmd

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bmacbmac]

Thanks!

I'm getting the error...

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ''

I've snuck extra single quotes throughout and can't get it to close... Can't figure out where it's missing.

 

[Olaf Doschke]

Maybe it should rather be

declare @cmd varchar(1000)='powershell "\\server\Archive\Richland\RICH000001 " + (Get-ChildItem "\\server\Archive\Richland\RICH000001" -recurse | Measure-Object -property length -sum).sum/1MB'

The first single quote ended the string and this turned this whole line into an error. If you need single quotes within a string in T-SQL you "escape" it by doing double single quotes, eg @somestring = 'In T-SQL you delimit strings with single quote ('') and to distinguish between string end and wanting a single quote within the string double it.'

The ugly thing about that double single quote is, it looks like a double quote. I changed all inner string delimiters to double quotes, as these are normal string delimiters to powershell, unlike for T-SQL, where they are delimiters for name expressions and not for strings.

Bye, Olaf.

 

[bmacbmac]

Thanks for the follow up. I'm now getting an error:
'Measure-Object' is not recognized as an internal or external command


Howerver... I was able to resolve the error by making a few minor adjustments:

declare @cmd varchar(1000)='powershell ''\\server\archive\Richland\RICH000001\00001, '' + "(Get-ChildItem ''\\server\archive\Richland\RICH000001\00001'' -recurse | Measure-Object -property length -sum).sum/1MB"'

Here is something interesting. When I run this script I receive the following results:
\\server\archive\Richland\RICH000001\00001, 6.6825027
4658203
NULL


Any idea what the 46582303 is?

If I take the first path out:

declare @cmd varchar(1000)='powershell "(Get-ChildItem ''\\server\archive\Richland\RICH000001\00001'' -recurse | Measure-Object -property length -sum).sum/1MB"'

I just get the results:
6.68250274658203

There is no row with the 4658203 data. Any idea what this is?

Thanks!

 

[fredericofonseca]

the output should have given you the hint.

\\server\archive\Richland\RICH000001\00001, 6.6825027
4658203
NULL

6.6825027[highlight #CC0000]4658203[/highlight]

the 4658203 is the continuation of the size - but for some reason on your system the output got wrapped at 53 chars - this does not happen on my machine so it seems it will be a setting on your powershell profile or you inserted into a table with the column of that size.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bmacbmac]

haha.... Can't believe I didn't catch that.

Thanks for your help!