Mailmerge from SQL Server 2000 Stored Procedure

[memead]

Is there any definitive guide on using a SQL Server 2000 stored procedure to
gather data for a mailmerge on Word 2003? I have a VB6 app that is currently
using an Access 97 MDb to assist in a Word 2003 mailmerge and I wish to phase
out Access. I have seen information on creating an .odc file to perform a
mailmerge from a SQL Server table or view, but I would prefer to use a stored
procedure.

Much thanks.

 

[Peter Jamieson]

Is there any definitive guide on using a SQL Server 2000 stored procedure

to
gather data for a mailmerge on Word 2003?

No, at least not as far as I know.

The only way I know how to do this is to use Word VBA OpenDataSource to open
a ODBC connection that issues an ODBC call "escape" rather than a SQL
SELECT. So for example if you have an ODBC System or user DSN called mydsn
that has all the info necessary to connect to your database server and a
specific database, and you are using integrated security, you can use

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;", _
SQLStatement:="{ call mystoredprocedure }", _
Subtype:=wdMergeSubtypeWord2000

The { call } syntax is one of a number of "ODBC escapes".

One problem with this is that Word does not "see" the data in columns that
have the Unicode data types in SQL Server when it connects via ODBC. Another
possible problem is that you may find that only some procedures work - if
so, it's probably multi-step procedures that fail.

In Word 2002 and later, you really ought to be able to use a stored
procedure via OLE DB, but I have never found any syntax that works in the
SQLStatement parameter. In theory, the same ODBC { call } syntax ought to
work because it is supported in the OLE DB provider as well, but it does
not. Nor do the variants { exec }, or using a Transact-SQL EXECUTE
statement.

In other words, I don't know how you do it in OLE DB or even if it can be
done.

It does at least seem to be possible to use the results of Transact-SQL
table-valued functions as a merge data source, simply because you can
retrieve that table using a standard SELECT statement. But
a. I think that type of function only appears in later versions of SQL
Server than 2000
b. you would have to wrap every stored procedure you wanted to use in a
table-valued function to do it.

Personally, I think Microsoft should have fixed this problem years ago, or
at least provided some clues on how to use stored procedures as a data
source, if it is feasible, but I guess the fact that they haven't problably
means that corporates rarely do things this way.

 

[memead]

Much thanks. I was hoping for an easy answer. The only reason that my VB6
application uses mailmerge is to get data into a Word document for easy
editing by the users. Crystal Reports does a good job of printing reports,
but I need some way of getting the data into Word and opening up the document
in Word.

Thanks again,

Mike

 

[Peter Jamieson]

You can use Word VBA and ADO to access SQL Server data directly, but not for
use as a MailMerge data source - in other words, you either have to create
your own mailmerge facility, or use VBA to create the mail merge data source
that you need, then use that in the merge(s) you want to do (cf. the way you
seem to be using Acess as an intermediary at the moment).