Loop within a loop... BUT...

Question

Loop within a loop... BUT...

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

donato1026 SSCrazy Points: 2765 More actions · Answer 1

Oh! That's a procedure from something different... I want to do the same thing, using some of that code, but I do not know how to handle the loop inside of the loop... if that makes sense.

Thank you!

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

I understand what you're trying to do, but I still don't know how you want it done.

You posted some sample data, now you could post the expected output based on your sample data.

-- Gianluca Sartori

donato1026 SSCrazy Points: 2765 More actions · Answer 3

Not to be a pain, but I did post a sample of what I am looking for... it isis above.

And thank you SO much for your time!

Lowell SSC Guru Points: 323518 More actions · Answer 4

donato1026 (10/5/2011)
Not to be a pain, but I did post a sample of what I am looking for... it isis above.
And thank you SO much for your time!

i could help on this one, but i still didn't see the desired format.

in one post, you said it's gotta look like this:

Dear John Smith,

Thank you for your recent purchase with us. We are sending this email to inform you that the following items will be arriving LATE NOVEMBER:

SKU VSN DES

but the example proc you posted is select 10 columns, and none of those columns are SKU/VSN/DES, bu tthere does seem to be a customer code.

I don't think i saw the query that would returnr the (x) rows per customer in the post so far....

so, what is the desired output then?

i'll hunt for the original source, but i've modled dozens of html emails based on this snippet; i have to manually define the column headers, but then the XML does the bulk of the work for me.

Declare @HTMLBody varchar(max),

@TableHead varchar(max),

@TableTail varchar(max)

Set NoCount On;

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=0 cellspacing=0 border=0>' +

'<tr bgcolor=#FFEFD8><td align=center>Server Name</td>' +

'<td align=center>Product</td>' +

'<td align=center>Provider</td>' +

'<td align=center>Data Source</td>' +

'<td align=center>Is Linked?</td></tr>';

Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

name As

,

product As

,

provider As

,

data_source As

,

is_linked As

From sys.servers

Order By is_linked, name

For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers

Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

-- return output

Select @HTMLBody

--this is the html body i would send via sp_send_dbmail

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026 SSCrazy Points: 2765 More actions · Answer 5

Yes, Lowell. Thank you... I will get a sample up ASAP.

donato1026 SSCrazy Points: 2765 More actions · Answer 6

OK. Here is a sample of what I want. I made a "comment" in there between [[[ ]]]. I would like to list the items in a table format using the following fields:

item_cd

vsn

des

qty

ordered by del_doc_ln#

Here is the code sample:

USE [DatabaseName]

GO

/****** Object: StoredProcedure [dbo].[usp_send_prodavail_email_ON-TIME] Script Date: 10/05/2011 13:36:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================================

--

-- Author:Dan

-- Created: 10/05/2011

-- Description:This procedure will loop through the EMAIL_CBO_LIST_DAN table and retrieve a list of customers.

-- From there, it will send an email to each customer letting them know the status of their order.

--

-- ===========================================================================================================

ALTER PROC [dbo].[usp_send_prodavail_email_ON-TIME]

AS

DECLARE

@del_doc_num nvarchar(30)

,@max_due_date datetime

,@insert_date datetime

,@bo_sku_count int

,@email varchar(60)

,@cust_cd varchar(10)

,@ship_to_f_name varchar(15)

,@ship_to_l_name varchar(20)

,@ship_to_addr1 varchar(30)

,@ship_to_addr2 varchar(30)

,@ship_to_city varchar(20)

,@ship_to_st_cd varchar(2)

,@ship_to_zip_cd varchar(10)

,@ship_to_h_phone varchar(12)

,@ShipToAddress varchar(MAX)

,@fname varchar(15)

,@lname varchar(20)

,@addr1 varchar(30)

,@addr2 varchar(30)

,@city varchar(20)

,@st_cd varchar(2)

,@zip_cd varchar(10)

,@home_phone varchar(12)

,@Address varchar(MAX)

,@avail_text varchar(36)

,@itm_cd varchar(9)

,@vsn varchar(30)

,@des varchar(30)

,@del_doc_ln# numeric(4,0)

,@qty int

,@body varchar(MAX);

DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT

distinct(cust_cd),

fname,

lname,

coalesce(

addr1 + char(13) + char(10) + addr2,

addr1,

addr2,

'') as Address,

--addr1,

--addr2,

--isnull(addr2, ''),

city,

st_cd,

zip_cd,

home_phone,

ship_to_f_name,

ship_to_l_name,

coalesce(

ship_to_addr1 + char(13) + char(10) + ship_to_addr2,

ship_to_addr1,

ship_to_addr2,

'') as ShipToAddress,

--ship_to_addr1,

--ship_to_addr2,

--isnull(ship_to_addr2, ''),

ship_to_city,

ship_to_st_cd,

ship_to_zip_cd,

ship_to_h_phone,

avail_text,

itm_cd,

vsn,

des,

del_doc_ln#,

qty,

email

FROM dbo.EMAIL_CBO_LIST_DAN

--where

--convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101)

--AND pu_del = 'D' and open_del_date is not null

--AND EmailSent is null

--AND dbo.emailFilter(email) = 0

--AND (financed = 'Y' or balance = 0);

OPEN Contacts;

WHILE 1 = 1

BEGIN

FETCH NEXT FROM Contacts INTO

@cust_cd,

@fname,

@lname,

@Address,

--@addr1,

--@addr2,

@city,

@st_cd,

@zip_cd,

@home_phone,

@ship_to_f_name,

@ship_to_l_name,

@ShipToAddress,

--@ship_to_addr1,

--@ship_to_addr2,

@ship_to_city,

@ship_to_st_cd,

@ship_to_zip_cd,

@ship_to_h_phone,

@avail_text,

@itm_cd,

@vsn,

@des,

@del_doc_ln#,

@qty,

@email;

IF @@FETCH_STATUS = -1 BREAK;

SET @body = '<style type="text/css">

.style1

{

color: #FF0000;

}

.body

{

font-family: Arial, Helvetica, sans-serif;

font-size: small;

}

.style3

{

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

}

.style4

{

font-size: xx-small;

}

.style6

{

color: #FF0000;

font-family: Arial, Helvetica, sans-serif;

}

.style7

{

font-family: Arial, Helvetica, sans-serif;

font-size: xx-small;

}

.style8

{

font-family: Arial, Helvetica, sans-serif;

font-weight: bold;

font-size: x-small;

}

.style10

{

font-size: x-small;

}

</style><body>

<div>

<table style="width: 100%;">

<tr>

<td width="50%">

<a href="http://www.website.com">

<img src="http://www.website.com/skins/Skin_1/images/logo.jpg"

alt="Company Name" style="border-width: 0px" /></a>

</td>

<td width="50%">

Do NOT reply to this email. This is a system generated email from

an unmonitored mailbox.<br

class="style6" />

All replies should be sent to <a href="mailto:customerservice@website.com">

customerservice@website.com</a><span

class="style7">.

Account Number: ' + '' + @cust_cd + '

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

Dear ' + @fname + ' ' + @lname + ',<br

class="style3" />

 

Thank you for your purchase from Company Name. 

 

Please review your address and your shipping address... 

 

</td>

</tr>

<tr>

<td width="50%" class="style8">

Address:</td>

<td width="50%" class="style8">

Shipping Address:</td>

</tr>

<tr>

<td width="50%" class="style3">

' + @fname + ' ' + @lname + '<br

class="style3"/>

' + @Address + ' 

' + @city + ', ' + @st_cd + ' ' + @zip_cd + '<br

class="style3"/>

' + @home_phone + '</td>

<td width="50%">

' + @ship_to_f_name + ' ' + @ship_to_l_name + '<br

class="style3"/>

' + @ShipToAddress + ' 

' + @ship_to_city + ', ' + @ship_to_st_cd + ' ' + @ship_to_zip_cd + '<br

class="style3"/>

' + @ship_to_h_phone + '</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

To request a change to your ship to or bill to address, please

<a href="http://www.website.com/change-address-request.aspx">click here</a>. 

The following item(s) will arrive in our warehouse and will be ready for delivery in ' + @avail_text + '.' +

'[[[IN HERE IS WHERE I WOULD LIKE TO PUT THE ITEM LIST IN A TABLE FORMAT]]]' +

'If you have any questions, please feel free to contact us at customerservice@website.com.

</td>

</tr>

<tr>

<td width="50%">

</td>

<td width="50%">

</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%">

*** Please add <a href="mailto:noreply@website.com">noreply@website.com</a> to your safe senders list or address book to ensure delivery to your inbox. ***</td>

<td width="50%">

</td>

</tr>

</table>

 

</div>

</body>'

EXECUTE msdb.dbo.sp_send_dbmail

@subject = 'Your Order Availability!',

@recipients = @email,

@body_format = 'HTML',

@body = @body,

@profile_name ='SQL Server Agent';

END

CLOSE Contacts;

DEALLOCATE Contacts;

Thank you!

Lowell SSC Guru Points: 323518 More actions · Answer 7

ugg;

stick to the data ...without the data we cannot help.

lets forget all about formatting, cursors, everything...for now.

does this return all the records that should receive an email?

it's ok if the customer is repeated once per row...the question is would the results contain the data that is supposed to be int eh email.

if we know that has the data, THEN, we can begin to construct some XML that has the desired html formatting.

can you show, say the top 5 rows of that output so i can use it as a model?(change the emails of course to something fake)

SELECT --TOP 5

cust_cd,

email,

itm_cd,

vsn,

des,

qty,

FROM dbo.EMAIL_CBO_LIST_DAN

-- where

-- convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101)

-- AND pu_del = 'D' and open_del_date is not null

-- AND EmailSent is null

-- AND dbo.emailFilter(email) = 0

-- AND (financed = 'Y' or balance = 0);

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026 SSCrazy Points: 2765 More actions · Answer 8

Something like this?

cust_cdemailitm_cdvsndesqty

ARMSJ59806email@yahoo.com 3399020BRY,3399-02END TABLE2

ARMSJ59807email@yahoo.com 3399020BRY,3399-02END TABLE2

I am sorry, but I thought I had provided everything that is usually requested...

I don't need to send a separate email from within the email... I just want to format the line items, grouped by the cust_cd and ordered by del_doc_ln#.

Thank you very much. Sorry for the frustration....

donato1026 SSCrazy Points: 2765 More actions · Answer 9

I tried doing this... you can see where I am calling (or trying to setup and call the XML)... am I on the right track?

USE [DatabaseName]

GO

/****** Object: StoredProcedure [dbo].[usp_send_prodavail_email_ON-TIME] Script Date: 10/05/2011 13:36:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================================

--

-- Author:Dan

-- Created: 10/05/2011

-- Description:This procedure will loop through the EMAIL_CBO_LIST_DAN table and retrieve a list of customers.

-- From there, it will send an email to each customer letting them know the status of their order.

--

-- ===========================================================================================================

ALTER PROC [dbo].[usp_send_prodavail_email_ON-TIME]

AS

DECLARE

@del_doc_num nvarchar(30)

,@max_due_date datetime

,@insert_date datetime

,@bo_sku_count int

,@email varchar(60)

,@cust_cd varchar(10)

,@ship_to_f_name varchar(15)

,@ship_to_l_name varchar(20)

,@ship_to_addr1 varchar(30)

,@ship_to_addr2 varchar(30)

,@ship_to_city varchar(20)

,@ship_to_st_cd varchar(2)

,@ship_to_zip_cd varchar(10)

,@ship_to_h_phone varchar(12)

,@ShipToAddress varchar(MAX)

,@fname varchar(15)

,@lname varchar(20)

,@addr1 varchar(30)

,@addr2 varchar(30)

,@city varchar(20)

,@st_cd varchar(2)

,@zip_cd varchar(10)

,@home_phone varchar(12)

,@Address varchar(MAX)

,@avail_text varchar(36)

,@itm_cd varchar(9)

,@vsn varchar(30)

,@des varchar(30)

,@del_doc_ln# numeric(4,0)

,@qty int

,@body varchar(MAX);

DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT

distinct(cust_cd),

fname,

lname,

coalesce(

addr1 + char(13) + char(10) + addr2,

addr1,

addr2,

'') as Address,

--addr1,

--addr2,

--isnull(addr2, ''),

city,

st_cd,

zip_cd,

home_phone,

ship_to_f_name,

ship_to_l_name,

coalesce(

ship_to_addr1 + char(13) + char(10) + ship_to_addr2,

ship_to_addr1,

ship_to_addr2,

'') as ShipToAddress,

--ship_to_addr1,

--ship_to_addr2,

--isnull(ship_to_addr2, ''),

ship_to_city,

ship_to_st_cd,

ship_to_zip_cd,

ship_to_h_phone,

avail_text,

itm_cd,

vsn,

des,

del_doc_ln#,

qty,

email

FROM dbo.EMAIL_CBO_LIST_DAN

--where

--convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101)

--AND pu_del = 'D' and open_del_date is not null

--AND EmailSent is null

--AND dbo.emailFilter(email) = 0

--AND (financed = 'Y' or balance = 0);

OPEN Contacts;

WHILE 1 = 1

BEGIN

FETCH NEXT FROM Contacts INTO

@cust_cd,

@fname,

@lname,

@Address,

--@addr1,

--@addr2,

@city,

@st_cd,

@zip_cd,

@home_phone,

@ship_to_f_name,

@ship_to_l_name,

@ShipToAddress,

--@ship_to_addr1,

--@ship_to_addr2,

@ship_to_city,

@ship_to_st_cd,

@ship_to_zip_cd,

@ship_to_h_phone,

@avail_text,

@itm_cd,

@vsn,

@des,

@del_doc_ln#,

@qty,

@email;

IF @@FETCH_STATUS = -1 BREAK;

DECLARE @xml varchar(MAX)

DECLARE @value int

SET @value = (select COUNT(itm_cd) as itm from TransactionalData.dbo.EMAIL_CBO_LIST_DAN where email = @email)

IF @value > 0

BEGIN

SET @xml = CAST(( SELECT itm_cd AS 'td','',vsn AS 'td','',des AS 'td','',qty AS 'td'

FROM TransactionalData.dbo.EMAIL_CBO_LIST_DAN GROUP BY itm_cd, vsn, des, qty FOR XML PATH('tr'), ELEMENTS ) AS varchar(MAX))

SET @body = '<style type="text/css">

.style1

{

color: #FF0000;

}

.body

{

font-family: Arial, Helvetica, sans-serif;

font-size: small;

}

.style3

{

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

}

.style4

{

font-size: xx-small;

}

.style6

{

color: #FF0000;

font-family: Arial, Helvetica, sans-serif;

}

.style7

{

font-family: Arial, Helvetica, sans-serif;

font-size: xx-small;

}

.style8

{

font-family: Arial, Helvetica, sans-serif;

font-weight: bold;

font-size: x-small;

}

.style10

{

font-size: x-small;

}

</style><body>

<div>

<table style="width: 100%;">

<tr>

<td width="50%">

<a href="http://www.website.com">

<img src="http://www.website.com/skins/Skin_1/images/logo.jpg"

alt="Company Name" style="border-width: 0px" /></a>

</td>

<td width="50%">

Do NOT reply to this email. This is a system generated email from

an unmonitored mailbox.<br

class="style6" />

All replies should be sent to <a href="mailto:customerservice@website.com">

customerservice@website.com</a><span

class="style7">.

Account Number: ' + '' + @cust_cd + '

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

Dear ' + @fname + ' ' + @lname + ',<br

class="style3" />

 

Thank you for your purchase from Company Name. 

 

Please review your address and your shipping address... 

 

</td>

</tr>

<tr>

<td width="50%" class="style8">

Address:</td>

<td width="50%" class="style8">

Shipping Address:</td>

</tr>

<tr>

<td width="50%" class="style3">

' + @fname + ' ' + @lname + '<br

class="style3"/>

' + @Address + ' 

' + @city + ', ' + @st_cd + ' ' + @zip_cd + '<br

class="style3"/>

' + @home_phone + '</td>

<td width="50%">

' + @ship_to_f_name + ' ' + @ship_to_l_name + '<br

class="style3"/>

' + @ShipToAddress + ' 

' + @ship_to_city + ', ' + @ship_to_st_cd + ' ' + @ship_to_zip_cd + '<br

class="style3"/>

' + @ship_to_h_phone + '</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

To request a change to your ship to or bill to address, please

<a href="http://www.website.com/change-address-request.aspx">click here</a>. 

The following item(s) will arrive in our warehouse and will be ready for delivery in ' + @avail_text + '.' +

'<table border = 0 cellpadding=5><tr><th>ITEM CODE</th><th>VSN</th><th>DESCRIPTION</th><th>QTY</th></tr>'

+ @xml + '</table>' +

'If you have any questions, please feel free to contact us at customerservice@website.com.

</td>

</tr>

<tr>

<td width="50%">

</td>

<td width="50%">

</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%">

*** Please add <a href="mailto:noreply@website.com">noreply@website.com</a> to your safe senders list or address book to ensure delivery to your inbox. ***</td>

<td width="50%">

</td>

</tr>

</table>

 

</div>

</body>'

EXECUTE msdb.dbo.sp_send_dbmail

@subject = 'Your Order Availability!',

@recipients = @email,

@body_format = 'HTML',

@body = @body,

@profile_name ='SQL Server Agent';

END

CLOSE Contacts;

DEALLOCATE Contacts;

donato1026 SSCrazy Points: 2765 More actions · Answer 10

OK! I actually have this working to some degree, but depending on where I put the END, I get anywhere from one email to three emails or SIX emails! This is what I have now... can someone fine tune it please?

USE [TransactionalData]

GO

/****** Object: StoredProcedure [dbo].[usp_send_prodavail_email_ON-TIME] Script Date: 10/05/2011 13:36:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================================

--

-- Author:Dan

-- Created: 10/05/2011

-- Description:This procedure will loop through the EMAIL_CBO_LIST_DAN table and retrieve a list of customers.

-- From there, it will send an email to each customer letting them know the status of their order.

--

-- ===========================================================================================================

ALTER PROC [dbo].[usp_send_prodavail_email_ON-TIME]

AS

DECLARE

@xml varchar(MAX)

,@value int

,@del_doc_num nvarchar(30)

,@max_due_date datetime

,@insert_date datetime

,@bo_sku_count int

,@email varchar(60)

,@cust_cd varchar(10)

,@ship_to_f_name varchar(15)

,@ship_to_l_name varchar(20)

,@ship_to_addr1 varchar(30)

,@ship_to_addr2 varchar(30)

,@ship_to_city varchar(20)

,@ship_to_st_cd varchar(2)

,@ship_to_zip_cd varchar(10)

,@ship_to_h_phone varchar(12)

,@ShipToAddress varchar(MAX)

,@fname varchar(15)

,@lname varchar(20)

,@addr1 varchar(30)

,@addr2 varchar(30)

,@city varchar(20)

,@st_cd varchar(2)

,@zip_cd varchar(10)

,@home_phone varchar(12)

,@Address varchar(MAX)

,@avail_text varchar(36)

,@itm_cd varchar(9)

,@vsn varchar(30)

,@des varchar(30)

,@del_doc_ln# numeric(4,0)

,@qty int

,@body varchar(MAX);

DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT

distinct(cust_cd),

fname,

lname,

coalesce(

addr1 + char(13) + char(10) + addr2,

addr1,

addr2,

'') as Address,

--addr1,

--addr2,

--isnull(addr2, ''),

city,

st_cd,

zip_cd,

home_phone,

ship_to_f_name,

ship_to_l_name,

coalesce(

ship_to_addr1 + char(13) + char(10) + ship_to_addr2,

ship_to_addr1,

ship_to_addr2,

'') as ShipToAddress,

--ship_to_addr1,

--ship_to_addr2,

--isnull(ship_to_addr2, ''),

ship_to_city,

ship_to_st_cd,

ship_to_zip_cd,

ship_to_h_phone,

avail_text,

itm_cd,

vsn,

des,

del_doc_ln#,

qty,

email

FROM dbo.EMAIL_CBO_LIST_DAN

OPEN Contacts;

WHILE 1 = 1

BEGIN

FETCH NEXT FROM Contacts INTO

@cust_cd,

@fname,

@lname,

@Address,

--@addr1,

--@addr2,

@city,

@st_cd,

@zip_cd,

@home_phone,

@ship_to_f_name,

@ship_to_l_name,

@ShipToAddress,

--@ship_to_addr1,

--@ship_to_addr2,

@ship_to_city,

@ship_to_st_cd,

@ship_to_zip_cd,

@ship_to_h_phone,

@avail_text,

@itm_cd,

@vsn,

@des,

@del_doc_ln#,

@qty,

@email;

IF @@FETCH_STATUS = -1 BREAK;

SET @value = (select COUNT(itm_cd) as itm from TransactionalData.dbo.EMAIL_CBO_LIST_DAN where cust_cd = @cust_cd)

IF @value > 0

BEGIN

SET @xml = CAST(( SELECT itm_cd AS 'td','',vsn AS 'td','',des AS 'td','',qty AS 'td'

FROM TransactionalData.dbo.EMAIL_CBO_LIST_DAN WHERE cust_cd = @cust_cd GROUP BY itm_cd, vsn, des, qty FOR XML PATH('tr'), ELEMENTS ) AS varchar(MAX))

SET @body = '<style type="text/css">

.style1

{

color: #FF0000;

}

.body

{

font-family: Arial, Helvetica, sans-serif;

font-size: small;

}

.style3

{

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

}

.style4

{

font-size: xx-small;

}

.style6

{

color: #FF0000;

font-family: Arial, Helvetica, sans-serif;

}

.style7

{

font-family: Arial, Helvetica, sans-serif;

font-size: xx-small;

}

.style8

{

font-family: Arial, Helvetica, sans-serif;

font-weight: bold;

font-size: x-small;

}

.style10

{

font-size: x-small;

}

</style><body>

<div>

<table style="width: 100%;">

<tr>

<td width="50%">

<a href="http://www.website.com">

<img src="http://www.website.com/skins/Skin_1/images/logo.jpg"

alt="Company Name" style="border-width: 0px" /></a>

</td>

<td width="50%">

Do NOT reply to this email. This is a system generated email from

an unmonitored mailbox.<br

class="style6" />

All replies should be sent to <a href="mailto:customerservice@website.com">

customerservice@website.com</a><span

class="style7">.

Account Number: ' + '' + @cust_cd + '

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

Dear ' + @fname + ' ' + @lname + ',<br

class="style3" />

 

Thank you for your purchase from Company Name. 

 

Please review your address and your shipping address... 

 

</td>

</tr>

<tr>

<td width="50%" class="style8">

Address:</td>

<td width="50%" class="style8">

Shipping Address:</td>

</tr>

<tr>

<td width="50%" class="style3">

' + @fname + ' ' + @lname + '<br

class="style3"/>

' + @Address + ' 

' + @city + ', ' + @st_cd + ' ' + @zip_cd + '<br

class="style3"/>

' + @home_phone + '</td>

<td width="50%">

' + @ship_to_f_name + ' ' + @ship_to_l_name + '<br

class="style3"/>

' + @ShipToAddress + ' 

' + @ship_to_city + ', ' + @ship_to_st_cd + ' ' + @ship_to_zip_cd + '<br

class="style3"/>

' + @ship_to_h_phone + '</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%" colspan="2" style="width: 100%" class="style3">

To request a change to your ship to or bill to address, please

<a href="http://www.website.com/change-address-request.aspx">click here</a>. 

The following item(s) will arrive in our warehouse and will be ready for delivery in ' + '' + upper(@avail_text) + '' + '.' +

'' +

'<table border = 0 cellpadding=5><tr><th>ITEM CODE</th><th>VSN</th><th>DESCRIPTION</th><th>QTY</th></tr>'

+ @xml + '</table>' +

'If you have any questions, please feel free to contact us at customerservice@website.com.

</td>

</tr>

<tr>

<td width="50%">

</td>

<td width="50%">

</td>

</tr>

<tr>

<td width="50%" class="style3">

</td>

<td width="50%" class="style3">

</td>

</tr>

<tr>

<td width="50%">

*** Please add <a href="mailto:noreply@website.com">noreply@website.com</a> to your safe senders list or address book to ensure delivery to your inbox. ***</td>

<td width="50%">

</td>

</tr>

</table>

 

</div>

</body>'

EXECUTE msdb.dbo.sp_send_dbmail

@subject = 'Your Order Availability!',

@recipients = @email,

--@recipients = 'ddicecca@hotmail.com;ddicecca@website.com;donato1026@yahoo.com',

@blind_copy_recipients = 'ddicecca@website.com',

@body_format = 'HTML',

@body = @body,

@profile_name ='SQL Server Agent';

END;

CLOSE Contacts;

DEALLOCATE Contacts;

donato1026 SSCrazy Points: 2765 More actions · Answer 11

donato1026

SSCrazy

Points: 2765

October 6, 2011 at 6:55 am

#1391180

Anyone out there?

donato1026 SSCrazy Points: 2765 More actions · Answer 12

I figured it out and it works awesome!

Thanks anyway...

spaghettidba SSC Guru Points: 105732 More actions · Answer 13

Great! Congrats.

You could post the working code here, so that others with the same problem can benefit from your solution.

-- Gianluca Sartori