SP_Send_dbmail is failing

I get this error when trying to email my results:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: ¿Msg 4060, Level 11, State 1, Server CANNWDAPPSRS01\DEVELOPMENT, Line 1
Cannot open database "Users_Master" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server CANNWDAPPSRS01\DEVELOPMENT, Line 1
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
--declare all variables used
declare @warningdate date
declare @daysLeft varchar (50)
declare @user varchar(50) 
 
 

--set the warning date variable. This says if its less than 30 days away
 SET @warningdate =   getdate() +50
--SELECT @warningdate




DECLARE c1 CURSOR READ_ONLY
FOR
(SELECT 
      [usedBy34] 
      
  FROM [AssetManagement].[dbo].[secureToken]
  
  where  [DateDeath]  < @warningdate )


OPEN c1

FETCH NEXT FROM c1
INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
	
  
 select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken
 where usedBy34 = @user) as expiresDate from dbo.Users_Master 
 where
dbo.Users_Master.user34 = @user
 




	FETCH NEXT FROM c1
	INTO @user

	

END


 ---strictly the email portion of the script.    
        IF @user is not NULL   
Begin  
declare @sql varchar(1000) 
--set @sql =  'select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user, '''', '''''') + ''' ' 
 set @sql ='select * from dbo.secureToken'
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AssetManage',
@recipients = 'darryl.emily@scresearch.net', --- 'dup_ran@kswiring.com' 
@query = @sql, 
@subject = 'There are secure Tokens that are about to expire', 
@body = 'The following tokens will expire:', 
@query_result_separator='    ', 
@attach_query_result_as_file = 'TRUE', 
@query_result_width=400 ,
@execute_query_database = Users_Master


End  
------ End of the emai section.

Open in new window

dboyd02Asked:
Who is Participating?
 
dboyd02Author Commented:
Experts I think I am close. I have moved the email portion of this procedure. Now I get an email for every record. 23 emails.
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
--declare all variables used
declare @warningdate date
declare @daysLeft varchar (50)
declare @user varchar(50) 
declare @allexpiring varchar(1150)
 
 

--set the warning date variable. This says if its less than 30 days away
 SET @warningdate =   getdate() +50
--SELECT @warningdate




DECLARE c1 CURSOR READ_ONLY
FOR
(SELECT 
      [usedBy34] 
      
  FROM [AssetManagement].[dbo].[secureToken]
  
  where  [DateDeath]  < @warningdate )


OPEN c1

FETCH NEXT FROM c1
INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
	
  
 select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken
 where usedBy34 = @user) as expiresDate from dbo.Users_Master 
 where
dbo.Users_Master.user34 = @user

 




	FETCH NEXT FROM c1
	INTO @user


 ---strictly the email portion of the script.    
        IF @user is not NULL   
Begin  
declare @sql varchar(1000) 
set @sql =  'select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user, '''', '''''') + ''' ' 
 
EXEC msdb.dbo.sp_send_dbmail @recipients = 'darryl.emily@scresearch.net', --- 'dup_ran@kswiring.com' 
@query = @sql, 
@subject = 'There are secure Tokens that are about to expire', 
@body = 'The following tokens will expire:', 
@query_result_separator='    ', 
@attach_query_result_as_file = 'TRUE', 
@query_result_width=200,
@execute_query_database= AssetManagement
End  
------ End of the emai section. 


	

END





CLOSE c1
DEALLOCATE c1

End

Open in new window

0
 
dboyd02Author Commented:
Well it is the select statement in the @query because when I remove this code the email goes through fine. Here is the select statement that I need to get the results I have to have.

The second set of code is this code in the @query statement:


set @sql =  'select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user, '''', '''''') + ''' '


select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken
 where usedBy34 = @user) as expiresDate from dbo.Users_Master 
 where
dbo.Users_Master.user34 = @user

Open in new window

0
 
dboyd02Author Commented:
Okay here is an update. I got the email working with the select statement. This issue is I am only getting one record when the select in the upper part of the script pulls like 23 records.
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
--declare all variables used
declare @warningdate date
declare @daysLeft varchar (50)
declare @user varchar(50) 
 
 

--set the warning date variable. This says if its less than 30 days away
 SET @warningdate =   getdate() +50
--SELECT @warningdate




DECLARE c1 CURSOR READ_ONLY
FOR
(SELECT 
      [usedBy34] 
      
  FROM [AssetManagement].[dbo].[secureToken]
  
  where  [DateDeath]  < @warningdate )


OPEN c1

FETCH NEXT FROM c1
INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
	
  
 select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken
 where usedBy34 = @user) as expiresDate from dbo.Users_Master 
 where
dbo.Users_Master.user34 = @user

 




	FETCH NEXT FROM c1
	INTO @user

	

END


 ---strictly the email portion of the script.    
        IF @user is not NULL   
Begin  
declare @sql varchar(1000) 
set @sql =  'select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user, '''', '''''') + ''' ' 
 
EXEC msdb.dbo.sp_send_dbmail @recipients = 'darryl.emily@scresearch.net', --- 'dup_ran@kswiring.com' 
@query = @sql, 
@subject = 'There are secure Tokens that are about to expire', 
@body = 'The following tokens will expire:', 
@query_result_separator='    ', 
@attach_query_result_as_file = 'TRUE', 
@query_result_width=400,
@execute_query_database= AssetManagement
End  
------ End of the emai section. 


CLOSE c1
DEALLOCATE c1

End

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Hi,

Think you left something behind in another thread :)

The code below is what you are looking for...

create table ##tokens  (user34 nvarchar(200), DateDeath date)
 
declare @user34 nvarchar(200), @dateDeath nvarchar(200)
declare @sql nvarchar(2000)
declare @warningdate date
declare @expiresdate date
 
 
 
--set the warning date variable. This says if its less than 50 days away
 SET @warningdate =   getdate() +50
--SELECT @warningdate
 
 
declare c cursor for (SELECT  
      [usedBy34]  
       
  FROM [AssetManagement].[dbo].[secureToken]
   
  where  [DateDeath]  < @warningdate )
open c  
 
 
 
fetch next from c into @user34
while @@fetch_status = 0  
begin  
    set @dateDeath = (select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = @user34)
      set @sql =  'if exists (select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user34, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user34, '''', '''''') + ''')  
      insert into ##tokens values ('''+@user34+''','''+@dateDeath+''')'
       
print @sql
      exec (@sql)
 
fetch next from c into @user34
end  
 
 
 
 
close c
deallocate c
go  
 
select * from ##tokens
 
 
--drop table ##tokens
 
----email script
        --IF @user34 is not NULL    
Begin  
declare @sql2 varchar(1000)  
set @sql2 =  'select * from ##tokens'  
 
EXEC msdb.dbo.sp_send_dbmail @recipients = 'darryl.emily@scresearch.net', --- 'dup_ran@kswiring.com'  
@query = @sql2,  
@subject = 'There are secure Tokens that are about to expire',  
@body = 'The following tokens will expire:',  
@query_result_separator='    ',  
@attach_query_result_as_file = 'TRUE',  
@query_result_width=200
 
End  
----END email script
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.