Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server Error

Status
Not open for further replies.
bdmoran

bdmoran

MIS
Nov 7, 2011
87
US
Hi all,

I am creating a stored procedure to send an email and when I execute the SP i get this error:

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 237
At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".


Here is part of my procedure:

DECLARE @live_order_id nvarchar(10),@add_usr varchar (100),@cust_name_id nvarchar(10),@class_name nvarchar(100), @order_id nvarchar(10),@strSubject varchar(100),@file_attachments nvarchar (100), @strBody varchar(4000), @type tinyint, @bitmap char (6), @event_name nvarchar(150), @cursor varchar(25),@event_datetime varchar (100), @section_name varchar (100),@email_addr nvarchar(100),
@name varchar(100),@additional varchar (4000),@recipients varchar(25),@profile_name varchar (25), @num_seats nvarchar (100), @date_entered varchar(100), @fulfilledon varchar(100),@inet_event_description varchar(100),@other_info_3 varchar(100),@acct_id nvarchar (25)
DECLARE UnVerifiedCursor CURSOR FOR
SELECT v_live_inventory.live_order_id, v_returned_inventory.other_info_3, v_live_inventory.inet_event_description, v_live_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description,
v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last
HAVING (v_returned_inventory.class_name = 'INT_BIRTHDAY')


--open cursor
OPEN UnVerifiedCursor
FETCH NEXT FROM UnVerifiedCursor INTO @order_id,@other_info_3,@inet_event_description,@section_name,@add_usr,@num_seats,@acct_id,@class_name,@email_addr,@cust_name_id,@name
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN
SET @strBody = 'Birthday Suite Notification

Addtional Notes:' + SPACE(1) + @other_info_3 + SPACE(1) +'


Event Name:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_datetime,120) + SPACE(1) + '
Section:' + SPACE(1) + @section_name + SPACE(1) + '


'+'Order Information:' +'
--------------------------------------------------------------------------------
Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +'
Order ID:' + SPACE(1) + @order_id + SPACE(1) +'
Acct ID:' + SPACE(1) + @acct_id + SPACE(1) +'
--------------------------------------------------------------------------------'

SET @strSubject = 'BIRTHDAY SUITE '+ @event_datetime + SPACE(1)+' '
-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = 'test@test.com',
@Body = @strBody,
@Subject = @strSubject
END

FETCH NEXT FROM UnVerifiedCursor INTO @order_id,@other_info_3,@inet_event_description,@section_name,@add_usr,@num_seats,@acct_id,@class_name,@email_addr,@cust_name_id,@name
END
-- close cursor
CLOSE UnVerifiedCursor
DEALLOCATE UnVerifiedCursor


Please let me know if you can help if you need any additional information. Thanks!
 
Sort by date Sort by votes
Looks like you're specifying Body and Subject, so the error message is a little confusing. However... it is possible that you have NULL's in your data?

For example, if @event_datetime is NULL, then both @strBody and @strSubject would both be NULL.

The easiest way to verify this would be to put a little debugging code in to the procedure. This debugging code should be removed before you put this in to production.

Code: 
-- Just be sp_send_dbmail
Select @strSubject, @strBody, @other_info_3, @inet_event_description, @event_dateTime, @Section_name, @name, @email_addr, @order_id, @acct_id

-- Comment out the send code until you have this debugged.
-- EXEC msdb.dbo.sp_send_dbmail
-- etc...

Now when you run this code, you will see the subject, body, and all the parts that make up the subject and body. If anything returns NULL, you will have probably found your problem. How you deal with NULL's will depend on your business needs. For example, if the event_datetime is null, you should probably send a different email like, "Please tell us the date and time for the event...".



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
When concatenating I try and use the ISNULL function. That was if something is null my whole is not null.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
Gmmastros - thank you for your response. If i understand correctly, I commented out my select statement, and inserted the one you provided. However I am still getting the same error:


At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".
 
Upvote 0 Downvote
Did you also comment out the part that sends the mail like he suggested? All you want at this point is for the result window to show what your commands would look like so you can look for issues.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Upvote 0 Downvote
SQLBill - I just commented out the send email portion and my query executed without any errors...So im assuming there aren't any nulls. Here is what I changed it to. Maybe if you take a look you can pinpoint some errors. Thanks again for your quick repsonses !


DECLARE @live_order_id nvarchar(10),@add_usr varchar (100),@cust_name_id nvarchar(10),@class_name nvarchar(100), @order_id nvarchar(10),@strSubject varchar(100),@file_attachments nvarchar (100), @strBody varchar(4000), @type tinyint, @bitmap char (6), @event_name nvarchar(150), @cursor varchar(25),@event_datetime varchar (100), @section_name varchar (100),@email_addr nvarchar(100),
@name varchar(100),@additional varchar (4000),@recipients varchar(25),@profile_name varchar (25), @num_seats nvarchar (100), @date_entered varchar(100), @fulfilledon varchar(100),@inet_event_description varchar(100),@other_info_3 varchar(100),@acct_id nvarchar (25)
DECLARE UnVerifiedCursor CURSOR FOR
--SELECT v_live_inventory.live_order_id,v_live_inventory.inet_event_description, v_live_inventory.section_name,
--v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name,
--AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
--FROM v_returned_inventory INNER JOIN
--v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
--v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
-- AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
--OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
-- AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
--GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description,
-- v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name,
--AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last
--HAVING (v_returned_inventory.class_name = 'INT_BIRTHDAY')
Select @strSubject, @strBody, @other_info_3, @inet_event_description, @event_dateTime, @Section_name


--open cursor
OPEN UnVerifiedCursor
FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN
SET @strBody = 'Birthday Suite Notification




Event Name:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_datetime,120) + SPACE(1) + '
Section:' + SPACE(1) + @section_name + SPACE(1) + '


'+'Order Information:' +'
--------------------------------------------------------------------------------
Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +'
Order ID:' + SPACE(1) + @order_id + SPACE(1) +'
Acct ID:' + SPACE(1) + @acct_id + SPACE(1) +'
--------------------------------------------------------------------------------'

SET @strSubject = 'BIRTHDAY SUITE '+ @event_datetime + SPACE(1)+' '
-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
--EXEC msdb.dbo.sp_send_dbmail @strSubject,
-- @profile_name = 'Orders_at_TDGarden_profile',
--@recipients = 'bdmoran@dncboston.com',
--@Body = @strBody,
--@Subject = @strSubject
END

FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
END
-- close cursor
CLOSE UnVerifiedCursor
DEALLOCATE UnVerifiedCursor
 
Upvote 0 Downvote
Try this:

Code: 
GetZoneList
Select * From DistrictPoints Where DistrictId = 12 Order By SequenceNumber



DECLARE @live_order_id nvarchar(10),@add_usr varchar (100),@cust_name_id nvarchar(10),@class_name nvarchar(100), @order_id nvarchar(10),@strSubject varchar(100),@file_attachments nvarchar (100), @strBody varchar(4000), @type tinyint, @bitmap char (6), @event_name nvarchar(150), @cursor varchar(25),@event_datetime varchar (100), @section_name varchar (100),@email_addr nvarchar(100),
@name varchar(100),@additional varchar (4000),@recipients varchar(25),@profile_name varchar (25), @num_seats nvarchar (100), @date_entered varchar(100), @fulfilledon varchar(100),@inet_event_description varchar(100),@other_info_3 varchar(100),@acct_id nvarchar (25)	
DECLARE UnVerifiedCursor CURSOR FOR 
--SELECT v_live_inventory.live_order_id,v_live_inventory.inet_event_description, v_live_inventory.section_name, 
--v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name, 
--AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
--FROM v_returned_inventory INNER JOIN
--v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND 
--v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
-- AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
--OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON 
-- AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
--GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description, 
-- v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name, 
--AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last
--HAVING (v_returned_inventory.class_name = 'INT_BIRTHDAY') 
Select @strSubject, @strBody, @other_info_3, @inet_event_description, @event_dateTime, @Section_name


--open cursor
OPEN UnVerifiedCursor
FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
WHILE @@FETCH_STATUS = 0
BEGIN	

BEGIN
SET @strBody = 'Birthday Suite Notification




Event Name:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_datetime,120) + SPACE(1) + '
Section:' + SPACE(1) + @section_name + SPACE(1) + '


'+'Order Information:' +'
--------------------------------------------------------------------------------
Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +' 
Order ID:' + SPACE(1) + @order_id + SPACE(1) +' 
Acct ID:' + SPACE(1) + @acct_id + SPACE(1) +'
--------------------------------------------------------------------------------'

SET @strSubject = 'BIRTHDAY SUITE '+ @event_datetime + SPACE(1)+' '

[!]Select @strSubject, @strBody, @other_info_3, @inet_event_description, @event_dateTime, @Section_name, @name, @email_addr, @order_id, @acct_id[/!]


-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
--EXEC msdb.dbo.sp_send_dbmail @strSubject,
-- @profile_name = 'Orders_at_TDGarden_profile',
--@recipients = 'bdmoran@dncboston.com',
--@Body = @strBody, 
--@Subject = @strSubject
END

FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
END
-- close cursor
CLOSE UnVerifiedCursor
DEALLOCATE UnVerifiedCursor

The line in red above is for debugging only. Once you have everything working, you;ll want to remove the red line and uncomment the actual send part.

Please run the code shown above. When you do, you should see a separate output for each email you send. Are any of the values returned NULL? If so, please let us know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I apologize. I hadn't noticed that you commented out the actual select statement at the beginning. This was probably based on my earlier advice, which was not clear enough to indicate what I wanted you to try.

Anyway, please run this instead of the query in my previous post.

Code: 
DECLARE @live_order_id nvarchar(10),@add_usr varchar (100),@cust_name_id nvarchar(10),@class_name nvarchar(100), @order_id nvarchar(10),@strSubject varchar(100),@file_attachments nvarchar (100), @strBody varchar(4000), @type tinyint, @bitmap char (6), @event_name nvarchar(150), @cursor varchar(25),@event_datetime varchar (100), @section_name varchar (100),@email_addr nvarchar(100),
@name varchar(100),@additional varchar (4000),@recipients varchar(25),@profile_name varchar (25), @num_seats nvarchar (100), @date_entered varchar(100), @fulfilledon varchar(100),@inet_event_description varchar(100),@other_info_3 varchar(100),@acct_id nvarchar (25)	
DECLARE UnVerifiedCursor CURSOR FOR 
SELECT v_live_inventory.live_order_id,v_live_inventory.inet_event_description, v_live_inventory.section_name, 
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name, 
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND 
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
 AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON 
 AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description, 
 v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name, 
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last
HAVING (v_returned_inventory.class_name = 'INT_BIRTHDAY') 

--open cursor
OPEN UnVerifiedCursor
FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
WHILE @@FETCH_STATUS = 0
BEGIN	

BEGIN
SET @strBody = 'Birthday Suite Notification




Event Name:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_datetime,120) + SPACE(1) + '
Section:' + SPACE(1) + @section_name + SPACE(1) + '


'+'Order Information:' +'
--------------------------------------------------------------------------------
Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +' 
Order ID:' + SPACE(1) + @order_id + SPACE(1) +' 
Acct ID:' + SPACE(1) + @acct_id + SPACE(1) +'
--------------------------------------------------------------------------------'

SET @strSubject = 'BIRTHDAY SUITE '+ @event_datetime + SPACE(1)+' '

[!]Select @strSubject, @strBody, @other_info_3, @inet_event_description, @event_dateTime, @Section_name, @name, @email_addr, @order_id, @acct_id[/!]


-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
--EXEC msdb.dbo.sp_send_dbmail @strSubject,
-- @profile_name = 'Orders_at_TDGarden_profile',
--@recipients = 'bdmoran@dncboston.com',
--@Body = @strBody, 
--@Subject = @strSubject
END

FETCH NEXT FROM UnVerifiedCursor INTO @strSubject, @strBody, @other_info_3, @inet_event_description,@event_dateTime,@section_name
END
-- close cursor
CLOSE UnVerifiedCursor
DEALLOCATE UnVerifiedCursor

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Alright I ran the lastestt query and It came back with no errors...
 
Upvote 0 Downvote
I posted twice. Did you run the most recent one? When you do, you should see a result set for each email it would send. Did you see the result set? Are there any NULL's returning from the result sets?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I ran the most recent one, but all I saw was a return value of 0. My co-worker updated some joins in my select statement. This may have something to do with it...

DECLARE @live_order_id nvarchar(10),@add_usr varchar (100),@cust_name_id nvarchar(10),@class_name nvarchar(100), @order_id nvarchar(10),@strSubject varchar(100),@file_attachments nvarchar (100), @strBody varchar(4000), @type tinyint, @bitmap char (6), @event_name nvarchar(150), @cursor varchar(25),@event_datetime varchar (100), @section_name varchar (100),@email_addr nvarchar(100),
@name varchar(100),@additional varchar (4000),@recipients varchar(25),@profile_name varchar (25), @num_seats nvarchar (100), @date_entered varchar(100), @fulfilledon varchar(100),@inet_event_description varchar(100),@other_info_3 varchar(100),@acct_id nvarchar (25)
DECLARE UnVerifiedCursor CURSOR FOR
SELECT v_live_inventory.live_order_id,v_live_inventory.inet_event_description, v_live_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description,
v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last
HAVING (v_returned_inventory.class_name = 'INT_BIRTHDAY')


Sorry for the confusion...this has been giving me trouble for a few days now !!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
376
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
359
pjw001
pjw001
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
239
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
220
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
261
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top