how to retrieve data from SQL database in Word 2010?

Question

I would like to populate a Word document with data from our MS SQL database.

Is this possible, and if yes how?

Answer 1

I have done it various ways in the past. It depends whether the user initiates the action from OUTSIDE of Microsoft Word or from INSIDE Microsoft Word.

From INSIDE Microsoft Word, you can use one of the following techniques:

1. Open a template with placeholders and use VBA or VSTO to iterate over them using a data source using copy & paste. Please note that tables are also a crime here. This approach resembles approach 1 below with "OUTSIDE". Disadvantage that it is relatively slow (copy & paste) and that Microsoft Word autocorrect likes to kick in when least needed.
2. Open a template with placeholders and use VBA or VSTO to parse the XML representation and then replace these. It is faster, but harder to write. Especially since the XML representation can contain XML fragments within the placeholders (such as '&amp;lt;&amp;lt;PUT_<xxx/>IT_HERE&amp;gt;&amp;gt;' and more complex cases). Also, you need to make sure you keep a valid XML document and well balanced.

From OUTSIDE Microsoft Word (such as web interface) you can use one of the following techniques:

1. Store a template somewhere using RTF (which is far easier to process than Word's own structure). Put '<<PLACEHOLDER-FOR-NAME>>' or similar easily recognized texts where you like to replace it. When user requests the Word document, fetch the RTF, fetch the data, replace the placeholders, server RTF to user. RTF has some restrictions, but some advantages. Advantages are: ease of creating new templates and also works with Microsoft Wordpad and other Office packages. Disadvantages are that tables are a real mess to process and that not all Microsoft Word constructs are possible. Repeating rows in a table are even less recommended. High volume can be an issue.
2. Use a reporting package that happens to also output docx, doc or RTF documents. Write a report. In general perfectly suited for high volume. Less suited if you want the end user to type a major amount of additional text, since reporting packages typically work based on pages instead on a flowing text, where sometimes an explicit or implicit page break is inserted. But if you only need the end user to enter one or two sentences in addition, it is sufficient.
3. Fat-client. Put the SQL data somewhere. Open Word. Read the data and see further techniques for from INSIDE Microsoft Word.

If you need to fill a Word document from SQL Server (or any other database or data platform) , I can suggest the free edition of Invantive Composition for filling Word documents from the database (please note that I've been involved with that product). It opens templates and merges them from within Word, but is more targeted at non-developers; just specify the template and datablocks (possibly nested) and publish. Developers can only add some C# in plugins. I think it is a good product when you have MANY templates (over 50) because it scales easier.

Answer 2

You may use microsoft query to fetch data from SQL database to your document, this video may be usefull https://vimeo.com/83983247