VBA query SQL Server FILEPROPERTY Not Returning

[TheFitz]

Hi All,

I wonder if you can help me, I'm trying to return some values using a connection from Excel to SQL Server.

The code I'm using is as follows:

select name, filename, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , [COLOR=#EF2929][b]convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2))[/b][/color] as SpaceUsedMB
    from myDB.dbo.sysfiles a

This correctly returns each of the names, the filename and the "FileSizeMB" correctly, however, the "SpaceUsed" (Highlighted) isn't working.

I've tested it in SQL Management studio and it works correctly and I've stripped it down to the bear essentials and it still doesn't work.

select name, fileproperty(a.name,''SpaceUsed'') as SpaceUsedMB
    from myDB.dbo.sysfiles a

Anyone got any ideas why this would be the case and how I can get it to work??

Thanks all

Fitz


Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[SkipVought]

hi,

Maybe...

select
  [highlight #FCE94F]a.[/highlight]name
, [highlight #FCE94F]a.[/highlight]filename, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB

from myDB.dbo.sysfiles a

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TheFitz]

Hi Skip,

I'll give it a shot, but the problem isn't with the name or filename fields, it's with the "FILEPROPERTY" part. :-S

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[TheFitz]

Found the issue. Incase anyone else comes across this, I had connected to the Server, but had not connected to the database.

The FILEPROPERTY "SpaceUsed" returns NULL for any Database that is isn't connected to. ie I needed to specify the connection directly to that DB and then all works ok!!!

Thanks for your help skip,

Regards,

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[trevil620]

Umh, just a guess, but when I copy your SQL and try to run in SQL Server, it gives an error because you have TWO single quotes around ~SpaceUsed~ (see how I avoided trying to say or use the forbidden character?(


The speculative judgment of the quality of an answer is based directly on … what was the question again?