Sign in Subscribe
Microsoft Dynamics GP

SQL server FILESTREAM for Dynamics GP file attachments in coattachitems -is it possible?

Tim Wappat

• 3 min read

docattachwindow

In previous post (Managing document attachments  in Dynamics GP) I looked at the attachments feature in Dynamics GP 2013-R2 and above. I talked about how files attached to GP in this manner are stored in the database, specifically in the coAttachmentItems table. This table can get to be a huge size as a consequence of file attachments file sizes accumulating, then dwarfing the other GP tables by size and having knock on issues like size of and time taken for SQL back up and restores.

The table looks like this:

/****** Object:  Table [dbo].[coAttachmentItems]    Script Date: 17/12/2016 23:32:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[coAttachmentItems](
    [Attachment_ID] [char](37) NOT NULL,
    [BinaryBlob] [varbinary](max) NOT NULL,
    [fileName] [char](255) NOT NULL,
 CONSTRAINT [PK_coAttachmentItems] PRIMARY KEY CLUSTERED 
(
    [Attachment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The files are stored in the BinaryBlob and filename stored for reference in the filename field. GP does this in two steps, creating the database row with empty blob (0x0), then populating the blob data.

INSERT INTO coAttachmentItems (  
    Attachment_ID  
    ,fileName  
    ,BinaryBlob  
    )  
VALUES (  
    @Attachment_ID  
    ,@fileName  
    ,0x0  
    )

Now populate the blob

UPDATE coAttachmentItems  
SET BinaryBlob.Write(@Bytes, @Offset, @Length)  
WHERE Attachment_ID = @Attachment_ID

The Dex Script log shows this too with two stages or CreateAttachment then InsertAttachment methods.

22:27:30      'CreateAttachment() of form coAttachManagement', "", "0\RM\Customer Maintenance\TEST", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt", 0, 1
22:27:30          '[0]\'
22:27:30          '[0]'
22:27:30          '[0]'
22:27:30          '[0]'
22:27:30          '[0]'
22:27:30          '[0]'
22:27:30          '[0]'
22:27:30          'InsertAttachment() of form coAttachManagement', "4bb1e4e4-fe24-41ad-b102-a1f51ba76b34", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt"

In the previous post I talked about how it would be helpful if the attach feature supported SQL Server FILESTREAM. This would allow the attachment files to be stored in file storage, which is a different tier of storage, cheaper for us to manage on the file system rather than in the database. I understand there are advantages to keeping the data together in the database but this does not scale well for us.

Today I wondered if it would be possible to just implement FILESTREAM myself for attachments in this table. Although this would be naughty and definitely would not be supported, it was an interesting experiment to try.

To use file stream FILESTREAM columns must be accompanied by a corresponding uniqueidentifierROWGUID column and the column must be varbinary(MAX). These kinds of tables must also be accompanied by a unique index too.

Looking at the table, the data is being stored in a varbinary(MAX) however although the “Attachment_ID” field is a GUID in its content, sadly the content is being stored in a char(37) database data type. It needs to be a uniqueIdentifierROWGUID column to allow FILESTREAM. I suspect that GPs software language, Dexterity does not support a GUID data type, hence why its been put in a char(37). Annoyingly this means I can’t change the BinaryBlob to have a FILESTREAM attribute.

coattachitems

Thus it looks like we have hit a brick wall with trying to implement FILESTREAM this way.

Thoughts for next attempts:

  • Perhaps we can intercept the InsertAttachment method using a GP Visual Studio addin in the GP client, so as to redirect to a custom table that is backed with FILESTREAM? The problem here would be pulling the data back into the form again.
  • Perhaps we could create a new table that uses FILESTREAM and remove the existing table, replacing it with a view that converts the GUID back to char(37), so that GP is updating the view.  However this is going too far in changing the native product tables, I would not be comfortable with this nature of “table hacking”, even if it technically worked I’d be afraid of it breaking the GP install in the future.

Let me know what your thoughts are with a comment against this post.