FAX using T-SQL

Question

FAX using T-SQL

CHAN YAO KANG

SSC Journeyman

Points: 80
More actions
January 15, 2007 at 8:38 pm

#171904

Hi all
i have do some research on the TSQL Fax
below is the code but im not really understand about the sp_OAMethod @WordDocument, 'SendFax', NULL, '', 'Invio fax da SQL Server'
i want to check what is the requirement to using this store pro,do i need any additional software to allow the SQL SERVER directly interact with the fax machine.

Thanks
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.Applic
--     ation
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 " + vbN
--     ewLine + "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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

Basically you are instantiating a COM object in your PROC. You will also need Office (professional probably) installed on your server. I can't see anything else you would need.

Think about this though. Is this really something you want to be doing on your server??? It's certainly cool to create faxes from a stored proc, but I have known the word COM object to crash A LOT! Furthermore, this will be nightmare to debug, will consume a huge amount of resources (how many copies of word do you want running on your SERVER?) and will not scale at all.

This functionality really belongs in an application, not on the server. Just because you can doesn't mean you should.

SQL guy and Houston Magician

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 2

And if you INSIST on running this code on your server, add sp_OADestory @WordDocument in the end of your code to explicitly destory the COM object.

SQL guy and Houston Magician