0

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.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • I'm not an SQL Server person, but the error is saying that it expects the command to be `varchar`, but your code appears to be using an `NVARCHAR`. – John Rotenstein May 18 '23 at 01:33

0 Answers0