execute stored procedure using Microsoft.Office.Interop.Word.MailMerge.OpenDataSource API

Monday, May 06, 2013 5:22 AM

Moderator

0

Version of word?

Details of the "issue", please.

Also, please provide the OpenDataSource code.

Cindy Meister, VSTO/Word MVP, my blog

Monday, May 06, 2013 8:57 AM

0

we are doing word automation using vb.net. There is a API OpenDataSource of Microsoft.Office.Interop.Word.MailMerge which has many parameters and also there is way of sql statement.

If we pass sql statement (like select .....) then it work fine. But issue with this is that we cannot pass more that 510 characters. That's why we want to use Stored procedure to run big sql.

But i am not sure that how to run store procedure using this api. What value pass to the parameter to run the procedure.?

Monday, May 06, 2013 9:24 AM

0

Assuming your database is SQL Server, if you do not need to pass any parameters to the query, it will be simpler if you can use a View rather than a procedure.

Unless it has been fixed recently, OpenDataSource does not work correctly with SQL Server Stored Procedures, unless they only contain a single SELECT statement. This was discussed, but never resolved, here:

http://social.msdn.microsoft.com/Forums/en-US/worddev/thread/272cf1a8-ea98-49a9-b05f-82cfec497a6a

If you have to use a procedure (e.g. because you have to have multiple steps or you need to pass parameters to it) you are more likely to succeed if you use a table-valued stored function instead. In that case your SQL statement needs to look like this:

SELECT m.* FROM myfunction(myparameter1,myparameter2,etc.) m

Peter Jamieson

Tuesday, May 07, 2013 2:47 AM

0

Thanks Peter for reply,

As from thread, word not supported multiple statement in SP.

,

UDF not supported executing dynamic sql & openDataSource API not supported string more than 510 length.

Then how can we run big sql using this API. Is there any other way of passing datasource to mail merge functionality. Following is the some code that am trying for mail merge functionality. I m not good Word Developer. Also if you have any info, can you let me know some site which are explaining these APIs in detail? In this code, i was trying to run stored procedure but not succeed in this.

Please let me know if we can use another approach for mail merge functionality to pass\bind datasource.

  Dim docPath As Object = mailMerge.MMTemplate.MMTPath
                                Dim wrdApp As Word.Application = Nothing
                                Dim wrdDoc As Word._Document = Nothing
                                Try
                                    wrdApp = New Word.Application
                                    wrdDoc = wrdApp.Documents.Open(docPath, ReadOnly:=True)
                                    Dim sqlHT As Hashtable = QueryExecuter.GenerateSQL(MMRep.QueryCriteria, MMRep.OutFieldSet)
                                    Dim merge As Microsoft.Office.Interop.Word.MailMerge = CType(wrdDoc.MailMerge, Microsoft.Office.Interop.Word.MailMerge)

                                    Dim odcfilename As String = System.AppDomain.CurrentDomain.BaseDirectory & "empty.odc"
                                    If Not My.Computer.FileSystem.FileExists(odcfilename) Then
                                        System.IO.File.Create(odcfilename)
                                    End If
                                    Dim conn As Object = SDP.Base.QueryBuilder.Util.ConfigUtil.GetDDCDataWordAddinConnectionString() 
                                    Dim enm As IDictionaryEnumerator = sqlHT.GetEnumerator
                                    Dim sql As String = ""
                                    While enm.MoveNext
                                        sql = CStr(enm.Key)
                                    End While
                                    Dim QBTSID As Integer = QBTempSqlDataStore.GetInstance.SaveMailMergeSql(sql)
                                    
                                    Dim mmsql As Object = "{ exec QBTempSqlResult(" & QBTSID & ")}"
                                    merge.OpenDataSource(Name:=odcfilename, Connection:=conn, SQLStatement:=mmsql, SubType:=Microsoft.Office.Interop.Word.WdMergeSubType.wdMergeSubTypeOther)
                                    merge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
                                    wrdApp.Visible = True
                                    merge.Execute()
                                Finally
                                    If wrdDoc IsNot Nothing Then
                                        wrdDoc.Close(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges)
                                    End If
                                    If wrdApp IsNot Nothing Then
                                        wrdApp.Quit(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges)
                                    End If
                                    wrdDoc = Nothing
                                    wrdApp = Nothing
                                End Try

Tuesday, May 07, 2013 8:41 AM

0

<<
Then how can we run big sql using this API.
>>

The only ways I know are:
a. the two I already mentioned (use a View or a table-valued stored function)
b. use ODBC and EXECUTE. Unfortunately, using ODBC from Word has a problem: values in Unicode type fields (NVARCHAR etc.) are returned as blanks. Unless that has been fixed.
c. try to use an intermediary. For example, in Excel I can use a Transact-SQL EXECUTE with an SP with parameters easily (it's the way I verify that the SQL I am trying in Word should work). So if you can rely on Excel being present, you could consider automating a workbook to refresh a data table, then use that as your datasource. The main problem with this (other than the fact that you should not have to do it) is that Word MailMerge can only access a maximum of 255 columns from an Excel source. Alternatively, you could consider creating a pass-through query in a .mdb or .accdb and connecting to that. I think most recent systems with Word/Office will have the necessary ADO and/or ADOX components to create a database and passthrough query dynamically, except maybe 64-bit versions of Windows and/or Office. In that case, you will need to connect to the query via OpenDataSource because Word does not "see" passthrough queries otherwise. Passthrough queries from Jet use ODBC, but in this case Unicode columns return something rather than nothing (whether it is the correct Unicode I do not know). Again, a 255-column limit I think.
d. Get the data another way, e.g via ADO, and "roll your own merge". If your mailmerge documents are being created by developers rather than end users, you could look at the material by Eric White, e.g. here: http://ericwhite.com/blog/category/open-xml/document-generation-series/

Ideally, you would report this problem to Microsoft through the appropriate channels and ask them to fix it. I find it difficult to believe that they do not know about it after all these years, but I suppose anything is possible.

<<
Also if you have any info, can you let me know some site which are explaining these APIs in detail?
>>

The official documentation for OpenDataSource is here (the Word 2013 version):
http://msdn.microsoft.com/en-us/library/office/ff841005.aspx

But it's mostly misleading or wrong. Mostly, you only ever need the following parameters (and most of the others never have any effect in any circumstances - the documentation seems to have been copied from other APIs years ago and never fixed):
Name
Connection SQLStatement (and SQLStatement1)
Subtype

For OLE DB connections, I cannot currently get an EXECUTE to work in the SQLStatement, whatever syntax I use. For ODBC connections in Word 2010, I needed this
Name:=""
Connection:="DSN=the name of a user or system ODBC DSN that specifies the server and database;Trusted_Connection=Yes;"
SQLStatement:="EXECUTE theprocname parameter1, parameter2, etc."

In older versions of Word, for ODBC connections you also typically had to use
Subtype:=wdMergeSubtypeWord2000

String parameters need to be quoted in ' ' or " ", and so on.

If you use a file DSN, you probably need

Name:="path/file name of the .dsn"
Connection:="FILEDSN=path/file name of the .dsn;Trusted_Connection=Yes;"
SQLStatement:="EXECUTE theprocname parameter1, parameter2, etc."

Not sure you will get this stuff to work without a trusted connection, but you will almost certainly have to include username and password in the Connection parameter to achieve that.

Peter Jamieson

Marked As Answer by Damon ZhengMicrosoft Contingent Staff, Moderator Tuesday, May 14, 2013 3:22 AM

Wednesday, May 08, 2013 4:56 AM

0

Thanks Peter,

Using ODBC connection , we are able to run the SP. But now we are seeing another issue.

we try this approach with simple and short sql. When we pass sql (SELECT statment) to OpenDataSource API. Then all the fields of mail merge document replaced with data.

But when we execute same sql from Stored Procedure, then only first column\field in doc replaced with data. Other mail merge fields show as blank. We run same stored procedure in SQL Server , it shows all the result there.

Wednesday, May 08, 2013 9:38 AM

0

This is almost certainly the problem I mentioned in my previous post - Unicode column types (the ones beginning with N, NVARCHAR etc.) do not get through to Word. This seems to be an error in Word rather than anything to do with ODBC, because Excel and Access can retrieve these values.

If you have those column types, you could try CASTing them to the equivalent non-Unicode types (VARCHAR). That may be OK as long as you do not have non-ANSI/OEM Unicode characters - at best, they will come through as "?"

I have tried a number of different things to try to work around this. Nothing I have tried works. You can for example try to connect via the OLEDB provider for ODBC data sources to try to get the "best of both worlds". But in that case, the problem is still that you cannot issue an EXECUTE in the SQL from Word. It is always possible that someone else can come up with something that does work, but I have never seen such a thing.

Possibly the best alternative is to create passthrough queries in a .mdb (or .accdb) and connect to them. In that case, Unicode data does get through, and the Word-Jet connection can be the default OLEDB connection type, which makes the connection code quite simple in recent versions of Word. However, I haven't used it for real. One problem is that there will almost certainly be a 255 column limit. But example, here's some crude VBA code that creates a .mdb and passthrough query "on the fly" then connects to it. Not sure how it would translate into VB.Net - in VBA, you need to make references to the relevant ADO library (here, I used Microsoft ActiveX DataObjects 6.1 Library and Microsoft ADO Ext. 2.8 for DDL and Security), but I assume that in VB.NET you can do it all via ADO.NET.

Sub testMakeMdbPassthrough()

Call makeMdbPassthrough("c:\a\tempmdb.mdb", _
  "tempq1", _
  "DSN=mydsn;Description=Northwind;Trusted_Connection=Yes;", _
  "myproc 10, ""abc""")
  ' or "EXECUTE myproc 10, ""abc"""
End Sub


Sub makeMdbPassthrough(MDBFullName As String, _
  MDBQueryName As String, _
  DBConnectString As String, _
  SQL As String)

' MDBFullname should be the path+file name of
' a .mdb. The path must exist but this Sub will
' create the .mdb

' MDBQueryName is the name of our passthrough query in the .mdb

' DBConnectString is an ODBC connection string
' for the SQL Server database. Only tested with "machine"
' connection strings at this point

' SQL is the passthrough Transact-SQL that you want to run

Const strJetODBCConnectionPrefix As String = "ODBC;"

' Specify the OLE DB Provider(s).
Const strProviderJet = "Microsoft.Jet.OLEDB.4.0"
Const strProviderACE = "Microsoft.ACE.OLEDB.12.0"

Dim objCatalog As ADOX.Catalog
Dim objCommand As ADODB.Command

Set objCatalog = New ADOX.Catalog
On Error Resume Next
Set objCatalog.ActiveConnection = objCatalog.Create( _
  "Provider='" & strProviderJet & "';" & _
  "Data Source= '" & MDBFullName & "';" & _
  "Jet OLEDB:Engine Type=5;")
' should improve the error check
If Err.Number <> 0 Then
  ' try the ACE provider
  Err.Clear
  On Error GoTo 0
  Set objCatalog.ActiveConnection = objCatalog.Create( _
  "Provider='" & strProviderACE & "';" & _
  "Data Source= '" & MDBFullName & "';" & _
  "Jet OLEDB:Engine Type=5;")
End If

Debug.Print objCatalog.ActiveConnection

Set objCommand = New ADODB.Command

With objCommand
  Set .ActiveConnection = objCatalog.ActiveConnection
  .CommandText = SQL
  .Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
  .Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = _
       strJetODBCConnectionPrefix & DBConnectString
End With

objCatalog.Procedures.Append MDBQueryName, objCommand

Set objCommand = Nothing

Set objCatalog.ActiveConnection = Nothing
Set objCatalog = Nothing
  
With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .MainDocumentType = wdDirectory
  .OpenDataSource Name:=MDBFullName, _
  sqlstatement:="SELECT * FROM [" & MDBQueryName & "]"
End With
End Sub

Peter Jamieson

Friday, May 10, 2013 4:19 AM

0

Hello Peter,

Can we use temptable ( global temptable) in OpenDataSource API? I was trying but API is not responding. I am getting msg "Invalid object Name '##GlobalTempTableName'." But if i query sql server , table exist and returning result.

Edited by Dssohal1 Friday, May 10, 2013 5:41 AM

Friday, May 10, 2013 8:40 AM

0

Good idea.

I tried it here using the following VBA, and it worked OK. So I am guessing as to why it does not work for you. Could be...
a. Your code isn't keeping the connection/session alive (as you can see, I do not actually think mine does either, although I would think that closing the connection after the Merge is performed should be enough.

b. the usual, i.e. you have the necessary privileges to create the table (e.g. in ADO.NET) but not to retrieve records from it. I can't easily test that here.

Sub useSQLServerTempTable()

' Specify the Connection string
' (you may be using SQLNCLI.1, SQLNCLI10.1 or whatever)
' use your server name instead of "myserver"
' and your database name instead of "mydb"
' NB, this connection should work even when we are getting
' the data from the temp table, which is in the System database "tempdb"

Const strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=myserver;Initial Catalog=mydb"

' Use the SQL you need
' # means "local"
' ## means "global"

Const strCreateTempTableSQL As String = "SELECT * INTO [##mytemptable] FROM [Customers]"
Const strMailMergeSQL As String = "SELECT * FROM [##mytemptable]"

Dim objConnection As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRecordset As ADODB.Recordset

Set objConnection = New ADODB.Connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
With objCommand
  Set .ActiveConnection = objConnection
  .CommandType = adCmdText
  .CommandText = strCreateTempTableSQL
  .Execute
  Set .ActiveConnection = Nothing
End With
Set objCommand = Nothing

' we can check the data is there
' If we close and re-open the connection here,
' we get an "invalid object name"...
' which is what I would expect

GoTo skip1
Set objRecordset = New ADODB.Recordset
objRecordset.Open Source:=strMailMergeSQL, ActiveConnection:=objConnection
Debug.Print objRecordset.Fields(1).Name, objRecordset.Fields(1).Value
objRecordset.Close
Set objRecordset = Nothing

skip1:

' I thought that closing the connection here would
' result in OpenDataSource not finding the temp table
' because it should have gone, unless SQL Server
' sees us as a single Session.

' But probably better to keep our connection open until
' after the merge, because the table should not
' be destroyed until we disconnect.

'objConnection.Close
'Set objConnection = Nothing

With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .MainDocumentType = wdDirectory
  .OpenDataSource Name:="c:\a\empty.odc", _
    Connection:=strConnection, _
    sqlstatement:=strMailMergeSQL
End With

'objConnection.Close
'Set objConnection = Nothing

End Sub

Peter Jamieson

execute stored procedure using Microsoft.Office.Interop.Word.MailMerge.OpenDataSource API

All Replies

Office 2013

Office Extensibility

Resources

Connect

Developer Centers

Information for