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
Copyright © 2011 – 2012 Vishal Pawar
-
June 30, 2011 at 10:15 pmList of monthly post of MS BI and SQL blog « (B)usiness (I)ntelligence Mentalist
-
June 30, 2011 at 10:20 pmList of monthly post of MS BI and SQL blog « SQL Server Mentalist
