Capture Directory Contents

[sleezy]

I would like to block certain tables in the database and ensure that other users don't have delete rights on database. i could use the revoke statement but some of these users have admin rights. How can l achieve this ?

The second question is.On a daily basis l pick up a text file from the D:\Datadrive. l would like to process it only if the Datemodified is today's Date otherwise do nothing. Currentlt l just import the file into a temp table and insert into the table only if there are new records. Is is possible to do that using sql server

[MAK]

Second Question:
If you are using BULK INSERT statement then you can use the following function to check the modified date.
use master
go
create function udf_GetFileModifieddate (@filename varchar(1000))
returns datetime
as
begin
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int
DECLARE @date varchar(100)
DECLARE @Flag bigint
set @Flag =0
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXECUTE @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT,@filename
EXECUTE @OLEResult = sp_OAGetProperty @FileId,'DateLastModified', @date OUT
--print @date
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
return @date
end

go

select master.dbo.udf_GetFileModifieddate ('c:\io.sys') as date


or



exec master..xp_getfiledetails 'c:\io.sys'

[MAK]

First Question:

SA are mesiahs. You cant do anything about it.

[Rawhide]

If you are using DTS, you can do an ActiveX script that checks the properties of the file and handle accordingly. One way is to have it raise an error if the file isn't updated and if the step fails, don't process it and if it is succesful, process it.

Here is an ActiveX script I use to move files from our ftp server if they are new to a stagin server. If there are no files on the staging server, then I know we did not receive new files.

Function Main()
	Dim oFSO
	Dim oFolder
	Dim oFile
	Dim srcPath : srcPath = "\\FTPServer\ftp$\PhotoDir\"
	Dim destPath : destPath = "D:\DataDir\Photos\"
	Dim iCount : iCount = 0
	Dim oCon
	Dim sSQL
	Dim sDSN : sDSN = "Driver={SQL Server};Server=" & DTSGlobalVariables( "ServerName" ).Value & "; Database=Master;Trusted_Connection=YES"
	Dim sendMailTo : sendMailTo = DTSGlobalVariables("EmailTo").Value

	Set oFSO = CreateObject("Scripting.FileSystemObject")
	Set oFolder = oFSO.GetFolder(srcPath)
	Set oFSO = nothing

	For each oFile in oFolder.Files
		If Right(LCase(oFile.Name), 4) = ".zip" Then
			If DateDiff("d", oFile.DateLastModified, Now()) <= 1 Then
				oFile.Copy destPath, True
				iCount = iCount + 1
			End If
		End If
	Next

	If iCount = 0 Then
		Set oCon = CreateObject("ADODB.Connection")

		oCon.Open sDSN

		sSQL = "Exec Master..xp_sendmail @recipients='"  & sendMailTo & "', @subject='No current photo zip files found'"

		oCon.Execute(sSQL)

		oCon.Close
		Set oCon = nothing
	End If

	Main = DTSTaskExecResult_Success
End Function

[sleezy]

Thanks guys tried both options and customised them to our environment.


---Poll For The Latest File
DECLARE @DATEMODIFIED DATETIME
SET @DATEMODIFIED = (Select Master.dbo.udf_FileModifieddate ('D:\Test\Trans.txt'))

IF (SELECT CONVERT(CHAR(10),@DATEMODIFIED,120)) =CONVERT(CHAR(10),GETDATE(),120)
BEGIN
EXEC Master..xp_cmdshell N'dtsrun /S BNFAG /E /N dtstest'

END
ELSE
EXEC XP_SENDMAIL @Recipients = 'far@Commercial.co.za'
,@Message = 'Your Tran.txt File Needs to be Updated.Please provide Me with The latest One ' --+ @DATEMODIFIED
,@Subject = 'FAILED PROCESS'