December 1, 2009 at 9:23 am
Created a stored procedure to look at Notes table that extracts notes, author and author's e-mail address based on criteria such as note type and number of days old. Trying to send results via e-mail using DBMail in SQL Server 2005. E-Mails should be sent to the author of the note(s).
My problem is that I want to "batch" all of the notes that meet the select criteria by author/e-mail address and sent 1 e-mail to the author that lists all of the selected notes. Example: Author Steve wrote 5 notes that meet the note type/age date criteria. Want to send 1 e-mail to
steve@company.com the lists the extracted notes data:
entrydate, notetype, author, e-mail address, companyname, contact firstname, contact lastname
In the cursor select statement, I ordered the results by e-mail address but this is not working as above example.
Everything else is working in the stored procedure except for this grouping/batching.
Attached Word document has table create script and test data - per article on how to post data/code to forum.
Stored Procedure script is below.
Stored Procedure Script
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- 2009/11/19 rss - new
-- Procedure to pull specific CRM Notes for e-mail notification
ALTER PROCEDURE [dbo].[QVI_CRMNotesNotificationSP]
AS
SET NOCOUNT ON
--- Creates temporary @tempnotes table to hold report ouput
DECLARE @tempnotes TABLE
( entrydate datetime
, notetype nvarchar(30)
, employeename nvarchar(50)
, title nvarchar(30)
, emailaddress nvarchar(255)
, companyname nvarchar(60)
, firstname nvarchar(20)
, middleinit nvarchar(1)
, lastname nvarchar(30)
, companyid uniqueidentifier
, employeeid uniqueidentifier
, icompemployeeid uniqueidentifier
)
DECLARE
@noteentrydate datetime
, @notetype nvarchar(30)
, @icompemployeename nvarchar(50)
, @icomptitle nvarchar(30)
, @icompemailaddress nvarchar(255)
, @xcompcompanyname nvarchar(60)
, @contactfirstname nvarchar(20)
, @contactmiddleinit nvarchar(1)
, @contactlastname nvarchar(30)
, @notecompanyid uniqueidentifier
, @noteemployeeid uniqueidentifier
, @icompemployeeid uniqueidentifier
, @notecontactid uniqueidentifier
, @concontactid uniqueidentifier
, @subj nvarchar(255)
, @body nvarchar(4000)
, @temp nvarchar(255)
, @to nvarchar(1000)
, @message nvarchar(4000)
DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR
SELECT
n.entrydate --- Notes entry date
, n.notetype --- Note Type
, i.employeename --- Note Author
, i.title --- Note Author Title
, i.emailaddress --- Note Author E-Mail address
, x.companyname --- Company/Account associated with Note
, c.firstname --- Contact associated with Note
, c.middleinit
, c.lastname
FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c
WHERE
n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
----and n.notetype = 'E-Mail Sent'
and DATEDIFF(day, n.entrydate, GETDATE()) between 0 and 90 ---- variable
order by i.emailaddress
---- Start Cursor Processing
OPEN CRMNotesPullCrs WHILE 1=1
BEGIN
FETCH CRMNotesPullCrs INTO
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
IF @@FETCH_STATUS <> 0 BREAK
---- End Cursor Processing
BEGIN --- @tempnotes temporary table
INSERT INTO @tempnotes
(
entrydate
, notetype
, employeename
, title
, emailaddress
, companyname
, firstname
, middleinit
, lastname
, companyid
, employeeid
, icompemployeeid
)
VALUES
(
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
)
END --- @tempnotes temporary table
---- new code rss 11-30-09
BEGIN --- e-mail distribution section
SET @to = @icompemailaddress
SET @subj = 'Notes Follow-up Notice'
SET @message = 'EntryDate Author E-Mail Address CompanyName FirstName LastName' + CHAR(10) + CHAR(13)
---- WHILE @@FETCH_STATUS = 0 --- (not sure if this is needed???)
BEGIN
SET @temp = (select CAST (@noteentrydate AS nvarchar(12)) ) + ' ' + @icompemployeename + ' ' + @icompemailaddress + ' ' + @xcompcompanyname + ' ' + @contactfirstname + ' ' + @contactlastname + ' ' + CHAR(10)
SET @message = @message + @temp
END --- e-mail distribution section
BEGIN --- dbmail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Job-Mail'
, @recipients = @to
, @subject = @subj
, @body = @message
, @body_format = 'HTML'
---- 'This is a test message from CRM via SQL Database Mail. This message is a reminder to follow-up on sales calls.'
---- debug print statements
print ' '
print ' '
print 'email output start set'
print @to
print @subj
print @message
print 'email output end set'
print ' '
--- end debug print statements
END --- db mail code
END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs
BEGIN --- data into @tempnotes temporary table
SELECT * from @tempnotes
ORDER BY employeename, companyname, entrydate
------- order by firstname --- test
END --- data into @tempnotes temporary table
END
SET NOCOUNT OFF
December 1, 2009 at 2:05 pm
It was a valiant attempt at sample data, but unfortunately it's not quite there. Here are a few of the errors:
No insert statement for the table
Word often uses 'smart quotes' which don't translate correctly back into sql and cause issues.
You only used the # temp table for the initial check, not for the table you created.
commas before the union alls
invalid number of columns in the sample data compared to the table.
Etc. etc. I tried to fix it for a while but there were simply too many mistake to correct. You could probably actually pick up quite a bit just by figuring out how to fix all these errors in your test data. The goal is for us to be able to copy/paste/run this in SSMS and have a small example of your table's data. Massage that script(use a dev or test server) until you get it all working and you'll get a better understanding of how it works. Also, not sure if those email addresses are real or not, but you want to be really careful of actual data when you post sample data on the internet.
For your actual problem:
First, Consider using the new ANSI style joins instead of the old style
Second, I will probably get yelled at by someone for suggesting nested cursors here, so I'll throw out a disclaimer that this is almost never the best solution. Try to avoid cursors for the most part.
At a glance, it looks like you're trying to pull in all the information into a cursor then loop through that cursor. The problem with this is that each message is on a different row and you're only accessing 1 row at a time. Going with a nested cursor approach for something like this, you'd want the outer cursor to pull in just the email address/name of each individual you're sending a message to, then the inner cursor would pull all the actual notes for each one, concatenate them into the body then exit. Your parent cursor would send the message then loop.
Here's the gist of what I mean
Take your current select statement and insert it into a temp table.
SELECT...
INTO #temp
FROM...
WHERE...
order by...
Use just the emailaddress as the declaration of the outer cursor
DECLARE CURSOR... --Outer
SELECT DISTINCT emailaddress
FROM #Temp
BEGIN
Within that:
DECLARE CURSOR--Inner
SELECT...(all other fields)
FROM #Temp
WHERE emailaddress = @emailaddress
BEGIN
--Concatenate message fields
END
CLOSE / DEALLOCATE --Inner
--Send email
END
CLOSE / DEALLOCATE --Outer
December 2, 2009 at 7:53 am
Thanks for your response. I have fixed and posted the test data below. Also attached a text file with the same.
I am still unclear on the structure and how to code the nested cursors that you had suggested. I understand the concept - again it is the structure and coding that I am not getting. My stored procedure is also posted below.
Can you also explain and give an example of the new ANSI joins style versus what I am using now (??) ?
Thanks.
UPDATED AND FIXED TEST DATA:
------ If the test table already exists, drop it
IF OBJECT_ID('TempDB..#NOTE_tblNote', 'U') IS NOT NULL
DROP TABLE #NOTE_tblNote
---- Create the test table with
/****** Object: Table [dbo].[#NOTE_tblNote] Script Date: 12/02/2009 09:05:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[#NOTE_tblNote](
[NoteID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_NOTE_tblNote_NoteID] DEFAULT (newid()),
[CompanyID] [uniqueidentifier] NOT NULL,
[EmployeeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EmployeeID] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ContactID] [uniqueidentifier] NULL,
[OpportunityID] [uniqueidentifier] NULL,
[TaskID] [uniqueidentifier] NULL,
[CaseID] [uniqueidentifier] NULL,
[CampaignID] [uniqueidentifier] NULL,
[ProjectID] [uniqueidentifier] NULL,
[EntryDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EntryDate] DEFAULT (getdate()),
[NoteType] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextRTF] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextTrunc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactMade] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ContactMade] DEFAULT ((0)),
[OleObjects] [image] NULL,
[BPartnerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_BPartnerAvailable] DEFAULT ((0)),
[CustomerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CustomerAvailable] DEFAULT ((0)),
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CreationDate] DEFAULT (getdate()),
[ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ChangeDate] DEFAULT (getdate()),
[ChangeDateOffline] [datetime] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__30E2A0C7] DEFAULT (getdate()),
[ChangedByID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__31D6C500] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ChangedByIDOffline] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__32CAE939] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
CONSTRAINT [PK_NOTE_tblNote] PRIMARY KEY NONCLUSTERED
(
[NoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [eCRM_crmtest1]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign] FOREIGN KEY([CampaignID])
REFERENCES [dbo].[CAMP_tblCampaign] ([CampaignID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CON_tblContact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[CON_tblContact] ([ContactID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CON_tblContact]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[ICOMP_tblEmployee] ([EmployeeID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity] FOREIGN KEY([OpportunityID])
REFERENCES [dbo].[OPP_tblOpportunity] ([OpportunityID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[PROJ_tblProject] ([ProjectID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase] FOREIGN KEY([CaseID])
REFERENCES [dbo].[SERV_tblCase] ([CaseID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask] FOREIGN KEY([TaskID])
REFERENCES [dbo].[TASK_tblTask] ([TaskID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[XCOMP_tblCompany] ([CompanyID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany]
----- END Create Table script
------ 7 row(s) data
------ All Inserts into the IDENTITY column
SET IDENTITY_INSERT #NOTE_tblNote ON
------ Insert the test data into the test table
INSERT INTO #NOTE_tblNote
(NoteID,CompanyID,EmployeeID,ContactID,OpportunityID,TaskID,CaseID,CampaignID,ProjectID,EntryDate,NoteType,NoteText,NoteTextRTF,NoteTextTrunc,ContactMade,OleObjects,BPartnerAvailable,CustomerAvailable,CreationDate,ChangeDate,ChangeDateOffline,ChangedByID,ChangedByIDOffline)
SELECT '335551BB-415E-48C0-A218-C7814C29A718','34B1D274-D622-428F-B11E-11247CE293BC','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E44AACF0-C835-4A04-A96F-B8273C1700F7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:54:49.000','E-Mail Received','Original E-Mail Address: test1@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test1@dummycompany.com\par }','Original E-Mail Address: test1@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:00:21.463','2009-12-01 10:30:44.073','2009-10-16 10:00:21.463','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E9C16DE1-613F-4CBD-8651-BBD92A68A377','0617409F-5D39-4C51-9708-368407D336BD','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D3B59963-2B28-459E-85F7-A1E4987FAAB7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:56:51.000','Contact','Original E-Mail Address: test2@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test2@dummycompany.com\par }','Original E-Mail Address: test2@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E36A3A02-0C67-4233-9740-16F6EE8E3D46','305299B1-81DE-4EDF-BCD5-4D82FFACDD91','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','9BC2D972-2602-457B-974F-BCCB33971493','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:52:42.000','Contact','Original E-Mail address: test3@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test3@dummycompany.com\par }','Original E-Mail address: test3@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'DB1F0867-7515-4E45-AE69-29D353CBE90D','750C7124-CBBB-4D1A-B8DA-854DB2E2DF0C','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E64A2EE8-DA05-482D-BCAA-8C4AD100C6DD','NULL','NULL','NULL','NULL','NULL','2009-10-22 14:41:15.000','Contact','Original E-Mail address: test4@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test4@dummycompany.com\par }','Original E-Mail address: test4@dummycompany.com','0','0x00000000','0','0','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT '115BBFCB-117C-4668-9BD5-1C09E777FB34','3E67D88D-042D-4E27-B65A-9DC31962B0F2','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','1A4EC154-AC62-4327-AB99-A0FD0B79BEB6','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:53:53.000','Contact','Original E-Mail address: test5@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test5@dummycompany.com\par }','Original E-Mail address: test5@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'AD8EB582-6174-4971-9934-4D501A0D7115','957FFCD7-3077-4A99-8465-D1DF5458A146','49230AD2-14FC-4470-83BF-D07EE95EED17','9938A6C4-25AD-443E-8397-98F51446496C','NULL','NULL','NULL','NULL','NULL','2009-11-18 09:49:30.000','E-Mail Sent','Test','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Test\par }','Test','0','0x00000000','0','0','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'CA83B9A3-9099-495E-8184-985B75FCFEEC','6BCBCFC8-8E8E-49AD-87CB-FB49797005DF','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D5641FB2-A787-493B-A7DA-96B17C12FE0E','NULL','NULL','NULL','NULL','NULL','2009-11-24 15:21:54.000','Phone Call','test1','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 test1\par }','test1','0','0x00000000','0','0','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111'
------ Set the IDENTITY_INSERT back to normal
SET IDENTITY_INSERT #NOTE_tblNote OFF
----- End Data Insert script
--------- START STORED PROCEDURE CODE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2009/11/19 rss - new
-- Procedure to pull specific CRM Notes for e-mail notification
ALTER PROCEDURE [dbo].[QVI_CRMNotesNotificationSP]
AS
SET NOCOUNT ON
--- Creates temporary @tempnotes table to hold report ouput
DECLARE @tempnotes TABLE
( entrydate datetime
, notetype nvarchar(30)
, employeename nvarchar(50)
, title nvarchar(30)
, emailaddress nvarchar(255)
, companyname nvarchar(60)
, firstname nvarchar(20)
, middleinit nvarchar(1)
, lastname nvarchar(30)
, companyid uniqueidentifier
, employeeid uniqueidentifier
, icompemployeeid uniqueidentifier
)
DECLARE
@noteentrydate datetime
, @notetype nvarchar(30)
, @icompemployeename nvarchar(50)
, @icomptitle nvarchar(30)
, @icompemailaddress nvarchar(255)
, @xcompcompanyname nvarchar(60)
, @contactfirstname nvarchar(20)
, @contactmiddleinit nvarchar(1)
, @contactlastname nvarchar(30)
, @notecompanyid uniqueidentifier
, @noteemployeeid uniqueidentifier
, @icompemployeeid uniqueidentifier
, @notecontactid uniqueidentifier
, @concontactid uniqueidentifier
, @subj nvarchar(255)
, @body nvarchar(4000)
, @temp nvarchar(255)
, @to nvarchar(1000)
, @message nvarchar(4000)
DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR
SELECT
n.entrydate --- Notes entry date
, n.notetype --- Note Type
, i.employeename --- Note Author
, i.title --- Note Author Title
, i.emailaddress --- Note Author E-Mail address
, x.companyname --- Company/Account associated with Note
, c.firstname --- Contact associated with Note
, c.middleinit
, c.lastname
FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c
WHERE
n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
and n.notetype = 'E-Mail Sent'
and DATEDIFF(day, n.entrydate, GETDATE()) between 0 and 90 ---- variable
order by i.emailaddress
---- Start Cursor Processing
OPEN CRMNotesPullCrs WHILE 1=1
BEGIN
FETCH CRMNotesPullCrs INTO
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
IF @@FETCH_STATUS <> 0 BREAK
---- End Cursor Processing
BEGIN --- @tempnotes temporary table
INSERT INTO @tempnotes
(
entrydate
, notetype
, employeename
, title
, emailaddress
, companyname
, firstname
, middleinit
, lastname
, companyid
, employeeid
, icompemployeeid
)
VALUES
(
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
)
END --- @tempnotes temporary table
---- new code rss 11-30-09
BEGIN --- e-mail distribution section
SET @to = @icompemailaddress
SET @subj = 'Notes Follow-up Notice'
SET @message = 'EntryDate Author E-Mail Address CompanyName FirstName LastName' + CHAR(10) + CHAR(13)
----- WHILE @@FETCH_STATUS = 0 ---- not sure if this statement is needed ????????
BEGIN
SET @temp = (select CAST (@noteentrydate AS nvarchar(12)) ) + ' ' + @icompemployeename + ' ' + @icompemailaddress + ' ' + @xcompcompanyname + ' ' + @contactfirstname + ' ' + @contactlastname + ' ' + CHAR(10)
SET @message = @message + @temp
END --- e-mail distribution section
BEGIN --- dbmail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Job-Mail'
, @recipients = @to
, @subject = @subj
, @body = @message
, @body_format = 'HTML'
---- 'This is a test message from CRM via SQL Database Mail. This message is a reminder to follow-up on sales calls.'
--- Start debug/testing print statements
print ' '
print ' '
print 'email output start set'
print @to
print @subj
print @message
print 'email output end set'
print ' '
--- End debug/testing print statements
END --- db mail code
END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs
BEGIN --- data into @tempnotes temporary table
SELECT * from @tempnotes
ORDER BY employeename, companyname, entrydate
------- order by firstname --- for testing only
END --- data into @tempnotes temporary table
END
SET NOCOUNT OFF
December 2, 2009 at 8:21 am
As a further follow-up and question:
When using the QUOTENAME script to pull/extract data from table (example script below) I had the following issues:
5 fields that had Null values for all records (OpportunityID,TaskID, CaseID, CampaignID, ProjectID)
3 fields with '0' as value for all records (ContactMade, BPartnerAvailable, CustomerAvailable)
2 fields as ntext datatype (NoteTextRTF, NoteTextTrunc)
When running the QUOTENAME script below, if it hit one of these fields then the result was one column of 'NULL'. When I commented out these fields from the script, then was able to get the correct results.
QUESTION: How to code or fix the script to allow extraction of the above fields (Null, 0, ntext values) so that the QUOTENAME script will extract all of the data correctly?
Thanks.
QUOTENAME SCRIPT:
select 'Select '
+ QUOTENAME(NoteID,'''') + ','
+ QUOTENAME(CompanyID, '''') + ','
+ QUOTENAME(EmployeeID, '''') + ','
+ QUOTENAME(ContactID, '''') + ','
+ QUOTENAME(OpportunityID,'''') + ','
+ QUOTENAME(TaskID, '''') + ','
+ QUOTENAME(CaseID, '''') + ','
+ QUOTENAME(CampaignID, '''') + ','
QUOTENAME(ProjectID, '''') + ','
+ QUOTENAME(EntryDate, '''') + ','
+ QUOTENAME(NoteType, '''') + ','
+ QUOTENAME(NoteText, '''') + ','
+ QUOTENAME(NoteTextRTF, '''') + ','
+ QUOTENAME(NoteTextTrunc, '''') + ','
+ QUOTENAME(ContactMade, '''') + ','
+ QUOTENAME(BPartnerAvailable, '''') + ','
+ QUOTENAME(CustomerAvailable, '''') + ','
+ QUOTENAME(CreationDate, '''') + ','
+ QUOTENAME(ChangeDate, '''') + ','
+ QUOTENAME(ChangeDateOffline, '''') + ','
+ QUOTENAME(ChangedByID, '''') + ','
+ QUOTENAME(ChangedByIDOffline, '''') + ','
+ ' UNION ALL'
from dbo.NOTE_tblNote
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply