Error whilst Saving the document as

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 21, 2013 at 12:11 am

  #269557

  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