Temporary table life span in a job

[John J. Hughes II]

I understand that temporary table start with # are session specific and ##
are global. I also understand the temporary tables declared in a stored
procedure are dropped at the end of the stored procedure.

Now I have a job that declares a temporary table would that table fall in
the session specific category or the stored procedure category assuming It
was named starting with #?

The reason I ask is I am concerned that if the job does not complete
normally and drop the table specifically then I might have a table floating
around forever. Or another way of putting is I am not sure if when a job is
started if its considered a session or when the server agent starts.

I would prefer not to have the function in a stored procedure.

Regard,
John

[Jerry Spivey]

John,

Why wouldn't you want to put this code in a stored procedure and schedule
the sproc execution as a job step? Also, I'm inclined to include a DROP
TABLE statement in either case for code completeness.

HTH

Jerry
"John J. Hughes II" <n...@invalid.com> wrote in message
news:%23yRJ2RN...@TK2MSFTNGP12.phx.gbl...

[John J. Hughes II]

Jerry,

I have had problems with people running the stored procedures and since I
can't control who has access to. What I have found job are better hidden.
If I have no choice it will end up there but I was trying to avoid it.

Yes I have a drop table but unfortunately its not called on error.

I was thinking of making the job three steps. The first step would create
the temp table, second step to use the table, and finally the third step
would drop it. If I set the first step to stop on failure and the second
step to run the last step on failure the table should always be dropped.
Still working on it.

Regards,
John

"Jerry Spivey" <jsp...@vestas-awt.com> wrote in message
news:euoPfhN2...@tk2msftngp13.phx.gbl...

[Tibor Karaszi]

Session specific. Agent is a client just as QA, for instance, is. the table is removed at end of job
step (as the connection ends with the job step). No need to worry it floating around, Agent opens
and closes new connections for the job steps.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"John J. Hughes II" <n...@invalid.com> wrote in message
news:%23yRJ2RN...@TK2MSFTNGP12.phx.gbl...

[John J. Hughes II]

Thanks for the response, very helpful.

Regards,
John