Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
VB.NET Mailmerge using Stored Procedures
Does anybody know how to execute a Word mailmerge using SQL SERVER stored
procedure as a datasource, from VB.NET? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...merge/200608/1 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
VB.NET Mailmerge using Stored Procedures
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 "sbalaton via OfficeKB.com" u12388@uwe wrote in message news:64451221a761e@uwe... Does anybody know how to execute a Word mailmerge using SQL SERVER stored procedure as a datasource, from VB.NET? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...merge/200608/1 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indicate a mailmerge record number before launching Word? | Mailmerge | |||
Mailmerge picture from an Access Database | Microsoft Word Help | |||
Word Mailmerge with Access via DDE | Mailmerge | |||
Trying to suppress Select Table box in Excel-Word mailmerge | Mailmerge |