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

  • Question

  • Hello There,

    We are trying to use word mail merge functionality  in vb.net and getting an issue with the API OpenDataSource. How can we call the stored procedure using this API.

    We are using Sql Server 2008 and VS 2005.

    Monday, May 6, 2013 3:52 AM

Answers

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

    Tuesday, May 7, 2013 8:41 AM

All replies

  • Version of word?

    Details of the "issue", please.

    Also, please provide the OpenDataSource code.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, May 6, 2013 5:22 AM
    Moderator
  • 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 6, 2013 8:57 AM
  • 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

    Monday, May 6, 2013 9:24 AM
  • 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 7, 2013 2:47 AM
  • <<
    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

    Tuesday, May 7, 2013 8:41 AM
  • 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 8, 2013 4:56 AM
  • 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

    Wednesday, May 8, 2013 9:38 AM
  • 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 4:19 AM
  • 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

    Friday, May 10, 2013 8:40 AM