I have created a s3 bucket to store all files in it and I know that I can get a presigned url to share it with others. Now I want to generate a quick way to grant a particular email address these rights. My goal would be to have some kind of template to send out the email, ideally a SQL server script or procedure. and I have tried the script below:
DECLARE @EmailAddress NVARCHAR(255) = 'recipient@example.com';
DECLARE @S3Bucket NVARCHAR(255) = 'your-s3-bucket';
DECLARE @FileName NVARCHAR(255) = 'your-file.txt';
DECLARE @PresignedUrl NVARCHAR(MAX);
DECLARE @Command NVARCHAR(MAX) =
'aws s3 presign s3://' + @S3Bucket + '/' + @FileName + ' --expires-in 3600';
DECLARE @Output TABLE (OutputText NVARCHAR(MAX));
INSERT INTO @Output (OutputText)
EXEC xp_cmdshell @Command;
SELECT TOP 1 @PresignedUrl = OutputText FROM @Output WHERE OutputText LIKE 'http%';
DECLARE @EmailSubject NVARCHAR(255) = 'Access to Shared File';
DECLARE @EmailBody NVARCHAR(MAX) = '
<p>Hello,</p>
<p>Please click the link below to access the shared file:</p>
<p><a href="' + @PresignedUrl + '">' + @PresignedUrl + '</a></p>
<p>Regards,</p>
<p>my Name</p>
';
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailAddress,
@subject = @EmailSubject,
@body = @EmailBody,
`my text`@body_format = 'HTML';
but I get bunch of errors :
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
I couldn't solve this problem. Could you please help me because I am very beginner in databases.