Home > Query, SQL > BISQL#16:TO Get All File Details From local file into SQL Server

BISQL#16:TO Get All File Details From local file into SQL Server

 

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

Advertisements
Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: