Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7,SS2K
Expertise: Intermediate
May 12, 2001

Send fax from SQL Server using Microsoft Word

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

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

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

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

Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date