sql server programming: Given a file name with the path i wan to find the date created or date
modified and the version associated with the file using SQL. Is there
any way we can do this. Please help.

Thanks in advance,

Hi
CREATE PROCEDURE dbo.test_GetFileVersion
(
@FileLocation VarChar(255)
)

AS
DECLARE @objectFSO int
DECLARE @objectFolder int
DECLARE @objectFiles int
DECLARE @objectFile int
DECLARE @resSP int
DECLARE @resSM int
DECLARE @resFN int
DECLARE @resError varchar(8000)
DECLARE @hr int



declare @tmpresult int
declare @Size varchar(50) ,@DateModified varchar(50),@Type varchar(50)

-- create the filesystemobject
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objectFSO OUT
IF @hr <> 0
BEGIN
SELECT 'FAILURE - Object could not be created' as Result
EXEC sp_displayoaerrorinfo @objectFSO, @hr
RETURN
END


SET @resFN = 0
SET @resError = ''

EXEC @resSM = sp_OAMethod @objectFSO, 'GetFile', @objectFolder OUT,
@FileLocation
IF @resSM <> 0
BEGIN
SELECT 'FAILURE - could not execute Method GetFile...' as Result
select @objectFSO as objectFSO, @resSM as resSM
exec sp_displayoaerrorinfo @objectFSO, @resSM
RETURN
END


EXEC @resSP = sp_OAGetProperty @objectFolder, 'Size', @Size OUT
IF @resSP <> 0
BEGIN
SELECT 'FAILURE - could not GetProperty for Size property' as Result
RETURN
END

EXEC @resSP = sp_OAGetProperty @objectFolder, 'DateLastModified',
@DateModified OUT
IF @resSP <> 0
BEGIN
SELECT 'FAILURE - could not GetProperty for datecreated property' as Result
RETURN
END

EXEC @resSP = sp_OAGetProperty @objectFolder, 'Type', @Type OUT
IF @resSP <> 0
BEGIN
SELECT 'FAILURE - could not GetProperty for datecreated property' as Result
RETURN
END

SELECT @Type AS 'File type',@Size AS 'File Size',@DateModified AS 'Date
Modified'

GO

EXEC dbo.test_GetFileVersion 'C:\Restore_DB.sql'







[quoted text, click to view]