August 26, 2014 at 8:30 pm
This is my table and a few data,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CRM]') AND type in (N'U'))
DROP TABLE [dbo].[CRM]
GO
/****** Object: Table [dbo].[CRM] Script Date: 08/27/2014 10:28:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CRM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CRM](
[idx] [int] IDENTITY(1,1) NOT NULL,
[nme] [nvarchar](200) NULL,
[nvarchar](50) NULL,
[subject] [nvarchar](100) NULL,
[body] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[CRM] ON
INSERT [dbo].[CRM] ([idx], [nme], , [subject], [body]) VALUES (1, N'bro cat', N'brocat@gmail.com', N'NEW MOVIE', N'SUPER KING')
INSERT [dbo].[CRM] ([idx], [nme], , [subject], [body]) VALUES (2, N'john', N'john@gmail.com', N'COMING SOON MOVIE', N'SLEEPYMAN')
INSERT [dbo].[CRM] ([idx], [nme], , [subject], [body]) VALUES (3, N'aireen', N'aireen@yahoo.com', N'CANCELED MOVIE', N'ANAK HARAM')
SET IDENTITY_INSERT [dbo].[CRM] OFF
I want to send email to all recipient in this CRM table
So, my T-SQL shown as follow,
DECLARE @i int
DECLARE @idx int
DECLARE @numrows int
-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM dbo.CRM)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM dbo.CRM))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name='nme',
@recipients='email',
@subject = 'subject',
@body = 'body',
@body_format = 'HTML'
SET @i = @i + 1
END
I need help to getting one by one - nme, email, subject, and body. Don't know how to do that in WHILE LOOP.
Please
August 26, 2014 at 9:05 pm
You want to use the "@i" to select each record of your CRM table and populate each value into a variable so you can then pass it into the send mail procedure.
Something like this inside your while loop should work:
DECLARE @idx int,
@nme nvarchar(200),
@email nvarchar(50),
@subject nvarchar(100),
@tbody text
SELECT @idx = idx, @nme=nme, @email=email, @subject=subject, @tbody = body
FROM [dbo].[CRM]
WHERE idx = @i
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @nme,
@recipients = @email,
@subject = @subject,
@body = @tbody
@body_format = 'HTML'
SET @i = @i + 1
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 26, 2014 at 9:15 pm
Based on your input, I've this
DECLARE @i int
DECLARE @idx int
DECLARE @numrows int
-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM dbo.CRM)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM dbo.CRM))
BEGIN
DECLARE
@nme nvarchar(200),
@email nvarchar(50),
@subject nvarchar(100),
@tbody text
SELECT @idx = idx, @nme=nme, @email=email, @subject=subject, @tbody = body
FROM [dbo].[CRM]
WHERE idx = @i
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @nme,
@recipients = @email,
@subject = @subject,
@body = @tbody,
@body_format = 'HTML'
--print @nme
SET @i = @i + 1
END
But got en error,
Msg 2739, Level 16, State 1, Line 38
The text, ntext, and image data types are invalid for local variables.
August 26, 2014 at 10:16 pm
Little Nick (8/26/2014)
But got en error,
Msg 2739, Level 16, State 1, Line 38The text, ntext, and image data types are invalid for local variables.
Ah did not pay attention to that one. Text data types cannot be used in variables. You would need to convert it to varchar(max), and actually should probably change that table column to this data type as the text data type is being depreciated at some point.
I have not tried this but think doing something like:
--change the variable
@tbody varchar(max)
-then you have to convert your text to varchar
@tbody = CAST(body AS varchar(max))
So just change the tbody variable to varchar(max) and then in the select statement you need to cast the body column as varchar(max). I think that should allow it to work but you will need to test it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply