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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.