How do we change the attachment path for files in the HEAT database after moving the files to a new location?

Version 1

    Details

    Change attachment path in the database


    Resolution

     

    Use the following SQL scrip to update the attachment references in the HEAT database data. You will need to replace the following text in the script with your specific path data:

    '\\OLDservername\OLDfoldername','\\NEWservername\NEWfoldername'

    UPDATE HEAT ATTACHMENT PATHS{

     

    SELECT GName, gdetail

    FROM dbo.HEATGen

    where GCode in ('at','ap')

    DECLARE Heat_cursor CURSOR FOR

    SELECT Gname, GDetail

    FROM dbo.HEATgen

    DECLARE @Gname AS VARCHAR(10)

    DECLARE @GDetail AS VARCHAR(8000)

    DECLARE @GDetail_Temp AS VARCHAR(8000)

    OPEN Heat_cursor

    FETCH FROM Heat_cursor INTO @Gname, @GDetail

    WHILE @@fetch_status = 0

    BEGIN

    SELECT @GDetail_Temp = REPLACE(@GDetail,'\\OLDservername\OLDfoldername','\\NEWservername\NEWfoldername')

    UPDATE dbo.HEATGen SET GDetail = @GDetail_Temp WHERE CURRENT OF Heat_cursor

    FETCH next from Heat_cursor INTO @Gname, @GDetail

    END

    CLOSE Heat_cursor

    DEALLOCATE Heat_cursor