We help IT Professionals succeed at work.

SQL Server MSWord document

srobia
srobia asked
on
Is it possible to create a formatted Word document with SQL Server from a table?  I have tried to use bcp to copy the table out to a .doc file but it lacks the format.  I have tried to create an MSWord object and found out that Word is not installed on the Server.  So, is it possible to do that?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
if Word is not installed on the server, and you don't probably the internal format of Word documents, you will run into trouble to get this working.
You might try to write out .rtf formatted documents, and save them as .doc anyway, rtf is somewhat easier and doesn't have the complex structure in it's file as .doc has...

CHeers

Commented:
srobia,

Interesting enough.. You can actually create a word document from SQL Server.  There are a couple of methods.. Here's a simple, I like to use.. it requires you have MS-Word installed on the SQL Server..  This can run within a Stored Procedure..

--Code Begins Here
DECLARE @Object int
DECLARE @aWorking int
DECLARE @aRange int
DECLARE @aRetCode int
DECLARE @Hresult int
DECLARE @aThisRange varchar(255)
DECLARE @Username varchar(255)
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)

--Create the object
EXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT
--Create the active document
EXEC @Hresult = sp_OACreate 'Document', @aWorking OUT
--Create the Range
EXEC @Hresult = sp_OACreate 'Range', @aRange OUT
--Call the object's property and return the value
EXEC @Hresult = sp_OASetProperty @Object,'Visible',1
EXEC @Hresult = sp_OAMethod @Object,'Documents.Add',NULL
--EXEC @Hresult = sp_OAGetProperty @Object, 'UserName', @Username OUT
EXEC @Hresult = sp_OAMethod @Object,'Quit',NULL
--Destroy the object
EXEC @Hresult = sp_OADestroy @aWorking    
EXEC @Hresult = sp_OADestroy @Object

IF @Hresult <> 0
BEGIN
  EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT
  PRINT "Error Occurred Calling Object:  " + @ErrorSource + " " + @ErrorDesc
  RETURN
END

--Code Ends Here

Good Luck,

DrMaltz

Author

Commented:
angellll -- I was afraid of that.  I might be able to do it in rtf format but . . .  I may just tell the users that there can be no formating.

DrMaltz -- I tried that and that is how I found out that Word is not installed on the server.  I kept getting errors when I tried to destroy the object.
Commented:
--Here's some sample code..

-- Check out the following link:  http://msdn.microsoft.com/library/?url=/library/en-us/dnrtfspec/html/rtfspec.asp?frame=true?frame=true

CREATE PROCEDURE ww_GenRTF  AS

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fsObject int
DECLARE @tfObject int
DECLARE @wl int
DECLARE @tf int
DECLARE @sRTF varchar(1000)

EXEC @hr = sp_OACreate "Scripting.FileSystemObject", @fsObject OUTPUT
if @hr <> 0 EXEC sp_displayoaerrorinfo @fsObject, @hr

EXEC @hr = sp_OAMethod @fsObject, 'CreateTextFile' , @tfObject OUTPUT ,
'c:\sample1.doc'
if @hr <> 0 EXEC sp_displayoaerrorinfo @fsObject, @hr


EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '{\rtf1'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

Select @sRTF = '{\colortbl;\red0\green0\blue0;\red0\green0\blue255;'
Select @sRTF = @sRTF +  '\red0\green255\blue255;\red0\green255\blue0;'
Select @sRTF = @sRTF +  '\red255\green0\blue255;\red255\green0\blue0;'
Select @sRTF = @sRTF +  '\red255\green255\blue0;\red255\green255\blue255;}'

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, @sRTF
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '{\info{\title Sample RTF Document}{\author Microsoft Developer Support}}'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '{\header\pard\qc{\fs50 ASP-Generated RTF\par}{\fs18\chdate\par}\par\par}'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '{\footer\pard\qc\brdrt\brdrs\brdrw10\brsp100\fs18 Page {\field{\*\fldinst PAGE}{\fldrslt 1}} of {\field{\*\fldinst NUMPAGES}{\fldrslt 1}} \par}'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '\par\fs24\cf2 This is a sample \b RTF \b0 document created with SQL Stored Procedure.\cf0'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '\par \page'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '\pard\fs18\cf2\qc This sample provided by Yours Truly.'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, '}'
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OAMethod @tfObject , 'Close' , NULL
if @hr <> 0 EXEC sp_displayoaerrorinfo @tfObject, @hr

EXEC @hr = sp_OADestroy @tfObject
IF @hr <> 0

BEGIN

    EXEC sp_displayoaerrorinfo @tfObject, @hr

    RETURN

END


EXEC @hr = sp_OADestroy @fsObject
IF @hr <> 0

BEGIN

    EXEC sp_displayoaerrorinfo @fsObject, @hr

    RETURN

END

--Good Luck,

--DrMaltz