Finance | Project Operations, Human Resources, ...

Where are documents saved in GP?

(0) Share

Report

Posted on by Richard Wheeler

75,848 Moderator

I was just brought into a client that has been using GP since version 1. It took them 20 years to get to a database size of 10GB. Suddenly over the last year their database has grown to over 80GB. When I asked them if they have changed anything they told me they started storing documents in the Spring of last year. When doing document attach what tables contain those images? Would it be possible to move them to a database outside of their current company database?

I have the same question (0)

All responses (5)

Answers (0)

Jeff Grant on at

Like (0)

Report

Hi Richard,

When you use the Document Attachment feature in Dynamics GP the attached document is stored as a SQL blob in the coAttachmentItems table of the company db.

I am not aware of a supported method to store the file in an alternate db as it is a static code location and not something you can choose as a default option.

Also, please note that the Document Attachment File Utility (Administration | Utilities | Company | Delete Attachment Files) will only clear out attachments that have already been manually delated in the program (and existing on the 'deleted' tab).

As such, if the attachments are causing performance issues you can back-up the existing db for historical purposes and remove the interconnected Document Attachment table data at your discretion.

As for how the data is stored, the following SQL statement will pull up the attachment data:

select * from coAttachmentItems where fileName = 'XXXXXX.docx' -- replace with your exact document/extension

And the following SQL statements should list the rest of the associated table materials for possible clearing:

select fileName,* from CO00101 where Attachment_ID = (
select Attachment_ID from coAttachmentItems where fileName = 'XXXXXX.docx')

select BusObjKey,* from CO00102 where Attachment_ID =
(select Attachment_ID from coAttachmentItems where fileName = 'XXXXXX.docx')

select BusObjKey,* from CO00103 where Attachment_ID =
(select Attachment_ID from coAttachmentItems where fileName = 'XXXXXX.docx')
-- usually empty unless changes made on the document

select BusObjKey,* from CO00104 where Attachment_ID =
(select Attachment_ID from coAttachmentItems where fileName = 'XXXXXX.docx')

select BusObjKey,* from CO00105 where Attachment_ID =
(select Attachment_ID from coAttachmentItems where fileName = 'XXXXXX.docx')

Best Regards,
Jeff
Dynamics GP Support

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Thanks Jeff, the size of the database has become an issue. They are running out of space plus their backups now take hours to complete and transfer to the cloud.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (2)

Report

Richard,

There is no way you'll be able to keep the GP company DB sleek if you start attaching documents.. They all end-up as Blob storage in a table..

The best option would be to move them into using an ISV product for document management, as those either store the data in a separate dedicated DB or on file in a share disk location.. I've seen this with Procurement systems based on Web engines, where all the attached docs are stored as a number (with an index of course) in a folder on the Web server, and an application table would hold the description and original document file name. The danger with that is if you try to duplicate a LIVE company into a TEST company, that it will carry over the same index numbers and screw up your LIVE attachments, so you have to be very careful where the stuff is stored physically.

PS: I believe Steve Endow had once written a small .NET application that allows you to retrieve easily the attachments from the GP tables.

Was this reply helpful? Yes No
Joseph Markovich 3,976 on at

Like (0)

Report

Richard-

Unfortunately, this is the fatal flaw with Document Attach in GP -- the ballooning database size.

Something I set up in the past many, many years ago (and it is still working!!) before Document Attach existed was some simple VBA customization and a $50 program called ScanToPDF that automatically scans it to a SharePoint on-premise document library tied to the master transaction number (SOP number, journal entry number, voucher number, etc).

On the GP screens, there is a button called Attachments that just opens the SharePoint document library and folder where all the docs are stored.

I am in the process of rewriting it using Visual Studio Tools and to use the OneDrive sync to SharePoint Online.

Joe

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Thanks Beat. I have used both Docuware and Papersave in the past.

Was this reply helpful? Yes No