xp_getfiledetails in SQL server 2005?

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.

--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.

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

But xp_getfiledetails is not in sql2k5.

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

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 :)

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