InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:34:43 GMT20http://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxthe example you found from simpletalk [b][url]http://www.simple-talk.com/code/WinWord/spExportToWord.txt[/url][/b]has [b]nothing [/b]to do with mailmerge; it's just an example automating the population of data into a word document; worse, you didn't modify it at all with anything related to your connection/database/specific data related to your issue; it's a verbatum copy paste, and then you asked us to figure out the rest.everyone here are volunteers; I don't think anyone would be willing to hazard much more than friendly guesses unless you, yourself, put some effort into the problem.I think you have to step back and re-evaluate what you are trying accomplish:You asked specifically about a mailmerge.Usually , when you do a mail merge, you send the results to a printer. Is that what you are trying to do here as well?Mon, 21 Jan 2013 08:13:58 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxI fine this procedure for export from sql to word:create 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='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) endEXEC sp_OADestroy @objDBCEXEC sp_OADestroy @objRecordSetExec sp_oaDestroy @objDocumentEXEC sp_OADestroy @objTableEXEC sp_OADestroy @objRangeEXEC sp_OADestroy @objWordreturn @hrGOwhen execute sp:--Execute spExportToword --@QueryText='select * from QMS_QmsBase',--@documentFile='C:\mailmerge.doc',--@Tableformat='Colourful 1'get error message :Msg 50000, Level 16, State 1, Procedure spExportToWord, Line 248No result set from 'Driver={SQL Server}; Server=WIN-00UO3RQ0BM3;Database=MaliGilan; trusted_Connection=Yes', using 'select * from QMS_QmsBase'please help me until solve this error.thanksSat, 19 Jan 2013 05:28:22 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxI dont believe you can.You cwn open word and use word to select a datasouece which is on a sql server.But sql has no way to call word and perform a mail merge so you cannot do it via tsql.May be with a clr you write in. Net, but even that would be the wrong tool.After you get a bunch of mailmerged doc files, whats next? Email as an attachment? Id rather send html email and no attachments at all.Sat, 19 Jan 2013 03:57:35 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxI want to create mail merge document direvtly from sql server with stor procedure.Is it possible?Sat, 19 Jan 2013 00:14:03 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxI mean that how to create Ms word mail merge with store peocedure in sql?not send email from sql server!!!!!!!!Fri, 18 Jan 2013 22:53:04 GMTelham_azizi_62http://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxsimilar to A word document, you can set up a template with something that represents the items that would be substituted.then you need a cursor/loop for each email you plan on sending so you can feed it to msdb.dbo.sp_send_dbmail.for each iteration in the loop, you do a TSQL REPLACE(@Template,'{FirstName}',ISNULL(FirstNameFromDatabase,'') for example for every field you put in your template[code="plain"]DECLARE @Body varchar(max)SET @Body ='<TABLE BORDER="1"><TR><TD NOWRAP VALIGN="top">CONGRAT {FirstName}</TD></TR><TR><TD NOWRAP VALIGN="top">A cash grant award of $5.2M from our Atm Card Award Promo has been awarded to you because your Email of {Email} is in our Scammers list.</TD></TR><TR><TD NOWRAP VALIGN="top"> Re-confirm your Full Names, Address ANY OF YOUR ID CARD, TELEPHONE NUMBERS. for the issuance and delivery of your Card. </TD></TR><TR><TD NOWRAP VALIGN="top"> You can call me on my number +234-806-947-4416</TD></TR><TR><TD NOWRAP VALIGN="top">MR.BEN MARTINS</TD></TR></TABLE>'[/code]that kind of stuff. Do you need more than that?Wed, 16 Jan 2013 06:30:29 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxIf you're thinking of a Word Mail Merge, I don't know of a way you can do that exactly. SQL does have outgoing mail functionality by way if the sp_send_dbmail procedure.First set up database mail and create a profile with an account. Here's the basic syntax of the command:[code="sql"]EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'Your Profile Name', @recipients = @strTo, @copy_recipients = @strCC, @subject = @strSubject, @body = @strBody, @body_format = 'HTML';[/code]For the full syntax of the command, see [url]http://msdn.microsoft.com/en-us/library/ms190307%28v=SQL.90%29.aspx[/url]. It contains a lot of options for including queries that you probably don't need in this specific situation, but they come in handy sometimes.I've set this up to periodically process outbound mail via a database job, but there's no reason you can't just run it anytime.Wed, 16 Jan 2013 06:16:17 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1407786-391-1.aspxhello all.I want to have a mail merge in sql server without c# code or vb code.for example in storeprocedure with open xml sdk.how do I do this?please guide me.thanks.Wed, 16 Jan 2013 05:47:49 GMTelham_azizi_62