InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 03:23:57 GMT20http://www.sqlservercentral.com/Forums/Topic1409367-391-1.aspxhello 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.docalter 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' */ASDeclare @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 intSelect @wdAlertsNone=0Select @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 endset nocount onIF @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 defaultIF @SourceServer IS NULL SELECT @SourceServer = @@servername-- Sets the database to the local one by defaultIF @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 stringSelect @strErrorMessage='Making ADODB connection ', @objErrorObject=nullEXEC @hr=sp_OACreate 'ADODB.Connection', @objDBC OUTif @hr=0 Select @strErrorMessage='Assigning ConnectionString property "' + @ConnectionString + '"', @objErrorObject=@objDBCif @hr=0 EXEC @hr=sp_OASetProperty @objDBC, 'ConnectionString', @ConnectionStringif @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 , @QueryTextif @hr=0 Select @strErrorMessage='Getting the RS State ', @objErrorObject=@objRecordSetif @hr=0 EXEC @hr=sp_OAGetProperty @objRecordSet, 'State', @State OUTif @hr=0 Select @strErrorMessage='Getting whether the EOF was reached 'if @hr=0 EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF', @eof OUTif @hr=0 Select @strErrorMessage='Getting the field count ' EXEC @hr=sp_OAGetProperty @objRecordSet, 'fields.count', @fields OUTSelect @RecordCount=0while @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 , @QueryTextif @hr=0 Select @strErrorMessage='Getting the RS State ', @objErrorObject=@objRecordSetif @hr=0 EXEC @hr=sp_OAGetProperty @objRecordSet, 'State', @State OUTif @hr=0 Select @strErrorMessage='Getting whether the EOF was reached 'if @hr=0 EXEC @hr=sp_OAGetProperty @objRecordSet, 'EOF', @eof OUTif @hr=0 Select @strErrorMessage='Getting the field count ' EXEC @hr=sp_OAGetProperty @objRecordSet, 'fields.count', @fields OUT--trap nonsense recordcount and add a header rowSelect @TableLength = case when @recordCount>0 then @recordcount+1 else 20 endif @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 endelse 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 endSelect @Row=1if @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 endwhile @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, @commandif @hr=0 Select @strErrorMessage ='Saving the document as '+@Documentfile, @objErrorObject=@objWordif @hr=0 EXEC @hr = sp_OAMethod @objWord, 'Activedocument.SaveAs' , NULL , @DocumentFileEXEC 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 @objDBCEXEC sp_OADestroy @objRecordSetExec sp_oaDestroy @objDocumentEXEC sp_OADestroy @objTableEXEC sp_OADestroy @objRangeEXEC sp_OADestroy @objWordreturn @hrGO--Execute spExportToword --@QueryText='select * from QMS_QmsBase',--@documentFile='C:\foobar.doc',--@Tableformat='Colourful 1'please help me to solve this problem.thanksMon, 21 Jan 2013 00:11:25 GMTelham_azizi_62