Mailmerge from SQL Server 2000 Stored Procedure

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.

> 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.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"memead" <memead.TakeThisOut@discussions.microsoft.com> wrote in message
news:2CF43565-ADB5-46AE-9E93-425A39927792@microsoft.com...
> 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.