export from sql to word - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

export from sql to word

Author

Topic

lilinikco
Starting Member

28 Posts

Posted - 2013-01-21 : 02:21:54

hello all.
I use this procedure for export from sql server to word that be found in
http://www.simple-talk.com/sql/t-sql-programming/writing-to-word-from-sql-server/
and changed connection string to :
select @ConnectionString='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Nik-Azizi;Data Source=tcp:192.168.1.9,1433;Database=MaliGilan'
procedure runs until line 340 fine but when save to doc,get this error:
Error whilst Saving the document as F:\foobar.doc

alter procedure [dbo].[spExportToWord] ( ----------------Exporting from SQL Server to Word
@SourceServer varchar(30)=null,
@SourceUID varchar(30)=null,
@SourcePWD varchar(30)=null,
@SourceDatabase varchar(100)=null,
@QueryText varchar(200),
@ConnectionString varchar(255) =null,
@DocumentFile varchar(100),
@TableFormat varchar(100)='professional',
@tableHeading varchar(7000)=null)
/*

create table sample(
[ ] varchar(80),
[Software Sales] varchar(80),
[Hardware Sales] varchar(80),
[Consultancy] varchar(80),
[Total] varchar(80))
insert into sample select 'First Quarter','£1940','£567','£765','£3272'
insert into sample select 'Second Quarter','£15960','£3685','£34000','£53645'
insert into sample select 'Third Quarter','£39480','£5000','£23000','£67480'
insert into sample select 'Fourth Quarter','£23960','£3549','£3470','£30979'
insert into sample select 'Total','£81340','£12801','£61235','£155376'
spExportToword @QueryText='Select * from sample',
@documentFile='C:\report5.doc',@Tableformat='Grid 6'
spExportToword @QueryText='Select * from sample',
@documentFile='C:\report6.doc',@Tableformat='Grid 6',@tableHeading='This is a pretty impressive table'

*/
AS

Declare @hr int,
@strErrorMessage varchar(1000),
@objDBC int,
@objRecordSet int,
@objErrorObject int,
@objWord int,
@objDocument int,
@ObjRange int,
@objTable int,
@Row int,
@Bucket int,
@ii int,
@Fieldname varchar(100),
@fields int,
@recordCount int,
@TableLength int,
@Command varchar(255),
@State int,
@EOF int,
@FieldValue varchar(8000),
@wdAlertsNone int,
@wdTableFormat int
Select @wdAlertsNone=0

Select @wdTableFormat= case replace(@TableFormat,' ','')
when '3DEffects1' then 32
when '3DEffects2' then 33
when '3DEffects3' then 34
when 'Classic1' then 4
when 'Classic2' then 5
when 'Classic3' then 6
when 'Classic4' then 7
when 'Colorful1' then 8
when 'Colorful2' then 9
when 'Colorful3' then 10
when 'Colourful1' then 8
when 'Colourful2' then 9
when 'Colourful3' then 10
when 'Columns1' then 11
when 'Columns2' then 12
when 'Columns3' then 13
when 'Columns4' then 14
when 'Columns5' then 15
when 'Contemporary' then 35
when 'Elegant' then 36
when 'Grid1' then 16
when 'Grid2' then 17
when 'Grid3' then 18
when 'Grid4' then 19
when 'Grid5' then 20
when 'Grid6' then 21
when 'Grid7' then 22
when 'Grid8' then 23
when 'List1' then 24
when 'List2' then 25
when 'List3' then 26
when 'List4' then 27
when 'List5' then 28
when 'List6' then 29
when 'List7' then 30
when 'List8' then 31
when 'None' then 0
when 'Professional' then 37
when 'Simple1' then 1
when 'Simple2' then 2
when 'Simple3' then 3
when 'Subtle1' then 38
when 'Subtle2' then 39
when 'Web1' then 40
when 'Web2' then 41
when 'Web3' then 42
else 0 end

set nocount on
IF @QueryText IS NULL
BEGIN
raiserror ('A query string is required for spExportToWord',16,1)
RETURN 1
END

-- Sets the server to the local server by default
IF @SourceServer IS NULL SELECT @SourceServer = @@servername
-- Sets the database to the local one by default
IF @SourceDatabase IS NULL SELECT @SourceDatabase = DB_name()
--if he hasn't specified a connection string...
if @connectionString is null
--if @SourcePWD is null or @SourceUID is null
-- begin
select @ConnectionString='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Nik-Azizi;Data Source=tcp:192.168.1.9,1433;Database=MaliGilan'
--'Driver={SQL Server};
----Server='+@SourceServer+';
----Database='+@SourceDatabase+';
----trusted_Connection=Yes'
-- end
-- else
-- Begin
-- select @ConnectionString='Driver={SQL Server};
--Server='+@SourceServer+';
--Database='+@SourceDatabase+';
--User ID='+@SourceUID+';
--Password='+@SourcePWD
-- end
--now we will create the connection string
Select @strErrorMessage='Making ADODB connection ',
@objErrorObject=null
EXEC @hr=sp_OACreate 'ADODB.Connection', @objDBC OUT
if @hr=0 Select @strErrorMessage='Assigning ConnectionString property "'
+ @ConnectionString + '"',
@objErrorObject=@objDBC
if @hr=0 EXEC @hr=sp_OASetProperty @objDBC,
'ConnectionString', @ConnectionString
if @hr=0 Select @strErrorMessage
='Opening the connection'
if @hr=0 EXEC @hr=sp_OAMethod @objDBC, 'Open'
if @hr=0 Select @strErrorMessage
='Executing the query'

if @hr=0 EXEC @hr=sp_OAMethod @objDBC, 'Execute',
@objRecordSet out , @QueryText

if @hr=0 Select @strErrorMessage='Getting the RS State ',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'State',
@State OUT
if @hr=0 Select @strErrorMessage='Getting whether the EOF was reached '
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF',
@eof OUT
if @hr=0 Select @strErrorMessage='Getting the field count '
EXEC @hr=sp_OAGetProperty @objRecordSet, 'fields.count',
@fields OUT
Select @RecordCount=0
while @hr=0 and @Eof=0 and @State=1 and @fields>0
begin
Select @RecordCount=@RecordCount+1
if @hr=0 Select @strErrorMessage='moving to the next record ',
@objErrorObject=@objRecordSet
if @hr=0 EXEC @hr=sp_OAMethod @objRecordSet, 'MoveNext'
if @hr=0 Select @strErrorMessage='Getting whether the EOF was reached ',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF', @eof OUT

select @ii=@ii+1
end
EXEC sp_OAMethod @objRecordSet, 'Close'

if @hr=0 Select @strErrorMessage
='Executing the query'

if @hr=0 EXEC @hr=sp_OAMethod @objDBC, 'Execute',
@objRecordSet out , @QueryText

if @hr=0 Select @strErrorMessage='Getting the RS State ',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'State',
@State OUT
if @hr=0 Select @strErrorMessage='Getting whether the EOF was reached '
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF',
@eof OUT
if @hr=0 Select @strErrorMessage='Getting the field count '
EXEC @hr=sp_OAGetProperty @objRecordSet, 'fields.count',
@fields OUT
--trap nonsense recordcount and add a header row
Select @TableLength = case when @recordCount>0 then @recordcount+1 else 20 end

if @hr=0 and @Eof=0 and @State=1
Begin
Select @strErrorMessage='instantiating Word application ',
@objErrorObject=null
EXEC @hr=sp_OACreate 'Word.Application', @objWord OUT
if @hr=0 Select @strErrorMessage='Ensuring no alerts',
@objErrorObject=@objWord
if @hr=0 EXEC @hr=sp_OASetProperty @objWord,
'DisplayAlerts', @wdAlertsNone
if @hr=0 Select @strErrorMessage='Ensuring Word invisible',
@objErrorObject=@objWord
if @hr=0 EXEC @hr=sp_OASetProperty @objWord,
'Visible', 0
if @hr=0 Select @strErrorMessage ='Creating a new file',
@objErrorObject=@objWord
if @hr=0 EXEC sp_OAMethod @objWord, 'Documents.Add',
@objDocument output
if @TableHeading is not null
begin
if @hr=0 Select @strErrorMessage ='Writing the heading',
@objErrorObject=@objWord,
@command='Selection.TypeText("'+replace(@tableHeading,'"','')+'")'
if @hr=0 EXEC sp_OAMethod @objWord, @command
if @hr=0 Select @strErrorMessage ='ending the paragraph'
if @hr=0 EXEC sp_OAMethod @objWord,'Selection.TypeParagraph()'
end
if @hr=0 Select @strErrorMessage ='Creating a range',
@objErrorObject=@objdocument
if @hr=0 EXEC @hr=sp_OAMethod @objWord, 'Selection.Range',
@objRange output

if @hr=0 Select @strErrorMessage ='Adding a table',
@objErrorObject=@objdocument
if @hr=0 EXEC @hr=sp_OAMethod @objdocument, 'Tables.Add',
@bucket output ,@objRange,@TableLength,@fields

if @hr=0 Select @strErrorMessage ='getting the table object',
@objErrorObject=@objdocument
if @hr=0 EXEC @hr=sp_OAGetProperty @objdocument, 'Tables(1)',
@objTable output
end
else
begin
EXEC sp_OAMethod @objRecordSet, 'Close'
EXEC sp_OAMethod @objDBC, 'Close'
EXEC sp_OADestroy @objDBC
EXEC sp_OADestroy @objRecordSet

Raiserror ('No result set from ''%s'', using ''%s''',16,1,@connectionString, @queryText)
return 1
end
Select @Row=1
if @hr=0 and @Eof=0 and @State=1
begin
Select @ii=0
while @ii<@fields and @hr=0
begin
if @hr=0 Select @strErrorMessage='Getting each field name ',
@Command='fields('+cast(@ii as varchar(3))+').name',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, @command,
@fieldName OUT
--select @FieldName,@ii,@fields
if @hr=0 Select @strErrorMessage='Setting the table heading font ',
@Command='Cell(1,'+cast(@ii+1 as varchar(3))+').range.font.bold',
@objErrorObject=@objTable
if @hr=0
EXEC @hr=sp_OASetProperty @objTable, @command, 1

if @hr=0 Select @strErrorMessage='Setting the table heading value ',
@Command='Cell(1,'+cast(@ii+1 as varchar(3))+').range.Text',
@objErrorObject=@objTable
if @hr=0
EXEC @hr=sp_OASetProperty @objTable, @command, @FieldName
select @strErrorMessage=@strErrorMessage+ ' with '+@Command
-- objTable.Cell(1, @ii).Range.Text = @FieldValue
Select @ii=@ii+1
end
end

while @hr=0 and @Eof=0 and @State=1 and @fields>0
begin
Select @Row=@Row+1
Select @ii=0
while @ii<@fields and @hr=0
begin
if @hr=0 Select @strErrorMessage='Getting each field value ',
@Command='fields('+cast(@ii as varchar(3))+').value',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, @command,
@fieldvalue OUT
--objTable.Cell(@row, @ii).Range.Text = objItem.Name
if @hr=0 Select @strErrorMessage='Setting the data table cell ',
@objErrorObject=@objTable,
@Command='Cell('+cast(@row as varchar(3))+','+cast(@ii+1 as varchar(3))+').range.Text'
if @hr=0
EXEC @hr=sp_OASetProperty @objTable, @command, @FieldValue
select @strErrorMessage=@strErrorMessage+ ' with '+@Command
Select @ii=@ii+1
end
if @hr=0 Select @strErrorMessage='moving to the next record ',
@objErrorObject=@objRecordSet
if @hr=0 EXEC @hr=sp_OAMethod @objRecordSet, 'MoveNext'
if @hr=0 Select @strErrorMessage='Getting whether the EOF was reached ',
@objErrorObject=@objRecordSet
if @hr=0
EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF', @eof OUT

select @ii=@ii+1
end
if @hr=0 Select @strErrorMessage='Autoformatting to style '+cast(@wdTableFormat as varchar(2)),
@objErrorObject=@objTable,
@command='AutoFormat('+cast(@wdTableFormat as varchar(2))+')'
if @hr=0
EXEC @hr=sp_OAMethod @objTable, @command

if @hr=0 Select @strErrorMessage ='Saving the document as '+@Documentfile,
@objErrorObject=@objWord
if @hr=0 EXEC @hr = sp_OAMethod @objWord,
'Activedocument.SaveAs' , NULL , @DocumentFile
EXEC sp_OAMethod @objRecordSet, 'Close'
EXEC sp_OAMethod @objDBC, 'Close'
EXEC sp_OAMethod @objWord, 'Quit'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
------------------------- ezafe ast khodam ezafe kardam
--declare @src varchar(255), @desc varchar(255)
-- if @hr<>0
-- EXEC sp_OAGetErrorInfo 16711422, @src OUT, @desc OUT
-- raiserror('Error 0x%x, %s, %s',16,1, @hr, @src, @desc)
--------------------------------
EXEC sp_OADestroy @objDBC
EXEC sp_OADestroy @objRecordSet
Exec sp_oaDestroy @objDocument
EXEC sp_OADestroy @objTable
EXEC sp_OADestroy @objRange
EXEC sp_OADestroy @objWord
return @hr
GO

--Execute spExportToword
--@QueryText='select * from QMS_QmsBase',
--@documentFile='C:\foobar.doc',
--@Tableformat='Colourful 1'

please help me to solve this problem.thanks

lili@@

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-21 : 03:26:00

is the path name correct? do you've F:\\ in server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-21 : 12:04:29

have you considered completing this task in Powershell . It's a lot less coding and much flexibility

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources