Binaryworld - Send fax from SQL Server using Microsoft Word ... [ SQL Server -> Stored Procedure ]

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/2000

Click here to copy the following block

DECLARE @WordDocument int
DECLARE @WordApplication int
DECLARE @Content int
DECLARE @visible int
DECLARE @hr int
DECLARE @text varchar(4096)

-- Set WordDocument = CreateObject("Word.Document")
EXEC @hr = sp_OACreate 'word.Document', @WordDocument OUT

-- Set Application = WordDocument.Application
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Application', @WordApplication OUT

-- Set Content = WordDocument.Content
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Content', @Content OUT

-- Content.Text = "Word Document " + vbNewLine + "generated by SQL Server"
IF @hr = 0
BEGIN
set @text = 'Word Document' + char(10) + 'generated by SQL Server'
EXEC @hr = sp_OASetProperty @Content, 'Text', @text
END

-- WordApplication.Visible = True
IF @hr = 0
BEGIN
EXEC @hr = sp_OASetProperty @WordApplication, 'Visible', 1
waitfor delay '00:00:10'
END

-- WordDocument.SendFax "", "Send a fax from SQL Server"
IF @hr = 0
EXEC @hr = sp_OAMethod @WordDocument, 'SendFax', NULL, '', 'Invio fax da SQL Server'

IF @hr <> 0
BEGIN
print "ERROR OCCURRED: " + cast(@hr as varchar(128))
RETURN
END