March 30, 2004 at 5:12 pm
I would like to write a stored procedure that looks at all .PDF files in a given directory on a server and checks if a record with that filename exists on a particular SQL table. If not, the SP would then create a new record in the SQL table. Can anyone tell me if this is possible and, if so, suggest a good way to do it?
Thanks...PC
March 30, 2004 at 7:09 pm
Actually with the complications of doing it with an SP and permissions I always found it easier to use DTS with an Active Script object. In it use FIleSystemObjects and look for the files and do all you need. DOn't have example handy but there are plenty on the web. Even try http://www.sqldts.com , unless you are adamant about doing in an SP.
March 31, 2004 at 1:14 am
It's easy to do in .Net. As long as the server is of a decent spec, you should have too much trouble. I've done something similiar myself in the past month or so.
Have a look around on http://www.csharpfriends.com and you should be able to figure it out.
March 31, 2004 at 4:40 am
If you want to do this using a stored procedure, maybe the code below will help...
DECLARE @RowCnt int
CREATE TABLE #DirList (RowId int IDENTITY (1,1), OneField varchar(2000))
INSERT INTO #DirList
EXEC master..xp_cmdshell 'dir \\SERVERNAME\DIRECTORY(Shared If Required)\*.*'
SET @RowCnt = @@ROWCOUNT
SELECT SUBSTRING(OneField, 40, LEN(OneField)-39)
FROM #DirList
WHERE SUBSTRING(OneField, 40, 20) NOT IN ('.', '..', ' bytes', ' bytes free')
AND RowID BETWEEN 6 AND (@RowCnt - 3)
AND SUBSTRING(OneField, 25, 5) NOT LIKE '<DIR>'
/*
Compare output above with the DB Table value and
do insert if required
*/
DROP TABLE #DirList
Nikki Pratt
Development DBA
March 31, 2004 at 6:47 am
change this to : (add switches /b/s)
EXEC master..xp_cmdshell 'dir \\SERVERNAME\DIRECTORY(Shared If Required)\*.PDF /b/s'
change *.* to *.PDF for a bit of a performance boost
and you can get rid of the complicated WHERE clause in the succeeding SQL statement. Regards
also... adding the /b/s will generate 1 null record. delete that 1 null record before joining the results to your table.
You can eliminate this complex query below
SELECT SUBSTRING(OneField, 40, LEN(OneField)-39)
FROM #DirList
WHERE SUBSTRING(OneField, 40, 20) NOT IN ('.', '..', ' bytes', ' bytes free')
AND RowID BETWEEN 6 AND (@RowCnt - 3)
AND SUBSTRING(OneField, 25, 5) NOT LIKE '<DIR>'
By using Select * from #DirList.
join (left/right whatever you fancy) to your table to find what's missing
March 31, 2004 at 4:11 pm
Thank you ANTARES686, PINHEAD and NPRATT. With the references and scripts you all offered, I'm well on my way to accomplishing the task. Your help is very much appreciated!
PC:
March 31, 2004 at 5:21 pm
In Cold Fusion this is literally about 3 lines of code.
That is a FACT!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply