romeiovasu
asked on
sql store procdure issue, sql 2005
hi All i have written a query where it sends an email it sending email but for some reaso in body it is showing only Dear after that everything is miising in tablehtml.
can some one take a look at my query and tell me what mistake i am doing in my @tablehtml.
use [stagingdatabase]
create table #emailsent(
customerid nvarchar(50) not null,
customername nvarchar(255),
customeremail nvarchar(255),
storeid nvarchar(3),
[storeaddress] nvarchar(255),
storecity nvarchar(50),
storeprovince nvarchar(3),
storephonenumber nvarchar(10),
storeZip nvarchar(10),
Storeemail nvarchar(255),
Storemanager nvarchar(255),
storeemailnotes nvarchar(max),
emailsend bit)
insert into #emailsent (customerid,customername,c ustomerema il,storeid )
select customerid,customername,em ail,storei d from dbo.customeremails where emailsend is null group by customerid,customername,em ail,storei d
update #emailsent set storeid = st.storeid,[storeaddress] = st.address,storecity = st.city,storeprovince = st.province,storezip = st.zip,storephonenumber = st.phonenumber,storeemail = st.storeemail,storemanager = st.manager,storeemailnotes = st.emailnotes
from #emailsent es inner join dbo.iqmlocations st on es.storeid = st.storeid
declare @customerid nvarchar(10),
@customeremail nvarchar(255),@storeemail nvarchar(255)
declare cusid cursor FAST_FORWARD FOR
select customerid,customeremail,s toreemail from #emailsent where emailsend is null
DECLARE @tableHTML NVARCHAR(MAX);
OPEN cusid
FETCH NEXT FROM cusid
INTO @customerid,@customeremail ,@storeema il
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tableHTML =
N'<b>Dear ' select customername from #emailsent where customerid = @customerid + '</b></br></br>' +
N'Thank you for shopping with us</br> If you have any problems please do contact us</br>' +
N'Thank you </br>'+
N'Manager ' Select storemanager from #emailsent where customerid = @customerid + '</br>' +
N'<b> ' Select storeaddress from #emailsent where customerid = @customerid + '</br>'
--N '<b>'select storeaddress from #emailsent where customerid = @customerid + '</b></br>'
;
EXEC msdb.dbo.sp_send_dbmail
@recipients=@customeremail ,
-- @replyto_address = @storeemail,
@subject = 'Thank You' ,
@body = @tableHTML,
@body_format = 'HTML'
--select * from #emailsent
update dbo.#emailsent set emailsend = 1 from dbo.#emailsent where customerid = @customerid
FETCH NEXT FROM cusid
INTO @customerid,@customeremail ,@storeema il
END
CLOSE cusid
DEALLOCATE cusid
update dbo.customeremails set emailsend = '1' --from dbo.neccredits n inner join #nec1 ne on n.docnum = ne.docnum
select * from #emailsent
drop table #emailsent
can some one take a look at my query and tell me what mistake i am doing in my @tablehtml.
use [stagingdatabase]
create table #emailsent(
customerid nvarchar(50) not null,
customername nvarchar(255),
customeremail nvarchar(255),
storeid nvarchar(3),
[storeaddress] nvarchar(255),
storecity nvarchar(50),
storeprovince nvarchar(3),
storephonenumber nvarchar(10),
storeZip nvarchar(10),
Storeemail nvarchar(255),
Storemanager nvarchar(255),
storeemailnotes nvarchar(max),
emailsend bit)
insert into #emailsent (customerid,customername,c
select customerid,customername,em
update #emailsent set storeid = st.storeid,[storeaddress] = st.address,storecity = st.city,storeprovince = st.province,storezip = st.zip,storephonenumber = st.phonenumber,storeemail = st.storeemail,storemanager
from #emailsent es inner join dbo.iqmlocations st on es.storeid = st.storeid
declare @customerid nvarchar(10),
@customeremail nvarchar(255),@storeemail nvarchar(255)
declare cusid cursor FAST_FORWARD FOR
select customerid,customeremail,s
DECLARE @tableHTML NVARCHAR(MAX);
OPEN cusid
FETCH NEXT FROM cusid
INTO @customerid,@customeremail
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tableHTML =
N'<b>Dear ' select customername from #emailsent where customerid = @customerid + '</b></br></br>' +
N'Thank you for shopping with us</br> If you have any problems please do contact us</br>' +
N'Thank you </br>'+
N'Manager ' Select storemanager from #emailsent where customerid = @customerid + '</br>' +
N'<b> ' Select storeaddress from #emailsent where customerid = @customerid + '</br>'
--N '<b>'select storeaddress from #emailsent where customerid = @customerid + '</b></br>'
;
EXEC msdb.dbo.sp_send_dbmail
@recipients=@customeremail
-- @replyto_address = @storeemail,
@subject = 'Thank You' ,
@body = @tableHTML,
@body_format = 'HTML'
--select * from #emailsent
update dbo.#emailsent set emailsend = 1 from dbo.#emailsent where customerid = @customerid
FETCH NEXT FROM cusid
INTO @customerid,@customeremail
END
CLOSE cusid
DEALLOCATE cusid
update dbo.customeremails set emailsend = '1' --from dbo.neccredits n inner join #nec1 ne on n.docnum = ne.docnum
select * from #emailsent
drop table #emailsent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.