Run stored procedure when temp table is created/dropped

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » Run stored procedure when temp table is...

Run stored procedure when temp table is created/dropped

Rate Topic

Display Mode

Topic Options

Author

Message

jo.wouters

Posted Monday, January 21, 2013 4:33 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 4:05 AM
Points: 14, 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

Post #1409477

opc.three

Posted Monday, January 21, 2013 10:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 6,696, Visits: 11,713

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1409643

Rob Schripsema

Posted Monday, January 21, 2013 12:20 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 1,632, Visits: 10,850

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
Accelitec, Inc

Post #1409675

jo.wouters

Posted Tuesday, January 22, 2013 1:43 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 4:05 AM
Points: 14, Visits: 67

Thanks for the replies.

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

grtz
Jo

Post #1409863

Rob Schripsema

Posted Tuesday, January 22, 2013 11:27 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 1,632, Visits: 10,850

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
Accelitec, Inc

Post #1410191

Lowell

Posted Tuesday, January 22, 2013 12:38 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,605, Visits: 27,647

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1410228

opc.three

Posted Tuesday, January 22, 2013 1:56 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 6,696, Visits: 11,713

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1410262

Your Name Here

Posted Wednesday, January 23, 2013 8:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 130, Visits: 510

Hm. Maybe using SQL Profiler? Trap all "CREATE TABLE #" commands? ...just a thought...

Post #1410643

jo.wouters

Posted Monday, January 28, 2013 8:44 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 4:05 AM
Points: 14, 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 Smile

).

Thanks for the help on this topic.

grtz
Jo

Post #1412479

« Prev Topic | Next Topic »

Permissions