question

David 2 1 avatar image
David 2 1 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)
tsqlsql-server-2016send_db_mail
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
1 Like 1 ·
@Oleg many thanks. I knew I was doing something stupid. Apologies for the TOP 1 and the brackets. I should have removed these from my example as these are required in the main script. Sorry for any confusion. Thanks again and please have a great day. :)
0 Likes 0 ·
@Oleg if you'd like to be awarded the top answer please can you move your comment into an answer box. Thanks. :)
0 Likes 0 ·

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The code is part of a larger script, and yes it needs to be dynamic. I've simply reproduced the relevant part above that I need to get working.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.