mail merge in sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » mail merge in sp

mail merge in sp

Rate Topic

Display Mode

Topic Options

Author

Message

elham_azizi_62

Posted Wednesday, January 16, 2013 5:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:33 AM
Points: 109, Visits: 390

hello 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.

Post #1407786

Ed Wagner

Posted Wednesday, January 16, 2013 6:16 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 1,871, Visits: 541

If 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:

EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'Your Profile Name',
        @recipients = @strTo,
        @copy_recipients = @strCC,
        @subject = @strSubject,
        @body = @strBody,
        @body_format = 'HTML';

For the full syntax of the command, see http://msdn.microsoft.com/en-us/library/ms190307%28v=SQL.90%29.aspx. 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.

Post #1407800

Lowell

Posted Wednesday, January 16, 2013 6:30 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

similar 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

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>'

that kind of stuff. Do you need more than that?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1407805

elham_azizi_62

Posted Friday, January 18, 2013 10:53 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:33 AM
Points: 109, Visits: 390

I mean that how to create Ms word mail merge with store peocedure in sql?not send email from sql server!!!!!!!!

Post #1409163

elham_azizi_62

Posted Saturday, January 19, 2013 12:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:33 AM
Points: 109, Visits: 390

I want to create mail merge document direvtly from sql server with stor procedure.Is it possible?

Post #1409166

Lowell

Posted Saturday, January 19, 2013 3:57 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

I 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.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1409173

elham_azizi_62

Posted Saturday, January 19, 2013 5:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:33 AM
Points: 109, Visits: 390

I 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'

*/
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='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
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

when 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 248
No 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.thanks

Post #1409190

Lowell

Posted Monday, January 21, 2013 8:13 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

the example you found from simpletalk
http://www.simple-talk.com/code/WinWord/spExportToWord.txt

has nothing 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?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1409575

« Prev Topic | Next Topic »

Permissions