-
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.
-
Getting xp_getfiledetails back in SQL Server
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
-
Solutions for xp_getfiledetails in SQL 2005
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. The Souce Code of PRJUtilsSQL2000.dll and the file dll, can download from: http://www.fileserve.com/file/zv4TsBj
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|