Jeffrey Williams wrote:
Okay - let's take a step back...can you outline what you need to do with this procedure? Maybe there is a better way to approach the problem. This sounds like something that might be better solved using an agent job running a PS script - but not sure.
Gladly – if you can come up with a better way to do this, I will be delighted. One of my pet peeves with SQL Server is the lack of decent access to reading a machine's file/folder structure.
The database is a museum collection management system. There are around 130,000 records in the primary table and anywhere from a few to a few thousand records in several dozen auxiliary tables - nowhere near any of SQL Server's limits, but large enough to have performance problems if I design poorly. The gist of the whole problem is, in the folder that contains the database files, there is a subfolder/subfolders with around 60,000 'attachment' files, mostly photographs of the collection objects. Rather than putting the photographs into the database, or trying to make some solid link from record to photographs, I have implemented a naming scheme. The record ID numbers have a specific format – one or two letters, a number, and an optional one or two more letters, or plus sign, or minus sign. When the user opens the details of a record, the database looks for files which are named beginning with the ID for that record. Record F1 will find and display the name of files F1.jpg, F1_sideview.tif, F1 notes.txt, and so on, but will NOT pick up F1a.jpg, F1-damages.docx, F11.bmp, etc. The code in the stored procedure to correctly isolate the ID number and discard things that look similar but are not correct works quite nicely. Initially, I had the procedure go directly to the disk and show what it found. However, the girl doing most of the photography for this system is rather industrious, and the number of photographs soon grew to volumes that made this impractical. I then tried putting a 'Show attachments' button on the detail form, to cut down on the time needed to display a record, but that also didn't work well. It still took an unacceptably long time, even when it was not needed all that often (users don't need to see this info on every interaction with a record), it was a PIA for the user to have to click the button each time, when I made it a toggle, it was a PIA to remember to turn it off again, and it was just generally clunky. I then made a separate table into which I copy the filenames, internal ID of the record, and a few other details. That made the response time instant, since I am simply querying a joined and indexed table of 60,000, rather than interrogating the server's file/folder structure. An additional benefit of the instant response is that I can now easily filter records that have or don't have attachments.
There are two ways for attachment files to appear in this subfolder:
1. There are command buttons in the detail view of a record for selecting a file and copying it to the server. The underlying code takes care of proper naming and placement.
2. Users (mostly the photographer) can manually name the files correctly and copy them into the server's folder via the internal network, with no interaction with the database. This is much more convenient for her, since she can name the photographs as she goes, and the simply dump an accumulated bunch of work into the folder. (There are subfolders as well – not all 60,000 files are in one folder, but that is not germane to the problem at hand. The lookups for all that work perfectly.)
I originally looked up how to use DIR commands from SQL Server and got it working, but it was years ago and I didn't really know what I was doing – I just kept trying things I found on the net until I finally got something to work. I recently migrated from the original 2008r2 to 2014, and I don't remember exactly how I did it back then - I think it was via a dummy account with credentials, but I'm not sure exactly what finally made it all work. I don't even think I really understood it back then, but it worked and I had other tasks on my plate.
The stored procedure to update the attachments table works fine, when I run it, but I have full admin privileges in the machine. Not an accident – I am the only person who services this thing, but we have a domain account system in the institution, and users have various local privileges in various machines. I don't want to give all user accounts admin privileges in the machine, and it's not necessary – I had it working once without them, but I had hardcoded the name of the subfolder, rather than reading it from the database, as I am trying to do now. The procedure is run unattended from a service account every night, to pick up files that have been simply dropped into the folder, and is run each time a file is added via the database. It has parameters to specify whether to update the table for one record, an entire catalog (specified by the leading letter(s) of the ID) or the entire dataset. When a user adds a file via the detail form, it updates only records for that one ID, but they can manually specify that they want to update an entire catalog, if they want to see some stuff that was manually added just that day, or the entire dataset, if there is reason to suspect that there may be a problem. (There are also procedures for locating 'orphan' files – files which are not named correctly, or are named correctly but have no corresponding record in the database. This does happen – the photographer may take pictures of something that has not yet been recorded in the database. Not a problem, when a record is created, the update routine will pick up the appropriate files on the nightly maintenance run, or the user can immediately request an update for just that one new record, and the stored procedure will find those previously 'orphan' files and add them to the attachments table.)
Sorry about the length of this post – I do go on, don't I?
Anyway, in a nutshell:
I need to read the file structure of my server.
If I run under the user's permissions, I can get the location of the database, but I cannot read the file structure.
If I run under different permissions, I can read the file structure, but I can't get the location of the database - as I wrote in the original post, the query:
SELECT physical_name FROM sys.master_files...