System.InvalidOperationException: Invalid attempt to read when no data is present.

[Kantuz001]

Msg 6522, Level 16, State 1, Procedure dbo.clr_send_ics_invite, Line 0 [Batch Start Line 78]
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_send_ics_invite":
System.InvalidOperationException: Invalid attempt to read when no data is present.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.EnsureOnRow(String operationName)
at System.Data.SqlClient.SqlDataReaderSmi.GetSqlString(Int32 ordinal)
at StoredProcedures.clr_send_ics_invite(SqlString profile_name, SqlString recipients, SqlString copy_recipients, SqlString blind_copy_recipients, SqlString from_address, SqlString reply_to, SqlString subject, SqlString body, SqlString body_format, SqlString importance, SqlString sensitivity, SqlString file_attachments, SqlString location, SqlDateTime start_time_utc, SqlDateTime end_time_utc, SqlDateTime timestamp_utc, SqlString method, SqlInt32 sequence, SqlString prod_id, SqlBoolean use_reminder, SqlInt32 reminder_minutes, SqlBoolean require_rsvp, SqlString recipients_role, SqlString copy_recipients_role, SqlString blind_copy_recipients_role, SqlString smtp_servername, SqlInt32 port, SqlBoolean enable_ssl, SqlBoolean use_default_credentials, SqlString username, SqlString password, SqlBoolean suppress_info_messages, SqlString& event_identifier, SqlString& ics_contents)

[EitanBlumin]

Can you please give an example script / arguments to reproduce the problem?

[bgioldasis]

I receive the same error trying to send my first email. I used the script to create the assembly and the sps.

SampleScript

DECLARE @EventID nvarchar(255)

EXEC sp_send_calendar_event
@profile_name = 'SendGrid', --SendGrid is a valid profile name that i can send emails
@recipients = N'[email protected]', --ValidEmailaddress here
@subject = N'SQL Testing',
@Body = N'You are on-call this week!This is an automated message',
@body_format = N'HTML',
@location = N'Our offices',
@start_time_utc = '2020-02-02 00:00',
@end_time_utc = '2020-02-02 23:59',
@use_reminder = 1,
@reminder_minutes = 300,
@require_rsvp = 0,
@event_identifier = @EventID OUTPUT

SELECT EventID = @EventID

[EitanBlumin]

Everything seems to be okay with your T-SQL code.

Based on the error message, this seems to be an issue while getting DB Mail profile properties.

Please try running the following query and see what's returned:

SELECT TOP 1 a.email_address, a.replyto_address, s.servername, s.port, s.enable_ssl, s.use_default_credentials, s.username
FROM [msdb].[dbo].[sysmail_profile] AS p
INNER JOIN [msdb].[dbo].[sysmail_profileaccount] AS pa
ON p.profile_id = pa.profile_id
AND pa.sequence_number >= 1
INNER JOIN [msdb].[dbo].[sysmail_account] AS a
ON pa.account_id = a.account_id
INNER JOIN [msdb].[dbo].[sysmail_server] AS s
ON p.profile_id = s.account_id
WHERE p.name = 'SendGrid'
ORDER BY pa.sequence_number ASC

Also, you might have been using an outdated version of the script (it took me a while to update the T-SQL installation script after several improvements in the source code).

[EitanBlumin]

I'll be closing this issue due to inactivity.

[twells-fi]

Hi I am actually still having this issue. When I run the above SQL with my profile instead of sendgrid i get:

email_address	replyto_address	servername	                 port	enable_ssl	use_default_credentials	username
[email protected]                    outlook.office365,com	         25	        0	                0	[email protected]

I'm currently attempting to use version 2.0 because I couldn't get any of the versions after that to install correctly.

The relevant part of my script currently is:

exec sp_send_calendar_event
	@body_format = 'HTML',
	@importance = 'NORMAL', 
	@use_reminder =  1,
	@reminder_minutes = 10,
	@require_rsvp =  0,
	@smtp_servername = 'outlook.office365.com',
	@username = '[email protected]',
	@password =  'xxxxxxxxxx',
	@profile_name = 'xxxxx',
	@recipients = '[email protected]', 
	@body = @emailText,
	@subject = 'xxxxxx Notification',
	@blind_copy_recipients = '[email protected]'

[EitanBlumin]

Hi @twells-fi ,

Based on your output, it looks like your DBMail account is not configured properly.
The server name for office365 should be smtp.office365.com
The port should be 587
and SSL should be enabled

[EitanBlumin]

I created a follow-up issue which should take care of this one as well: #23 .