Database

create procedure procGetEventType
  (@EventID int, @EventHeading varchar(12))
as
  declare@desc varchar(50)
  select @desc = 'Unknown event type'

begin
  select [EventID], [EventHeading] 
  from tblEvents 
  where [EventID] = @EventID 
			
  if ([EventHeading] = 'Info')  desc = 'Information'
  if ([EventHeading] = 'Warn')  desc = 'Warning'
  if ([EventHeading] = 'Error') desc = 'Error'
		
  return @desc		
end

/* DECLARATIONS */
DECLARE @fileobj INT , @fsobj INT
DECLARE @folderobj INT 
DECLARE @exists INT, @error INT 
DECLARE @Property varchar(255)
DECLARE @src VARCHAR(255), @desc VARCHAR(255)

/* SECURITY SETTINGS */
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

/* CREATE FILESYSTEM OBJECT */
EXEC @error = sp_OACreate 'Scripting.FileSystemObject', @fsobj OUT
EXEC sp_OAGetErrorInfo @fsobj, @src OUT, @desc OUT 
SELECT CONVERT(varbinary(4),@error), Source=@src, Description=@desc

/* RETURNS NOTHING */
-- EXEC sp_OAGetProperty @folderobj, 'Name', @Property OUT
-- print @Property

/* GET FOLDER OBJECT */
EXEC sp_OAMethod @fsobj, 'GetFolder', @folderobj OUT,'C:\'
EXEC sp_OAGetErrorInfo @folderobj, @src OUT, @desc OUT 
SELECT CONVERT(varbinary(4),@error), Source=@src, Description=@desc

/* RETURN NOTHING */
--EXEC sp_OAGetProperty @folderobj, 'Name', @Property OUT
--print @Property

/* CREATE A TEMP TABLE */
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL 
	DROP TABLE #DirList 
CREATE TABLE #dirlist (FName VARCHAR(1000)) 

/* FILL TEMP TABLE WITH FILES COLLECTION */
INSERT INTO #dirlist (FName) 
EXEC sp_OAGetProperty  @folderobj , 'Files', @fileobj OUTPUT
EXEC sp_OAGetErrorInfo @fileobj, @src OUT, @desc OUT 
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc

/* SHOW FILES */
select * from #dirlist