Introduction
We store a large number of items on network shares as these are the easiest place for people to have ready to access them, especially if they are not regular users of the ERP solution. Using a bolt-on to Syteline called Doc-Trak (by lake company) we then link these files to the appropriate database record.
I’m having a tidy up and trying to re-organise our network share as over the years it has become a little messy. After moving the files around, and updating the database records accordingly, I want to ensure I have every file still linked. This can be done via the form “Doc-Track Purge Attachments”, but I wanted to do it in code just to keep my SQL skills up.
The following code is similar to a couple of other posts, whereas it uses a cursor to step through records procedurally, placing results into a temporary table.
The Code
— Declare variables
declare @filename as nvarchar(1000)
DECLARE @File_Exists int
— create a table to store results
IF OBJECT_ID(‘tempdb..##PBTI_MissingDocTrak’) IS NOT NULL
DROP TABLE ##PBTI_MissingDocTrak
create table ##PBTI_MissingDocTrak (
filename nvarchar(1000)
)
declare db_cursor cursor for
select filename from lc_dt_map
open db_cursor
fetch next from db_cursor into @filename
while @@FETCH_STATUS = 0
begin
exec master.dbo.xp_fileexist @filename, @file_exists out
if @file_exists = 0
begin
insert into ##PBTI_MissingDocTrak(filename)
values(
@filename
)
end
fetch next from db_cursor into @filename
end
close db_cursor
deallocate db_cursor
select * from ##PBTI_MissingDocTrak