May 30, 2008 at 12:27 pm
I have a SQL CLR sproc that lists the contents of a folder.
It is called from the T-SQL code like this:
BEGIN TRY
EXEC usp_ListFolder @path = N'blahblahblah';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
Output:
A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_ListFolder":
System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\WINDOWS\system32\blahblahblah'.
System.IO.DirectoryNotFoundException:
at FileIO.SendError(Exception e)
at FileIO.ListFolder(String directory)
.
I would like to change the C# code to emit a more user-friendly error message, such as:
System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\WINDOWS\system32\blahblahblah'.
Right now, my C# code looks like this:
private static void SendError( Exception e )
{
throw (e);
}
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 2, 2008 at 6:35 pm
You can't change how it outputs the errors, but you can change how you consume them. For example, you can create a simple function to clean it up like:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CleanCLRError(SqlString errorMsg)
{
return new SqlString(errorMsg.ToString().Split("\r".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)[1]);
}
You then use it in your catch block like this:
declare @err varchar(max)
begin try
exec GenerateError
end try
begin catch
print dbo.CleanCLRError(error_message())
end catch
The TSQL Equivalent to it is:
create function CleanCLRError_TSQL
(@errorMsg varchar(max))
returns varchar(max)
as
begin
return (left(substring(@errorMsg, patindex('%'+char(10)+'%', @errorMsg), len(@errorMsg)), patindex('%'+char(10)+'%', substring(@errorMsg, patindex('%'+char(10)+'%', @errorMsg)+1, len(@errorMsg)))))
end
Hope this helps.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 2, 2008 at 8:44 pm
Thank you, I will try that.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 2, 2008 at 8:49 pm
Just a note,
If I had to implement this, I would go the TSQL Route for the function over paying the cost of the CLR function in loading an AppDomain, and calling out to the CLR Stack. It just makes more sense. It is deterministic, and who cares that the code for it is really nasty, you are calling a function, so you don't have to remember the code anyway.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 6, 2008 at 11:06 pm
Marios Philippopoulos (5/30/2008)
I have a SQL CLR sproc that lists the contents of a folder.
Just curious... what columns are you returning fo this?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 7:39 am
Jeff Moden (7/6/2008)
Marios Philippopoulos (5/30/2008)
I have a SQL CLR sproc that lists the contents of a folder.Just curious... what columns are you returning fo this?
Here is some sample output:
Date Type Size Name
----------------------- ---- -------------------- ------------------------
2006-08-17 16:27:44.110 F 431 $winnt$.inf
2003-03-25 08:00:00.000 F 2151 12520437.cpx
2003-03-25 08:00:00.000 F 2233 12520850.cpx
2006-08-16 09:52:05.000 F 99840 6to4svc.dll
2005-03-24 20:55:26.000 F 35328 aaaamon.dll
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 7, 2008 at 8:02 am
I hate to say it - but you CAN change the errors if you so desire. Just don't have the CLR code "throw" the error again: instead have it create the happy error message. Let the T-SQL code raise an error if you so desire once it has the return message in hand.
Just have the CLR procedure/function catch the error, and pass the "friendlier" message back as an output parameter. Set your return code so that you can figure out that it wasn't a successful run....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 7, 2008 at 8:02 am
Thanks,
Just a couple more questions...
1) Which date are you listing... Create, Modified, or Last Accessed?
2) Are you allowed to use sp_OA* procedures?
3) Do you really need the date and the size?
The reason for all the questions is, obviously, there's an all T-SQL solution
to totally replace the need for a CLR... I just want to make sure to give you
a solution that actually does what you want it to....
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 8:08 am
Matt Miller (7/7/2008)
I hate to say it - but you CAN change the errors if you so desire. Just don't have the CLR code "throw" the error again: instead have it create the happy error message. Let the T-SQL code raise an error if you so desire once it has the return message in hand.Just have the CLR procedure/function catch the error, and pass the "friendlier" message back as an output parameter. Set your return code so that you can figure out that it wasn't a successful run....
The developers wanted to use TRY/CATCH logic to catch the errors, so I had to accommodate.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 7, 2008 at 8:16 am
Jeff Moden (7/7/2008)
Thanks,Just a couple more questions...
1) Which date are you listing... Create, Modified, or Last Accessed?
2) Are you allowed to use sp_OA* procedures?
3) Do you really need the date and the size?
The reason for all the questions is, obviously, there's an all T-SQL solution
to totally replace the need for a CLR... I just want to make sure to give you
a solution that actually does what you want it to....
1) Which date are you listing... Create, Modified, or Last Accessed?
The date is the Last Write Time.
2) Are you allowed to use sp_OA* procedures?
I don't know much about sp_OA* procedures, but I think they come with their own baggage of problems, and in any case, I don't think they are nearly as customizable and extensible as the SQLCLR solution.
3) Do you really need the date and the size?
The code is used primarily by other users who might need to use these and other columns as well, that I have not included in the returned resultset as yet.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 7, 2008 at 8:24 am
Marios Philippopoulos (7/7/2008)
Matt Miller (7/7/2008)
I hate to say it - but you CAN change the errors if you so desire. Just don't have the CLR code "throw" the error again: instead have it create the happy error message. Let the T-SQL code raise an error if you so desire once it has the return message in hand.Just have the CLR procedure/function catch the error, and pass the "friendlier" message back as an output parameter. Set your return code so that you can figure out that it wasn't a successful run....
The developers wanted to use TRY/CATCH logic to catch the errors, so I had to accommodate.
Fair enough.
I would then use Jonathan's idea, except that the "clean" function would intercept and change the error message altogether, likely based on the error number, and not the message. Build a custom errors table to pass whatever message you wish, and simply lookup the error number there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 7, 2008 at 8:25 am
Marios Philippopoulos (7/7/2008)
2) Are you allowed to use sp_OA* procedures?I don't know much about sp_OA* procedures, but I think they come with their own baggage of problems, and in any case, I don't think they are nearly as customizable and extensible as the SQLCLR solution.
Heh... how can you say that? You just said you don't know much about them. 😉
Alright... check this out...
-- CREATE PROCEDURE dbo.GetFileDetails
-- --===== Declare the I/O parameters
-- @piFullPath VARCHAR(128)
-- AS
declare @piFullPath VARCHAR(128)
set @piFullPath = 'c:\temp\'
SET NOCOUNT ON
--=======================================================================================
-- Local variables
--=======================================================================================
--===== These are processing control and reporting variables
DECLARE @Counter INT --General purpose counter
DECLARE @CurrentName VARCHAR(256) --Name of file currently being worked
DECLARE @DirTreeCount INT --Remembers number of rows for xp_DirTree
DECLARE @ErrorMessage VARCHAR(255) --the potential error message
DECLARE @ErrorObject INT --the error object
DECLARE @HR INT --the HRESULT returned from
DECLARE @IsFile BIT --1 if Name is a file, 0 if not
--===== The are object "handle" variables
DECLARE @ObjFile INT --File object
DECLARE @ObjFileSystem INT --File System Object
--===== These variable names match the sp_OAGetProperty options
-- Made names match so they're less confusing
DECLARE @Attributes INT --Read only, Hidden, Archived, etc
DECLARE @DateCreated DATETIME --Date file was created
DECLARE @DateLastAccessed DATETIME --Date file was last read
DECLARE @DateLastModified DATETIME --Date file was last written to
DECLARE @Name VARCHAR(128) --File Name and Extension
DECLARE @Path VARCHAR(128) --Full path including file name
DECLARE @ShortName VARCHAR(12) --8.3 file name
DECLARE @ShortPath VARCHAR(100) --8.3 full path including file name
DECLARE @Size INT --File size in bytes
DECLARE @Type VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)
--=======================================================================================
-- Create temporary working tables
--=======================================================================================
--===== Create a place to store all file names derived from xp_DirTree
IF OBJECT_ID('TempDB..#DirTree','U') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
(
RowNum INT IDENTITY(1,1),
Name VARCHAR(256) PRIMARY KEY CLUSTERED,
Depth BIT,
IsFile BIT)
--===== Create a place to store the file details
IF OBJECT_ID('TempDB..#FileDetails','U') IS NOT NULL
DROP TABLE #FileDetails
CREATE TABLE #FileDetails
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128), --File Name and Extension
Path VARCHAR(128), --Full path including file name
ShortName VARCHAR(12), --8.3 file name
ShortPath VARCHAR(100), --8.3 full path including file name
DateCreated DATETIME, --Date file was created
DateLastAccessed DATETIME, --Date file was last read
DateLastModified DATETIME, --Date file was last written to
Attributes INT, --Read only, Hidden, Archived, etc
Size INT, --File size in bytes
Type VARCHAR(100)) --Long Windows file type (eg.'Text Document',etc)
--=======================================================================================
-- Get all the file names for the directory (includes directory names as IsFile = 0)
--=======================================================================================
--===== Get the file names for the desired path
INSERT INTO #DirTree (Name, Depth, IsFile)
EXEC Master.dbo.xp_Dirtree @piFullPath,1,1 -- Current diretory only, list file names
-- Remember the row count
SET @DirTreeCount = @@ROWCOUNT
--===== Update the file names with the path for ease of processing later on
UPDATE #DirTree
SET Name = @piFullPath + Name
--select * from #dirtree
--=======================================================================================
--
--=======================================================================================
--===== Create a file system object and remember the "handle"
EXEC @HR = dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
SET @Counter = 1
WHILE @Counter <= @DirTreeCount
BEGIN
--===== Get the current name and see if it's a file
SELECT @CurrentName = Name,
@IsFile = IsFile
FROM #DirTree
WHERE RowNum = @Counter
--===== If it's a file, get the details for it
IF @IsFile = 1 AND @CurrentName LIKE '%%'
BEGIN
--===== Create an object for the path/file and remember the "handle"
EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName
--===== Get the all the required attributes for the file itself
EXEC dbo.sp_OAGetProperty @ObjFile, 'Path', @Path OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath', @ShortPath OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Name', @Name OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName', @ShortName OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated', @DateCreated OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes', @Attributes OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Size', @Size OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Type', @Type OUT
INSERT INTO #FileDetails
(Path, ShortPath, Name, ShortName, DateCreated, DateLastAccessed, DateLastModified, Attributes, Size, Type)
SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated,@DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type
END
SELECT @Counter = @Counter + 1
END
EXEC sp_OADestroy @ObjFileSystem
EXEC sp_OADestroy @ObjFile
SELECT * FROM #FileDetails
Do ya suppose that's just as "customizable and extensible" as a CLR for the same thing?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 8:25 am
Matt Miller (7/7/2008)
Marios Philippopoulos (7/7/2008)
Matt Miller (7/7/2008)
I hate to say it - but you CAN change the errors if you so desire. Just don't have the CLR code "throw" the error again: instead have it create the happy error message. Let the T-SQL code raise an error if you so desire once it has the return message in hand.Just have the CLR procedure/function catch the error, and pass the "friendlier" message back as an output parameter. Set your return code so that you can figure out that it wasn't a successful run....
The developers wanted to use TRY/CATCH logic to catch the errors, so I had to accommodate.
Fair enough.
I would then use Jonathan's idea, except that the "clean" function would intercept and change the error message altogether, likely based on the error number, and not the message. Build a custom errors table to pass whatever message you wish, and simply lookup the error number there.
Yeah, I like the lookup table idea, thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 7, 2008 at 8:36 am
Jeff Moden (7/7/2008)
Marios Philippopoulos (7/7/2008)
2) Are you allowed to use sp_OA* procedures?I don't know much about sp_OA* procedures, but I think they come with their own baggage of problems, and in any case, I don't think they are nearly as customizable and extensible as the SQLCLR solution.
Heh... how can you say that? You just said you don't know much about them. 😉
Alright... check this out...
-- CREATE PROCEDURE dbo.GetFileDetails-- --===== Declare the I/O parameters
-- @piFullPath VARCHAR(128)
-- AS
declare @piFullPath VARCHAR(128)
set @piFullPath = 'c:\temp\'
SET NOCOUNT ON
--=======================================================================================
-- Local variables
--=======================================================================================
--===== These are processing control and reporting variables
DECLARE @Counter INT --General purpose counter
DECLARE @CurrentName VARCHAR(256) --Name of file currently being worked
DECLARE @DirTreeCount INT --Remembers number of rows for xp_DirTree
DECLARE @ErrorMessage VARCHAR(255) --the potential error message
DECLARE @ErrorObject INT --the error object
DECLARE @HR INT --the HRESULT returned from
DECLARE @IsFile BIT --1 if Name is a file, 0 if not
--===== The are object "handle" variables
DECLARE @ObjFile INT --File object
DECLARE @ObjFileSystem INT --File System Object
--===== These variable names match the sp_OAGetProperty options
-- Made names match so they're less confusing
DECLARE @Attributes INT --Read only, Hidden, Archived, etc
DECLARE @DateCreated DATETIME --Date file was created
DECLARE @DateLastAccessed DATETIME --Date file was last read
DECLARE @DateLastModified DATETIME --Date file was last written to
DECLARE @Name VARCHAR(128) --File Name and Extension
DECLARE @Path VARCHAR(128) --Full path including file name
DECLARE @ShortName VARCHAR(12) --8.3 file name
DECLARE @ShortPath VARCHAR(100) --8.3 full path including file name
DECLARE @Size INT --File size in bytes
DECLARE @Type VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)
--=======================================================================================
-- Create temporary working tables
--=======================================================================================
--===== Create a place to store all file names derived from xp_DirTree
IF OBJECT_ID('TempDB..#DirTree','U') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
(
RowNum INT IDENTITY(1,1),
Name VARCHAR(256) PRIMARY KEY CLUSTERED,
Depth BIT,
IsFile BIT)
--===== Create a place to store the file details
IF OBJECT_ID('TempDB..#FileDetails','U') IS NOT NULL
DROP TABLE #FileDetails
CREATE TABLE #FileDetails
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128), --File Name and Extension
Path VARCHAR(128), --Full path including file name
ShortName VARCHAR(12), --8.3 file name
ShortPath VARCHAR(100), --8.3 full path including file name
DateCreated DATETIME, --Date file was created
DateLastAccessed DATETIME, --Date file was last read
DateLastModified DATETIME, --Date file was last written to
Attributes INT, --Read only, Hidden, Archived, etc
Size INT, --File size in bytes
Type VARCHAR(100) --Long Windows file type (eg.'Text Document',etc))
--=======================================================================================
-- Get all the file names for the directory (includes directory names as IsFile = 0)
--=======================================================================================
--===== Get the file names for the desired path
INSERT INTO #DirTree (Name, Depth, IsFile)
EXEC Master.dbo.xp_Dirtree @piFullPath,1,1 -- Current diretory only, list file names
-- Remember the row count
SET @DirTreeCount = @@ROWCOUNT
--===== Update the file names with the path for ease of processing later on
UPDATE #DirTree
SET Name = @piFullPath + Name
--select * from #dirtree
--=======================================================================================
--
--=======================================================================================
--===== Create a file system object and remember the "handle"
EXEC @HR = dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
SET @Counter = 1
WHILE @Counter <= @DirTreeCount
BEGIN
--===== Get the current name and see if it's a file
SELECT @CurrentName = Name,
@IsFile = IsFile
FROM #DirTree
WHERE RowNum = @Counter
--===== If it's a file, get the details for it
IF @IsFile = 1 AND @CurrentName LIKE '%%'
BEGIN
--===== Create an object for the path/file and remember the "handle"
EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName
--===== Get the all the required attributes for the file itself
EXEC dbo.sp_OAGetProperty @ObjFile, 'Path', @Path OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath', @ShortPath OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Name', @Name OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName', @ShortName OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated', @DateCreated OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes', @Attributes OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Size', @Size OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Type', @Type OUT
INSERT INTO #FileDetails
(Path, ShortPath, Name, ShortName, DateCreated, DateLastAccessed, DateLastModified, Attributes, Size, Type)
SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated,@DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type
END
SELECT @Counter = @Counter + 1
END
EXEC sp_OADestroy @ObjFileSystem
EXEC sp_OADestroy @ObjFile
SELECT * FROM #FileDetails
Do ya suppose that's just as "customizable and extensible" as a CLR for the same thing?
OK, maybe I'm a little biased, but pls take a look at the following link by Adam Machanic on the pros and cons of SQLCLR vs. sp_OA* type functionality:
http://www.eggheadcafe.com/software/aspnet/30214874/clr-vs-spoacreate.aspx
CLR vs. spOACreate - Adam Machanic
21-Jun-07 02:16:09
Aside from the MUCH nicer programming model, SQLCLR routines will tend to
perform and scale better, and you don't have the potential for memory leaks
and other nasty side-effects that you do with sp_OA*. If you have something
already working and it's fairly complex, it may be hard to argue in favor of
a re-write; but perhaps next time you go to modify the functionality you
should think about it.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 7, 2008 at 9:11 am
Jeff Moden (7/7/2008)
Marios Philippopoulos (7/7/2008)
2) Are you allowed to use sp_OA* procedures?I don't know much about sp_OA* procedures, but I think they come with their own baggage of problems, and in any case, I don't think they are nearly as customizable and extensible as the SQLCLR solution.
Heh... how can you say that? You just said you don't know much about them. 😉
Alright... check this out...
-- CREATE PROCEDURE dbo.GetFileDetails-- --===== Declare the I/O parameters
-- @piFullPath VARCHAR(128)
-- AS
declare @piFullPath VARCHAR(128)
set @piFullPath = 'c:\temp\'
SET NOCOUNT ON
--=======================================================================================
-- Local variables
--=======================================================================================
--===== These are processing control and reporting variables
DECLARE @Counter INT --General purpose counter
DECLARE @CurrentName VARCHAR(256) --Name of file currently being worked
DECLARE @DirTreeCount INT --Remembers number of rows for xp_DirTree
DECLARE @ErrorMessage VARCHAR(255) --the potential error message
DECLARE @ErrorObject INT --the error object
DECLARE @HR INT --the HRESULT returned from
DECLARE @IsFile BIT --1 if Name is a file, 0 if not
--===== The are object "handle" variables
DECLARE @ObjFile INT --File object
DECLARE @ObjFileSystem INT --File System Object
--===== These variable names match the sp_OAGetProperty options
-- Made names match so they're less confusing
DECLARE @Attributes INT --Read only, Hidden, Archived, etc
DECLARE @DateCreated DATETIME --Date file was created
DECLARE @DateLastAccessed DATETIME --Date file was last read
DECLARE @DateLastModified DATETIME --Date file was last written to
DECLARE @Name VARCHAR(128) --File Name and Extension
DECLARE @Path VARCHAR(128) --Full path including file name
DECLARE @ShortName VARCHAR(12) --8.3 file name
DECLARE @ShortPath VARCHAR(100) --8.3 full path including file name
DECLARE @Size INT --File size in bytes
DECLARE @Type VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)
--=======================================================================================
-- Create temporary working tables
--=======================================================================================
--===== Create a place to store all file names derived from xp_DirTree
IF OBJECT_ID('TempDB..#DirTree','U') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
(
RowNum INT IDENTITY(1,1),
Name VARCHAR(256) PRIMARY KEY CLUSTERED,
Depth BIT,
IsFile BIT)
--===== Create a place to store the file details
IF OBJECT_ID('TempDB..#FileDetails','U') IS NOT NULL
DROP TABLE #FileDetails
CREATE TABLE #FileDetails
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR(128), --File Name and Extension
Path VARCHAR(128), --Full path including file name
ShortName VARCHAR(12), --8.3 file name
ShortPath VARCHAR(100), --8.3 full path including file name
DateCreated DATETIME, --Date file was created
DateLastAccessed DATETIME, --Date file was last read
DateLastModified DATETIME, --Date file was last written to
Attributes INT, --Read only, Hidden, Archived, etc
Size INT, --File size in bytes
Type VARCHAR(100) --Long Windows file type (eg.'Text Document',etc))
--=======================================================================================
-- Get all the file names for the directory (includes directory names as IsFile = 0)
--=======================================================================================
--===== Get the file names for the desired path
INSERT INTO #DirTree (Name, Depth, IsFile)
EXEC Master.dbo.xp_Dirtree @piFullPath,1,1 -- Current diretory only, list file names
-- Remember the row count
SET @DirTreeCount = @@ROWCOUNT
--===== Update the file names with the path for ease of processing later on
UPDATE #DirTree
SET Name = @piFullPath + Name
--select * from #dirtree
--=======================================================================================
--
--=======================================================================================
--===== Create a file system object and remember the "handle"
EXEC @HR = dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
SET @Counter = 1
WHILE @Counter <= @DirTreeCount
BEGIN
--===== Get the current name and see if it's a file
SELECT @CurrentName = Name,
@IsFile = IsFile
FROM #DirTree
WHERE RowNum = @Counter
--===== If it's a file, get the details for it
IF @IsFile = 1 AND @CurrentName LIKE '%%'
BEGIN
--===== Create an object for the path/file and remember the "handle"
EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName
--===== Get the all the required attributes for the file itself
EXEC dbo.sp_OAGetProperty @ObjFile, 'Path', @Path OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath', @ShortPath OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Name', @Name OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName', @ShortName OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated', @DateCreated OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes', @Attributes OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Size', @Size OUT
EXEC dbo.sp_OAGetProperty @ObjFile, 'Type', @Type OUT
INSERT INTO #FileDetails
(Path, ShortPath, Name, ShortName, DateCreated, DateLastAccessed, DateLastModified, Attributes, Size, Type)
SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated,@DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type
END
SELECT @Counter = @Counter + 1
END
EXEC sp_OADestroy @ObjFileSystem
EXEC sp_OADestroy @ObjFile
SELECT * FROM #FileDetails
Do ya suppose that's just as "customizable and extensible" as a CLR for the same thing?
Jeff,
Just one little problem with your code above. The closing paren for the CREATE TABLE #FileDetails is included in your comment. It took me a bit to figure out that problem, kept getting an error near the INSERT statment following it.
😎
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply