asked
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 = '[1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890]' SELECT @MyEmail = 'msdb.dbo.sp_send_dbmail @profile_name=''' + @ProfileName + ''', @recipients =
''dba@acme.com'', @subject=''Test'', @attach_query_result_as_file = 0, @importance =''High'', @body=' + @Message1 EXEC(@MyEmail)
@David 2 1 Why do you have the brackets around the value of the message1 variable? Why do you need top 1? Even if all that is actually OK, you still need to add a single quote on both ends of the message. Otherwise, the parser assumes that you are passing some sort of object name as the value of the body parameter, and because any object name is actually sysname type, it cannot be more than 128 characters long. To cure your problem, please modify the line reading @body=' + @Message1 to read @body=''' + @Message1 + ''''; This way, the value of @MyEmail will no longer include any sysname identifiers and so you can execute it.
why dynamic SQL is to be used? Why it can't be simply exec msdb.dbo.sp_send_dbmail @profile_name= @ProfileName , @recipients =
'dba@acme.com', @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.
19 People are following this question.
