How does this capture multiple values from a cursor, and put them into an email message?
DECLARE @SINGLE_RETURN char(2) = ISNULL(char(13), '') + ISNULL(char(10), '')
(here are statements of a cursor that finds unencrypted databases)
(then, EACH database name is put into a single message, (before I was sending an email with each cursor/database by having the message statement inside the cursor). I see how one email with multiple databases is much cleaner - not understanding how this works).
SET @msg += 'Encryption was found disabled and then enabled on database [' + @database_name + ']' + @SINGLE_RETURN
December 31, 2024 at 9:03 pm
If you look up CURSOR on the MSFT site, you'll find examples.
Once you open a cursor (a recordset in Access-speak), you have to loop through it, and once inside the cursor, you can loop over the records and do something with each one.
see sp_send_db_mail stored procedure.
December 31, 2024 at 9:37 pm
Your question isn't clear - and the code to create @SINGLE_RETURN is really confusing. Why wrap a non-null CHAR(13) function with ISNULL to return an empty string when CHAR(13) will never be null?
Definitely need the context of where @msg is being set, but I suspect it is being set inside the loop (cursor). The operator '+=' appends the string to the variable, so each loop will append additional data to that variable.
The variable @SINGLE_RETURN appears to be creating a CRLF (carriage return line feed). Depending on where this variable is being used - you probably don't need a full CRLF and probably only need a LF. Either way - each time the string is appended that CRLF is adding a new line of text.
Also - instead of hard-coding the braces - you can use QUOTENAME. ' ... database ' + quotename(@database_name) + char(13) + char(10)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 1, 2025 at 6:37 pm
Maybe this is enough info without the whole cursor;
DECLARE @SINGLE_RETURN char(2) = ISNULL(char(13), '') + ISNULL(char(10), '')
Inside the cursor:
SET @msg += 'Encryption was found disabled and then enabled on database [' + @database_name + ']' + @SINGLE_RETURN
Outside the cursor:
IF @msg <>'' EXECUTE msdb.dbo.sp_notify_operator @profile_name=N'ABC',@name=N'ABC',@subject=@msgSubject ,@body=@msg
To clarify my question; How does the @SINGLE_RETURN cause the messages to accumulate? I was sending one message as each cursor processed, but my co-worker re-wrote it to accumulate the databases, then send one message at the end. Is this becuase the variable is defines as a CHAR(2) so it just collects each database name with each processed cursor?
I'm not clear on why, for the message part, seems to store each database name, then sends the email outside the cursor at the end, with all the databases it encrypted(?) in a single email. For database encryption cursor that I wrote (not the above change), it finds one database at a time that's not encrypted, creates a cert one at a time, encrypts the database one at a time, sends an email for each one, one at a time, then it moves through again and does the same thing until all databases are encrypted (for the ones that were not encrypted). However, what was re-written (above), and I'm not clear how it's working to send all the database names at once. It's clear the one message is becuase it's outside the cursor, but how does it retain each database to put in the message?
Steve
... How does the @SINGLE_RETURN cause the messages to accumulate? ...
Steve
It doesn't. It's the "+=" that causes the msgs to "accumulate" (concatenate).
For example, if you do this:
DECLARE @msg varchar(max);
DECLARE @SINGLE_RETURN char(2) = CHAR(13) + CHAR(10);
SET @msg = '';
SET @msg += 'abc' + @SINGLE_RETURN;
SET @msg += 'def' + @SINGLE_RETURN;
You can see that @msg contains both 'abc' and 'def'.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 2, 2025 at 12:58 am
I see. So the @SINGLE_RETURN variable is just for formating/separating the messages (which is what I thought might be concactenating the strings.). Thanks for clarifying.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply