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

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.

Trying to update locked view

Trying to update a view with ALTER VIEW and it does not happen immediately, then there is most likely a contention for the table with another process (user).

To find out what is blocking, start the alter statement again, then execute the following SQL, for SQL server,

sp_who2 active
 
spwho2

look at the Blk By column (blocked by) against the row that represents your login and check that the “Command” column has the ALTER command in it. There should be a number of the SPID that is blocking the ALTER command shown where the dashes are shown above.

Use that ID to kill the process (if appropriate!). Although the screen shot shows no blockers, if it had 206 as the blocker, then issuing “kill 206” would allow the ALTER to run.

Note you should understand what you are killing and apologise to the user, don’t kill a process that is going to cause data integrity issues later!

kill 206

Old SQL habits

With each new version of SQL server some old SQL habits have to die.

In Dynamics GP we often find document numbering sequences that are padded with zeros to facilitate sorting/ordering and to “look normal” on printed documentation.

For example website orders may be imported via econnect into GP in the range:

W00000001 to W99999999

Now sometimes we need to generate sequences of those numbers or join with tables holding just the number and no prefix. There are very many examples of this, originating from various reasons all now in my TSQL code, embedded in stored procedures.

DECLARE @WebOrderID int
SET @WebOrderID=22
SELECT 'W' + REPLACE(STR(@WebOrderID, 7), SPACE(1), '0')

The above shows a contrived example, to give us the result: W0000022 by overlaying on a template.er

The above was the way we did things in the 90s, new kids these days (starting with SQL 2012), can use the much simpler to read and use  FORMAT command . A command that supports standard .NET format strings.

DECLARE @WebOrderID int
SET @WebOrderID=22
SELECT FORMAT(@WebOrderID,'W#00000000')

This produces  much more easily read SQL and assuming you don’t need to be backward compatible, it seems the best way to go forward.
 
Old habits die hard, I need to start thinking FORMAT for these kinds of problem where pre-SQL 2012 support is not required. 

Beware diacritic characters where integrating with SQL

I’m certain you all know the above and practice it regularly. First a little background…

In Dynamics GP we wrote a very basic “CRM like” system using a .NET GP Addin, that lays over the top of the SOP module. It introduces the concept of contact records, with many-many relationship to customers/debtors in GP.  The list of contacts associated with an account can be viewed from a sales order and debtor card. The contacts are syncronised to MailChimp (saas email marketing). Marketing click through and email opens are also synced back to be shown next to the contact record. The contacts are also synchronised with the various ecommerce websites that feed GP, contacts being soft linked to website users.

The website integration means there is a merge required to accommodate new and updated records when users update details on the websites. This is where my oversight came to light. Duplicate records were being created, it turned out to be due to diacritics. Below is an example of a duplicate record.

FirstName
Kristján
Kristjan

The example shows what we know they are the same person, but SQL MERGE statement, due to the default collation on the database, sees these as the same. Instead it sees two distinctly different names and thus creates a new contact record for the second instance, where it should (in our case) be merging changes into the first instance. This is an over simplified version of what happened as there are other keys involved and lot of business rules. Obviously SQL is not doing anything wrong but it is not our desired behaviour for this particular task.

It is easy to resolve, when comparing records, for our purpose, we override the default collation and use a Accent Insensitive (AI) version instead, for example:

COLLATE Latin1_general_CI_AI

where “AI” at the end of the collation name is the key to the insensitive comparison.

WHERE
t2.FirstName = t1.FirstName COLLATE Latin1_general_CI_AI

The implementation depends on your own needs, my point for this post is to not forget about this issue if merging data from different sources where there may be a mixture of diacritic and non-diacritic text entered. Integration of data continues to have its challenges…