xp_cmdshell dir different results when passing command as variable

Question

I am creating a stored procedure to track some server's folders' used space using SQL Server 2008 R2. I have run into an interesting issue for a particular directory.

When I run EXEC master.dbo.xp_cmdshell 'dir "\\servername\e$\media\Google" /s /-C' I get a result:

However, when I use a variable for the dos command
DECLARE @dir VARCHAR(255) = 'dir "\\servername\e$\media\Google" /s /-C' EXEC master.dbo.xp_cmdshell @dir

I get a different result:

You can see that the number of files is the same, but the number of bytes used is different. Here are the details for the subdirectory with the files:

Not using variable:

Using @dir variable:

You can see that although it seems to have each of the files the same size, the total is different.

I have other subdirectories with zip files that do not exhibit this behavior. Does anyone have any ideas on what could cause this? Or how to fix?

Answer 1

Maybe it is not the answer you're looking for but I hope it helps you in some way :)

I notice in the first comparison:

181661290 - 181644906 = 16384 / 2048 = 8

So I thought maybe, for some reason, when you use the variable the command count the "cluster size" (I guess 2048 bytes) of the sub-folders...

But then I notice:

8131596 - 8119308 = 12288 / 2048 = 6

So only 6 folders are adding the 2048 bytes in there, and I don't know why.

Then you said:

Interesting, so when I run with just *.zip, the two results match each other.

Maybe adding the switch /a-d (to not show the folders) to your command line solves the problem.

I'm adding this post of sqlservercentral I found maybe it has something to do with the problem:

CompressedBit AS CASE WHEN Attributes&2048=2048 THEN 1 ELSE 0 END,