The first thing to note is that as far as I know you can only successfully
connect to some stored procedures and functions: when SQL Server executes a
stored procedure, it may produce a number of result sets. Broadly speaking,
there will be one for every SELECT that is executed, and one for some other
types of executable statement (I forget which). If you connect to the Stored
procedure using ODBC, Word seems to get the results from the first result
set (which is probably not the one you want). If you connect using OLEDB and
there is more than one result set, you will probably just get an error.
That said, if the procedure only has one SELECT and only generates one
result set, then you should be able to use the procedure as a data source.
To do it, issue an OpenDataSource call along the follwing lines (I am using
VBA syntax: you will need to adjust it when using VB.NET, but I can't tell
you how):
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myodcs\sqlserver.odc, _
Connection:="", _
SQLStatement:= "{ call myprocedure(myparameter1,myparameter2) }", _
SubType:=wdMergeSubTypeOther
You will need to create a suitable .odc file using Word. Or you can create
an empty .odc file (e.g. in Notepad) and put all the connection information
in the Connection parameter. Typically, the following would be enough to
connect to a SQL Server 2000 procedure called myprocedure with two
parameters in a database called mydb on a server called myserver.
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myodcs\empty.odc, _
Connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Data Source=myserver;Initial Catalog=mydb;", _
SQLStatement:= "{ call myprocedure(myparameter1,myparameter2) }", _
SubType:=wdMergeSubTypeOther
If the parameters are strings, quote them with single quotes, e.g.
'mystring1'. If there are no parameters, you either need myprocedure() or
just myprocedure.
If you are using SQL Server 2005 and the new SQL Server provider you'll need
to make appropriate changes.
As far as I know, the only other way to make stored procedures work is to
use a stored function (OK, so they are not precedures but they are near
enough) that returns a table as a result. In that case you can use the
following SQLStatement for a function called myfunction with a single
numeric parameter
SQLStatement:="SELECT m.* FROM myfunction(mynumber) m"
You need the alias name (m) with OLEDB connections. If you use an ODBC
connection, you don't.
You may also need to prefix the function/procedure name by the owner, e.g.
dbo.myfunction etc.
Peter Jamieson
