How To Resolve 'The identifier is too long. Maximum length is 128'
Hi there, I am trying to get the below string to email however I keep receiving the 'identifier is too long. Maximum length is 128' error message. Using PRINT I can output Message1 however the string will not email successfully due to its length. Can anyone advise? TIA DECLARE @MyEmail VARCHAR(MAX) DECLARE @ProfileName VARCHAR(25) = 'DBA' DECLARE @Message1 VARCHAR(MAX) SELECT TOP 1 @Message1 = '' SELECT @MyEmail = 'msdb.dbo.sp_send_dbmail @profile_name=''' + @ProfileName + ''', @recipients =
''email@example.com'', @subject=''Test'', @attach_query_result_as_file = 0, @importance =''High'', @body=' + @Message1 EXEC(@MyEmail)
why dynamic SQL is to be used? Why it can't be simply exec msdb.dbo.sp_send_dbmail @profile_name= @ProfileName , @recipients =
'firstname.lastname@example.org', @subject='Test', @attach_query_result_as_file = 0, @importance ='High', @body= @Message1 If you want to use dynamic SQL for some reason, then `sp_executesql` would have have served better? Anyhow if you want to carry on with this code as it is then you have to quote the parameter `@body` just like you did for `@ProfileName`. Cheers.