SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run stored procedure when temp table is created/dropped


Run stored procedure when temp table is created/dropped

Author
Message
jo.wouters
jo.wouters
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 67
Hi guys and girls,

I need to update 1 or 2 records in my database each time a temp table with the name like '##ABC_%' is created/dropped.
(Note: Temp tables are created from my application.)
I can do this in my application (VB.NET), but I didn't wrote it and the guy before me didn't understand multi-tier/OO.
So I want to move the update part to the SQL Server (if possible).
Is there a way in SQL2005 to detect the creation/deleting of a temp table and then run a SP?

All ideas/thoughts are welcome.

Grtz
Jo
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40300 Visits: 14413
I checked to be sure and DDL triggers FOR CREATE_TABLE do not fire when a global temp table is created. I do not know of way to accomplish what you're after.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Rob Schripsema
Rob Schripsema
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3219 Visits: 11042
You could do this with SQL Server Integration Services (SSIS) running on a tight schedule (every few minutes?) or with SSIS and the FileWatcher task (available from www.sqlis.com), which will detect changes to folders on the server.

Edit: Sorry, I may have mis-read your post. You're looking at temp tables, not files dropped to a folder. My solution probably won't solve your problem then.

Rob Schripsema
Propack, Inc.
jo.wouters
jo.wouters
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 67
Thanks for the replies.

Does any one else have an idea how to solve this?

grtz
Jo
Rob Schripsema
Rob Schripsema
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3219 Visits: 11042
I did explore this further yesterday and wondered about your apps architecture. You could create these global temp files (##ABC) and look through the tempdb.sys.tables table to locate them, but they will only exist for as long as there exists a connection that references them. So, if your VB app creates them, writes to them and then disappears (dropping the connection), the file(s) will be dropped also.

But, if the VB app is continously running and keeping the SQL connection open, you could create a 'polling' job that would execute every N minutes/seconds that would query tempdb.sys.tables for entries for temp file names that match your pattern. So, your update process wouldn't be 'triggered' by an event, but would be 'discovered' by a process that goes out and checks (polls) for data.

Then you'd probably have to use some dynamic SQL to actually query those temp tables to get the data out of them for your update process.

Not sure I'd agree that this is the most robust architecture, but I suppose it depends on your system and your situation.

Rob Schripsema
Propack, Inc.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73019 Visits: 40959
i am wondering if you could put an extended event for CREATE_TABLE on the tempdb?

I'll grab one of my sample extended events and try it, but it's just an idea for now;

i'd think that the extended event would be destroyed on start/stop of the server, so you'd need something that keeps adding it back, if an extended event is even allowed.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40300 Visits: 14413
Lowell (1/22/2013)
i am wondering if you could put an extended event for CREATE_TABLE on the tempdb?

I thought of that too, but it's SQL 2005.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Your Name Here
Your Name Here
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 830
Hm. Maybe using SQL Profiler? Trap all "CREATE TABLE #" commands? ...just a thought...
jo.wouters
jo.wouters
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 67
Hi,

It seams to be not that easy/possible to catch this at server-side.
I'm going to dig into the code and seach for those temp tables (and centralize it :-)).

Thanks for the help on this topic.

grtz
Jo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search