0

I would like to store MS Word documents (docx) in SQL Server by default. What I have been able to do so far is create a varbinary(max) column in SQL Server, link to this table via MS Access, and drag Word documents into this column via MS Access (in Access it is coming up as OLE Object column). This is great and the files were saved/uploaded successfully, but ideally I would like these documents in this table by default, or when the record is created. I hope that's clear.

Here is what I've tried...

I tried to add this document via MS Access (as mentioned above). This worked fine. I copied the blob text in this column (the 0x0151... text below), and created the following contraint:

ALTER TABLE [dbo].[CM_PROJECTS] ADD  CONSTRAINT [DF_CM_PROJECTS_ATTACHMENT]  DEFAULT (0x0151...D2CC72D) FOR [CM_DOCUMENTATION]

This seems to work in SQL Server. More specifically, when I add a record to CM_PROJECTS, the CM_DOCUMENTATION column has this blob in there when I query the table in SSMS. However in MS Access, when I try to see the contents of this field, I get nothing. The field is blank in MS Access (I tried to refresh).

I also tried what was mentioned in this thread, but this didn't play nice with the ALTER command (as above). I kept getting syntax errors.

Any ideas?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Roofy
  • 1
  • 1
  • Exactly how did you extract the binary data for the default constraint? If you copied the binary literal from an SSMS result grid, the value is truncated to 64K. The length can be increased from the SSMS menu Query-->Query Optiosn-->Results-->Grid. – Dan Guzman May 24 '22 at 13:12
  • 3
    Honestly, making *every* row have such a default value seems like a bad idea; it's such a waste of space. If you have a default document you want stored then store that elsewhere as a single row, and then if the column has a `NULL` value then get the "default" value from the other table. – Thom A May 24 '22 at 13:15
  • Thanks Dan... good to know. I just copied and pasted via the result grid. – Roofy May 24 '22 at 13:30
  • 1
    Thanks Lamu, but each of these records will have this document at some point. And this will not be used much. Maybe four new records every month. Having these already attached would enhance the user experience. – Roofy May 24 '22 at 13:32

0 Answers0