# Database Discussions > Microsoft SQL Server 2005 >  xp_getfiledetails in SQL server 2005?

## ravi

I  have been using xp_getfiledetails in SQL server 2000 but it is not supporting in SQL server 2005. Do we any alternate way to get the file size details?  My goal is need to get the FLAT file size which I'm exporting from table.

----------


## MAK

--try this first
http://www.databasejournal.com/scrip...le.php/3443911

--if this doesnt work then try this

--let us assume your dirpath is C:\
--let us assume your filename is test.csv
--you could do the following

create table #dir (name varchar(200))
insert #dir exec master..xp_cmdshell 'dir  c:\test.csv | findstr test.csv'
delete from #dir where name is NUll
--select * from #dir
select replace(replace(name,left(name,23),''),'test.csv',  '') from #dir
drop table #dir

--if this doesnt work
--create a CLR function to check the file size.

----------


## ravi

Mak,
Unfortunately for some security reason I can't use xp_cmdshell also. 
I'm storing my file in text mode with .dat extn.
Please provide me If you have any  CLR function script for my requirement.
Thanks

----------


## rmiao

But xp_getfiledetails is not in sql2k5.

----------


## mrdenny

I've got a request open with Microsoft to have xp_getfiledetails back in SQL Server.  You can go here https://connect.microsoft.com/SQLSer...dbackID=126180 and vote on the request and add comments (I think).

The more people that vote on the item the more responsive Microsoft will be.

Denny

----------


## wilsonmunoz

First, copy the filename PRJUtilsSQL2000.dll  in the server,  after register with REGSVR32,  example:
REGSVR32 C:\WINDOWS\SYSTEM32\PRJUtilsSQL2000.dll

after execute the next code in SQL Query analyzer:

*
use master
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

if exists(select 1 from sysobjects where name='xp_getfiledetails')
   drop procedure xp_getfiledetails
GO

create procedure xp_getfiledetails (@filename varchar(200))
AS
declare @nombre             varchar(60),
        @size               int,
        @creation_date      varchar(10),
        @creation_time      varchar(10),
        @last_written_date  varchar(10),
        @last_written_time  int,
        @last_accessed_date varchar(10), 
        @last_accessed_time int,
        @attributes         int

DECLARE @Object         int
DECLARE @Hresult        int
declare @ret            int

EXEC @Hresult = sp_OACreate 'PRJUtilsSQL2000.clsUtilidades', @Object OUT;

EXEC @Hresult = sp_OAMethod @Object, 'xp_getfiledetails', @ret out, @filename

if @ret = 1 
begin
   EXEC @Hresult = sp_OAGetProperty @Object, 'Alternate_Name', @nombre OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Size', @size OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Creation_Date', @creation_date OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Creation_Time', @creation_time OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Last_Written_Date', @last_written_date OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Last_Written_Time', @last_written_time OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Last_Accessed_Date', @last_accessed_date OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Last_Accessed_Time', @last_accessed_time OUT
   EXEC @Hresult = sp_OAGetProperty @Object, 'Attributes', @attributes OUT
   select 'Alternate Name' = @nombre,
          'Size' = @size,
          'Creation Date' = @creation_date,
          'Creation Time' = @creation_time,
          'Last Written Date' = @last_written_date,
          'Last Written Time' = @last_written_time,
          'Last Accessed Date' = @last_accessed_date,
          'Last Accessed Time' = @last_accessed_time,
          'Attributes' = @attributes
end
else
   print 'Archivo no existe, verifique por favor'


EXEC @Hresult = sp_OADestroy @Object


GO
*


Then we would have the store procedure in our environment SQL2005.

Greetings  :Smilie: 

P :Big Grin: . The Souce Code of PRJUtilsSQL2000.dll and the file dll, can download from: http://www.fileserve.com/file/zv4TsBj

----------

