SQL Server Performance Forum – Threads Archive

Can SQL pull in files automatically from a dir?

Hi all! I’ve just started playing with SQLS2k in Win XP. I have a situation where we have cameras in our parking garage that records snapshots of license plates. Based on the camera, exit or entrance, the picture gets dumped into a directory, there are about 10 directories. Currently, I have an access db that runs some VB that pulls in all the information from the directories, then parses out the necessary data from the image name. This has to be run manually right now. Is there a way to set something up in SQL Server that can automate this? Even if I could get it to run every hour, it would save a lot of time. Files will = about 8,000 – 20,000 pictures per day. Any help would be greatly appreciated! Thanks,
John

You can schedule a process or sequence of SQL statement using SQLAgent job scheduler, refer to books online for more information on JOBS topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the reply! Can I use some of my existing VB code to help write a SQL import command? Here is my VB code that’s currently in my access db. PART 1
—-code—-
Public Sub AddFilePaths(Path As String)
Const INSERT_INTO As String = " INSERT INTO tblFilePaths (FilePath) "
Dim strSelect As String
strSelect = "SELECT ‘" & Path & "’ AS ThePath"
Debug.Print INSERT_INTO & strSelect
CurrentDb.Execute INSERT_INTO & strSelect
End Sub
—-end code—- PART 2
—-code—-
Private Sub Command37_Click()
Dim strFileName As String
Dim strPathName As String
Dim zipTVCDP0
MsgBox "Please wait while files are being loaded. This will take a few minutes. Click ‘OK’ to load files"
‘dir 1
strPathName = "E:inetpubftproot vcdp0"
strFileName = Dir("E:inetpubftproot vcdp0*.*")
While strFileName & "" > ""
AddFilePaths strPathName & strFileName
strFileName = Dir()
Wend
End Sub
—-end code—-

This could all be rewritten in T-SQL. The master..xp_cmdshell stored procedure allows you to fire off ‘command prompt’ commands and you can capture the output and then parse it as required. Alternatively you could wrap your VB code into a COM object and have the SQL Agent job create the COM object and make method calls to it

… or even set this code in DTS package to execute. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>