Results 1 to 6 of 6

Thread: xp_getfiledetails in SQL server 2005?

  1. #1
    Join Date
    Nov 2002
    Posts
    231

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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.

  3. #3
    Join Date
    Nov 2002
    Posts
    231
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    But xp_getfiledetails is not in sql2k5.

  5. #5
    Join Date
    Jul 2006
    Posts
    1

    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

  6. #6
    Join Date
    Apr 2011
    Location
    Panama, but I'm born in Guayaquil, Ecuador
    Posts
    1

    Thumbs up 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
  •