SQL Server Mentalist

Hi Friends,

After long posting of CDC I think I will discuss first easy thing rather than hard one.

Todays Stored Procedure is quite simple and easy to implement

Requirement:We have File stored on locally on computer,We need to find all required properties related to file

Solution:We just have to execute following Stored procedure

CREATE PROCEDURE xp_getfiledetails 
@filename NVARCHAR(255) = NULL --(full path)
AS
DECLARE @fileobj INT , @fsobj INT
DECLARE @exists INT, @error INT 
DECLARE @src VARCHAR(255), @desc VARCHAR(255)

--create FileSystem Object
EXEC @error = sp_OACreate 'Scripting.FileSystemObject', @fsobj OUT
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj, @src OUT, @desc OUT 
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc
RETURN 2
END

--check if specified file exists
EXEC @error = sp_OAMethod @fsobj, 'FileExists', @exists OUT, @filename

IF @exists = 0
BEGIN
RAISERROR 22004 'The system cannot find the file specified.'
RETURN 2
END

--Create file object that points to specified file
EXEC @error = sp_OAMethod @fsobj, 'GetFile' , @fileobj OUTPUT, @filename
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj
RETURN 2
END

--Declare variables holding properties of file
DECLARE @Attributes TINYINT, 
@DateCreated DATETIME, 
@DateLastAccessed DATETIME,
@DateLastModified DATETIME,
@Drive VARCHAR(1),
@Name NVARCHAR(255),
@ParentFolder NVARCHAR(255),
@Path NVARCHAR(255),
@ShortPath NVARCHAR(255),
@Size INT,
@Type NVARCHAR(255)

--Get properties of fileobject
EXEC sp_OAGetProperty @fileobj, 'Attributes', @Attributes OUT
EXEC sp_OAGetProperty @fileobj, 'DateCreated', @DateCreated OUT
EXEC sp_OAGetProperty @fileobj, 'DateLastAccessed', @DateLastAccessed OUT 
EXEC sp_OAGetProperty @fileobj, 'DateLastModified', @DateLastModified OUT 
EXEC sp_OAGetProperty @fileobj, 'Drive', @Drive OUT
EXEC sp_OAGetProperty @fileobj, 'Name', @Name OUT
EXEC sp_OAGetProperty @fileobj, 'ParentFolder', @ParentFolder OUT
EXEC sp_OAGetProperty @fileobj, 'Path', @Path OUT
EXEC sp_OAGetProperty @fileobj, 'ShortPath', @ShortPath OUT
EXEC sp_OAGetProperty @fileobj, 'Size', @Size OUT
EXEC sp_OAGetProperty @fileobj, 'Type', @Type OUT

--destroy File Object
EXEC @error = sp_OADestroy @fileobj
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fileobj
RETURN
END

--destroy FileSystem Object
EXEC @error = sp_OADestroy @fsobj
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj
RETURN 2
END


--return results
SELECT NULL AS [Alternate Name], 
@Size AS [Size], 
CONVERT(varchar, @DateCreated, 112) AS [Creation Date], 
REPLACE(CONVERT(varchar, @DateCreated, 108), ':', '') AS [Creation Time], 
CONVERT(varchar, @DateLastModified, 112) AS [Last Written Date], 
REPLACE(CONVERT(varchar, @DateLastModified, 108), ':', '') AS [Last Written Time], 
CONVERT(varchar, @DateLastAccessed, 112) AS [Last Accessed Date], 
REPLACE(CONVERT(varchar, @DateLastAccessed, 108), ':', '') AS [Last Accessed Time], 
@Attributes AS [Attributes]

--EOF-- 
 

Before that you need to make sure about configuration of following SQL Server Option

/**********************************************************************/
--Execute the code only once
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Test Run for same Stored Procedure :

/***************************************************************************************/
--Test RUN   DECLARE @FILEPATH VARCHAR(50) set @FILEPATH='D:\new\BBP.dtsConfig' EXEC xp_getfiledetails @FILEPATH

Hope this helps !!!!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 17 :How to determine the version and edition of SQL Server

· BISQL # 18 :How to Backing up a Mirrored database using SQL Agent

· BISQL # 19 :How should we store Images and BLOB files in SQL Server

· BISQL # 20 :Why should I Learn SQL

· BISQL # 21 :All White Paper For SQL Server R2 Review and Better Understanding

Hope you will like this post on How to get all file details from local file into SQL server.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

                  For More information related to BI World visit my Mentalist Blog

                            Link Resource Blog >> Daily Interesting links

                           SQL Server Mentalist >> SQL Learning Blog

                           Business Intelligence Mentalist >> BI World

                                        Connect With me on

               | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                 Copyright © 2011 – 2012 Vishal Pawar