May 21, 2008 at 10:55 am
Since sp_send_dbmail executes the query parameter in its own session, I'm currently "stealing" one row from a table with static data to hold a comparison data value while the query parameter executes. I create the row in the table then delete it at the end of the sp_send_dbmail task.
It has been suggested to me that I should use a global temp table (a local temp table will not work). I have some deep knee-jerk NO feelings about doing this, but I can't coherently form the argument.
What say y'all?
May 21, 2008 at 11:34 am
I am not sure a local temp table will not work, so you should try it. When the query is run, it is executed by an extended stored procedure that may be reconnecting with it's own spid - making the local temp table useless, but I am not sure if that it does not run within the calling process.
I would probably lean toward using a real table (not a global temp table) and a GUID to ensure you do not overlap concurrent processes. As another option, you could use a real table just like you use a temp table, explicitly perform a table lock on the table before you run the send mail procedure, then delete the data and unlock the table when you are done. This would make any other calls wait for the table to be available before they can continue.
May 22, 2008 at 8:10 am
Man, I didn't even make the "Database Pros Who Need Your Help!" cut :crying:
Allow me to rephrase.
Which statement is "more true", and why?
"I would rather spork my eyes out than use a global temp table"
or
"Global temp tables are fine, but using a single row of an existing table for a data swap is bad mojo"
May 22, 2008 at 8:36 am
Global temp tables are not the devil, but in your situation, I think they may be more trouble than they are worth. Since you are really trying to work around concurrency issues here, you are going to have to check if the global temp table already exists, if it does not, create it, but if it does, what do you do, wait? After waiting, you create it? At this point, a real table allowing you to use the SQL locking mechanism seems like a more logical approach to me.
I guess you could use sp_GetAppLock at the beginning of your procedure so it cannot be run by more than one connection at a time, but then why not bite the bullet and use a table. Again, if you use a GUID or some other kind of identifier, you could actually have concurrent executions with no wait and no conflict.
May 22, 2008 at 8:54 am
I don't think you can use a local temp table with sp_send_dbmail.
Create a global temp table using newid() to generate the table name, and use that.
declare @temp_table_name sysname
set @temp_table_name = '##'+replace(newid(),'-','')
exec ('
create table '+@temp_table_name+'
(
colls....
)
insert into '+@temp_table_name+'
select
....
exec sp_send_dbmail
...
')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply