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