This article explains how you can create a Microsoft Word document from T-SQL and fax it through a method exposed by its Automation object model.

You can create and destroy OLE Automation object using the sp_OACreate and sp_OADestroy, whereas you can use the sp_OAMethod to invoke a method, and the sp_OAGetProperty and sp_OASetProperty to read and write a property.

The following example creates a Word document and retrieves its Application object, which it stores in an Integer variable which represents the COM pointer to the automation interface of the component. Next, it makes the Word application visible by setting the Application’s Visible property to True, then it inserts some text into the Word document and finally faxes the document using the SendFax method. Obviously you can use such stored procedures from triggers or other stored procedures, and invoke them from client applications or from the SQL Server Agent.

Sql Server OLE Automation example by Giuseppe Dimauro 04/2000DECLARE @WordDocument intDECLARE @WordApplication intDECLARE @Content intDECLARE @visible intDECLARE @hr intDECLARE @text varchar(4096)– Set WordDocument = CreateObject(“Word.Document”)EXEC @hr = sp_OACreate ‘word.Document’, @WordDocument OUT– Set Application = WordDocument.ApplicationIF @hr = 0EXEC @hr = sp_OAGetProperty @WordDocument, ‘Application’, @WordApplication OUT– Set Content = WordDocument.ContentIF @hr = 0EXEC @hr = sp_OAGetProperty @WordDocument, ‘Content’, @Content OUT– Content.Text = “Word Document ” + vbNewLine + “generated by SQL Server”IF @hr = 0BEGINset @text = ‘Word Document’ + char(10) + ‘generated by SQL Server’EXEC @hr = sp_OASetProperty @Content, ‘Text’, @textEND– WordApplication.Visible = TrueIF @hr = 0BEGINEXEC @hr = sp_OASetProperty @WordApplication, ‘Visible’, 1waitfor delay ’00:00:10’END– WordDocument.SendFax ““, “Send a fax from SQL Server”IF @hr = 0EXEC @hr = sp_OAMethod @WordDocument, ‘SendFax’, NULL, ‘‘, ‘Invio fax da SQL Server’IF @hr <> 0BEGINprint “ERROR OCCURRED: ” + cast(@hr as varchar(128))RETURNEND

########################################################

This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################