Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail Query Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 4:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
Regarding the @query parameter, BOL states:

"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."

I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.

Since I can't pass a local variable to @query, I'm doing something like:

@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC);
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'

While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?
Post #1472810
Posted Thursday, July 11, 2013 6:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
dan-572483 (7/11/2013)
Regarding the @query parameter, BOL states:

"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."

I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.

Since I can't pass a local variable to @query, I'm doing something like:

@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC);
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'

While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?


Why do you even need a variable local to the query?

@query = 'SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC)'




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1472834
Posted Friday, July 12, 2013 9:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
I am assuming that the SELECT TOP 1 Record_ID ..... ORDER BY MODIFYDATE DESC is going to return the record that was just modified. Is it safe to assume that if multiple users are doing the same operation at about the same time?

Post #1473093
Posted Friday, July 12, 2013 5:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
I would say that it improves the probability.

Any time you retrieve a record in one SQL statement that you want to be "current" and then use it in a second one, you leave open that infinitesimal instant of time where another query could sneak in and ruin your day.

If you can combine them, the probability is reduced but not rendered to zero. There is a locking hint you can look at: UPDLOCK. I've never used it in a situation exactly like yours. Usually I've used it to lock a record, to use the value on it to update another record in another table. Done within a transaction, the first query holds the lock on the table until the transaction ends.

I'm not sure if it is necessary for your case or not and it would probably depend on exactly how much volume of transactions you're dealing with.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1473267
Posted Monday, July 15, 2013 4:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
Just capture value of the RecordID that you just modified in the calling code, and pass it into the code to be dynamically executed:

@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = ''' + CAST(@RecordID_Updated AS varchar(10)) + '''
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'



SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1473927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse