Daniel Krüger

7 minute read BPS Version: 2025.2.1.179

Overview

If you are wondering why this is necessary, this post is probably not for you. But the reason is that you cannot change the attachment database after an attachment has been added using the Designer Studio. Nevertheless, with a little bit of understanding of the database structures and SQL, this isn’t a problem. :)

If it doesn’t work from the Designer Studio we use another way.
If it doesn’t work from the Designer Studio we use another way.

Test scenario

I’ve set up a standard process which saves the attachments in the default attachment database.

Standard process which stores attachment in content db.
Standard process which stores attachment in content db.

I added multiple documents to a workflow instance:

  • Quote for bikes 2023-09-15.docx I’ve modified this in Word a few times
    • Version 1
      /attachments/db/23/preview/236?fileversion=0&isdesignerdesk=0&authkey=
    • Version 2
      /attachments/db/23/preview/236?fileversion=1
    • Version 3
      Has been modified in the same step /attachments/db/23/preview/236?fileversion=2
    • Version 4
      /attachments/db/23/preview/236?fileversion=3
  • Credit rating as of 2023-09-17.docx Has been added in one step and deleted at a later step
  • Overwriting Credit-rating as of 2023-09-17.pdf with capa-report.pdf
    The attachment id is the same /attachments/db/23/preview/237?fileversion=0
    /attachments/db/23/preview/237?fileversion=1

In the below screenshot we can see that the content database is accessed to fetch the attachment file. I’ve used this approach to verify that the copied files will be read from the correct database, before deleting them from the content database.

SQL Profiler trace shows that the content database is used.
SQL Profiler trace shows that the content database is used.

Info: If you want to test this, you shouldn’t click on all attachments for the preview. It seems that WEBCON BPS somehow caches the previewed document.

This SQL queries returns all the attachment files

/* Using the GUID will allow us to execute the same script on different environments */
declare @defId int = (select DEF_ID from WFDefinitions  where DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c')
select  WFAttachmentFiles.ATF_ATTID, WFAttachmentFiles.ATF_Version, WFAttachmentFiles.ATF_OrginalName
from 
  WFAttachmentFiles 
	join WFDataAttachmets on ATF_ATTID = ATT_ID		
	/* The V_WFElements view would be another option, but the execution will be longer because of the numerous joins */
	join WFElements on ATT_WFDID = WFD_ID
	join WFDocTypes on  WFD_DTYPEID = DTYPE_ID
where 
 DTYPE_DEFID = @defId
The current attachment files rows in the content database.
The current attachment files rows in the content database.

Moving files

Attachment database creation

If you haven’t done so already, create a new attachment database from the Tools for application management dialog in the setup.

Setup.exe -> Tools for application management -> Create attachment database
Setup.exe -> Tools for application management -> Create attachment database

Follow the advice and recycle the application pool or run iisreset from the command line in admin mode.

From content db to attachment db

This very simple query will copy the files of the defined process from the current content database to the target attachment database and change the attachment database. Make sure to read the

/* Using the GUID will allow us to execute the same script on different environments */
declare @defId int = (select DEF_ID from WFDefinitions where DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c')

insert into [DEV01_BPS_Content_DKR_Att].dbo.WFAttachmentFiles (
    ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
    ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
    ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
)
select 
    ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
    ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
    ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
from 
    WFAttachmentFiles 
    join WFDataAttachmets on ATF_ATTID = ATT_ID
    join WFElements on ATT_WFDID = WFD_ID
    join WFDocTypes on WFD_DTYPEID = DTYPE_ID
where 
    DTYPE_DEFID = @defId

update WFDefinitions
 set DEF_AttachmentsDatabase = 'DEV01_BPS_Content_DKR_Att'
where DEF_ID = @defId

Afterwards I would recommend:

  • Execute an IIS reset
  • Restart the WEBCON service
  • Reset all caches in the Designer Studio

Even so I did all this and hit the Refresh all button the Designer studio the changed database name has only be visible after a restart

The Designer Studio showed the new database name only after a restart
The Designer Studio showed the new database name only after a restart

Verification

Everything seems to be fine:

  • The profiler trace is looking good
    We now see the attachment database name
  • Preview from the form works
  • The previews from the history look fine
  • Editing existing file works
  • The new version is saved in the attachment database

Deletion of the old rows

Since everything looks good, it’s time for the real test and delete the existing rows in the database.

/* Using the GUID will allow us to execute the same script on different environments */
declare @defId int = (select DEF_ID from WFDefinitions where DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c')

delete from WFAttachmentFiles
where ATF_ATTID in (
    select ATT_ID
    from WFDataAttachmets
    join WFElements on ATT_WFDID = WFD_ID
    join WFDocTypes on WFD_DTYPEID = DTYPE_ID
    where DTYPE_DEFID = @defId
)

Reindex the process

If you haven’t deactivated the Add attachment content to SOLR in the process settings, you should reindex the process

Process settings defining, whether an attachment can be searched.
Process settings defining, whether an attachment can be searched.
Select the process
Select the process

Final tests

I could open all the previews just fine and even the search is working:

Even searching for the content works fine.
Even searching for the content works fine.

From one attachment database to another

I didn’t check this thoroughly and only wanted to test it because it’s just adding a database name to the SQL statements. :)

This is the statement for copying the files to another attachment database and the difference to the original one:

/* Using the GUID will allow us to execute the same script on different environments */
declare @defId int = (select DEF_ID from WFDefinitions where DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c')

insert into [DEV01_BPS_Content_DKR_Att2].dbo.WFAttachmentFiles (
    ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
    ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
    ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
)
select 
    ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
    ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
    ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
from 
   [DEV01_BPS_Content_DKR_Att].dbo.WFAttachmentFiles 
    join WFDataAttachmets on ATF_ATTID = ATT_ID
    join WFElements on ATT_WFDID = WFD_ID
    join WFDocTypes on WFD_DTYPEID = DTYPE_ID
where 
    DTYPE_DEFID = @defId

update WFDefinitions
 set DEF_AttachmentsDatabase = 'DEV01_BPS_Content_DKR_Att2'
where DEF_ID = @defId
Differences between the statements for copying the files
Differences between the statements for copying the files

This is the statement for deleting the files to another attachment database and the difference to the original one:

/* Using the GUID will allow us to execute the same script on different environments */
declare @defId int = (select DEF_ID from WFDefinitions where DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c')

delete from [DEV01_BPS_Content_DKR_Att].dbo.WFAttachmentFiles
where ATF_ATTID in (
    select ATT_ID
    from WFDataAttachmets
    join WFElements on ATT_WFDID = WFD_ID
    join WFDocTypes on WFD_DTYPEID = DTYPE_ID
    where DTYPE_DEFID = @defId
)
Difference between the statements for deleting the attachment files.
Difference between the statements for deleting the attachment files.

In my case SOLR throwed and error and I have no idea why. I needed to grant the WEBCON workflow service account permission to the database manually although it had been created using the setup.

Afterwards everything was working as expected.

Warnings

It’s your risk

You should be aware that any database modifications can severely damage your environment.

You need to verify everything for yourself in your own environment. Especially, if you are using OCR. I don’t have this in my environment.

Remember, it would be best if you could recreate the production database on your test environment and test it there. You don’t know how? Of course, there’s a post for this. ;)

After recreating the database, you should backup it too. This will save time, if there’s an issue with the script.

OneDrive

While it shouldn’t make a difference you should also check whether any files are currently made available in OneDrive. While I don’t think that it will have an impact, it could have.

Verify columns

I’ve tested this using version 2025.2.1.179, if you are running a different version, you should verify that you copy all columns except:

  • ATF_ID
  • ATF_RowVersion

Script improvements

Handle database growth with transaction

Depending on the amount and size of your attachment files it will not only take a while but will bloat the log file if you don’t modify the scripts. It may be necessary to add some transaction /commit commands so that the log file won’t grow to the same size as the attachment files which will be transferred. Depending on your hard disk setup this may cause problems otherwise. The below is an untested suggestion form AI

DECLARE @defId INT = (SELECT DEF_ID FROM WFDefinitions WHERE DEF_Guid = 'ec4a610b-ca59-4058-9907-53f6b3877c9c');
DECLARE @BatchSize INT = 100;
DECLARE @LastAttatchmenFileId INT = 0;
DECLARE @RowsMoved INT = 1; -- Initialize to enter the loop

WHILE @RowsMoved > 0
BEGIN
    BEGIN TRANSACTION;

    WITH BatchToMove AS (
        SELECT TOP (@BatchSize)
            ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
            ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
            ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
        FROM 
            WFAttachmentFiles 
            JOIN WFDataAttachmets ON ATF_ATTID = ATT_ID
            JOIN WFElements ON ATT_WFDID = WFD_ID
            JOIN WFDocTypes ON WFD_DTYPEID = DTYPE_ID
        WHERE 
            DTYPE_DEFID = @defId
            AND ATF_ID > @LastAttatchmenFileId
        ORDER BY ATF_ID
    )
    INSERT INTO [DEV01_BPS_Content_DKR_Att].dbo.WFAttachmentFiles (
        ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
        ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
        ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
    )
    SELECT 
        ATF_WFDID, ATF_ATTID, ATF_Value, ATF_TSInsert, ATF_TSUpdate, 
        ATF_IsDeleted, ATF_Version, ATF_AttachmentImage, ATF_OrginalValueHash, ATF_FileType, 
        ATF_CreatedBy, ATF_UpdatedBy, ATF_FlexiData, ATF_AttributesMapping, ATF_OrginalName, ATF_FRData
    FROM BatchToMove;

    SET @RowsMoved = @@ROWCOUNT;

    -- Update the last max ATT_ID for the next batch
    SELECT @LastAttatchmenFileId = MAX(ATF_ID) FROM BatchToMove;

    COMMIT TRANSACTION;
END

-- Update after all batches
UPDATE WFDefinitions
SET DEF_AttachmentsDatabase = 'DEV01_BPS_Content_DKR_Att'
WHERE DEF_ID = @defId;

Working in batches

If you can’t execute the migration during offline times, it may be necessary to split the movement into batches. This will require us to check which files have already been copied to the target database and then continue with the missing ones.

If you are doing this, wait with the change of the attachment database until the final batch.

Comments

You May Also Enjoy

Public link notes

3 minute read BPS Version: 2025.2.1.179

My learnings after using the public link / Single-use access license option

Execute form rule with parameters from JavaScript

4 minute read BPS Version: 2025.2.1.179

The Designer Studio doesn’t offer the functionality to pass parameters to form rules in JavaScript mode. This post demonstrates a workaround

Manuel mass editing

2 minute read

If you need to repeat the same action multiple times you should learn browser shortcuts

Creating tags dynamically

6 minute read BPS Version: 2025.1.1.105, 2025.2.1.179

Choose a tag (classification value) from a centralized or decentralized sourced or add one.