Import SQL Data to Word/Export Data from Word to SQL?

24 Jun 2009 11:01 AM

Andrew Staley

Is it possible using Microsoft Word, SQL Server to create a document
template that will import data into mail merge fields. And then if the data
in that field is changed, for that change to be written back to the SQL
database?

Thanks in advance, Andrew.

24 Jun 2009 11:06 AM

Uri Dimant

Andrew
Hmm, I think that is a good candidate for FILESTREAM in SQL Server 2008

Show quoteHide quote

"Andrew Staley" <spam@no.no> wrote in message
news:F9F7B776-CC96-44E0-8887-D09DDD7A15B9@microsoft.com...
> Is it possible using Microsoft Word, SQL Server to create a document
> template that will import data into mail merge fields. And then if the
> data in that field is changed, for that change to be written back to the
> SQL database?
>
> Thanks in advance, Andrew.

24 Jun 2009 12:00 PM

Linchi Shea

Sure, it's possible. You can access/manipulate Word programatically through
its exposed API and access/manipulate SQL Server through any number of data
access APIs (e.g. .NET SqlClient) in the same program.

Linchi

Show quoteHide quote

"Andrew Staley" wrote:

> Is it possible using Microsoft Word, SQL Server to create a document
> template that will import data into mail merge fields. And then if the data
> in that field is changed, for that change to be written back to the SQL
> database?
>
> Thanks in advance, Andrew.
>

24 Jun 2009 3:52 PM

Peter Jamieson

You can connect a Word 2002/2003/2007 Mail Merge Main document to a single
SQL Server data source using either ODBC or OLE DB - OLE DB is the default,
and essential if you have Unicode type text fields. However, unless
something has changed recently
a. you will only be able to connect to a Table or View, not a Stored Proc.
(actually, you can just about connect to a Table-valued function).
b. I am pretty sure you will need to be using Integrated (Windows)
Security.

You need a .odc which you would typically create starting from New Source...
button in the Select Data Source dialog in Word.

Word has a number of limitations that Excel does not appear to have when
accessing SQL Server data - for example, Word does not honour any SQL text
you put in the .odc. If you want to specify anything other than the basic
sorts/filters you can set up in the MailMerge Edit recipients dialog box,
you'll have to use Word's OpenDataSource method to specify the query, or use
a view. You ony get to use /one/ table/view; there's no parent/child stuff
in Word MailMerge out-of-the-box Mailmerge.

Incidentally, you can also use a Word DATABASE field to insert an entire
table without using MailMerge. This has other limitations and won't help you
post updates back to SQL Server.

What I can't tell you off the top of my head is whether you will be able to
edit source data in that scenario, using MailMerge Edit Recipients. I would
expect that you can as long as the data source allows it. It doesn't (can't)
impose any validation rules of its own so you would have to rely on SQL
Server constraints etc. for that.

Another approach if you are using Word 2007 is to use Content Controls
instead of merge fields and use the related Custom XML data as an
intermediary. You get to write all the code, probably in a .NET language,
but there are ome basic MS examples around and AIUI VSTO can do some of the
plumbing for you.

Peter Jamieson

Show quoteHide quote

"Andrew Staley" <spam@no.no> wrote in message
news:F9F7B776-CC96-44E0-8887-D09DDD7A15B9@microsoft.com...
> Is it possible using Microsoft Word, SQL Server to create a document
> template that will import data into mail merge fields. And then if the
> data in that field is changed, for that change to be written back to the
> SQL database?
>
> Thanks in advance, Andrew.