Temporary Tables within SQL Server 2012 - length of time kept?

1) The temporary tables seem to be owned by "tempdb.."? Will we need to set the user that enquires upon these tables from java to be able to access these tempdb tables (not quite clear as this isn't obviously a standard user) if so how do we do it - if not do we just need to reference them as their full name tempdb..#temptab1?

I dont think you need to inquiry upon these table is their any specific requirement for that as temp table are used for temp calculations/storage. etc.. DBA normally keeps track of temp Db , its health etc...

2) Once the temporary table is written how long does it stay available to be queried upon? (I.E. Does it have to be within the same calling SessionID that calls the original query as I think within Java each SQL call generates a new SessionID connection - I may be wrong about this)

Temp tables are session specific.

If your query/Sp is creating the temp table it will be available to that session only. You cannot access that outside that session. one exception can be if your temp table is of Global type. Now global temp table can be accessible by other session till the session is available who creates it.

Ok thanks for these comments both of you. I think I will need to arrange then that these procedures be changed to write out to permanent tables and that I delete them once I have connected via a separate session or that they are deleted before a re-run of those procedures.

Thanks for your help. I will allocated points tomorrow as it is late here now.

Thx
Stephen

Welcome, happy to help !

If you need to create and update tables accessible by all users then do it in a scheduled job which is running under SQL Agent and which can do whatever you need independently on users connected to SQL Server. This is obvious way of cached results preparation when the calculation takes reasonable time but users need them frequently and almost immediately.

Ok thanks again for your comments - I am being given a bit more information about these procedures now. Although they are generating these Temporary Tables it seems that they are using SSRS to produce the output. Although I see no reference to this in the procedures other than the name of the procedure begin with "SSRS_". From my Java program I will only get the return Code (success or failure) so I'm assuming in fact that the data is being written out somehow as an equivalent to an Sysout.println() command to the FileSystem.

As you can tell - I've not used SSRS before so I will need to follow this through more. Have either of you had experience of this report writer - Is it simply written out to a set location from a variable specified somewhere - would you know how this operates? Is it in an CSV format etc.. etc..

No worries if not I'll still award points for your current assistance!! ;-)

Stephen

You can call a reporting services(SSRS) into your UI code, Are you consuming any rdl file into your code. SSRS report can be exported to csv, excel, pdf etc.
How you you using report writer. it must writing data in a file.

Ok thanks folk for all your input. I managed to get the full details from the user yesterday.

In fact they are using the temporary tables and calling from SSRS to run those Procedures and then exporting to Excel manually. What they want me to do is to call from Java and automatically write out the data to excel as required for one particular yearly job where they are having to do the process hundreds of times. I saw the FULL procedures yesterday and now understand the situation.

The problem when I ran these and got no resultset back was nothing to do with the temporary tables but to do with how SQL Server and Java interact. As the procedures have an int return_value this was hiding the true dataset return. I needed to add to the start of each procedure:-

    SET NOCOUNT ON

This then allowed Java to see the actual returned resultset and this solved my problem. I apologise that my understanding of the problem, because I was only advised in part, confused the issue. However I will duly award points as your help pointed the way to the solution.

One other point - Completely aide of this problem but may be useful for anyone reading this for help in the future - is that Java and the SQLException description of an error are a bit bad when it comes to declaration of Procedure Names (and presumably also Table Names).

If in your Java Program you declare the procedure with the square Brackets... i.e. [dbo].[<procname>].... Java will raise an SQLException stating it cannot find dbo.<procname> .. it does not say it cannot find  [dbo].[<procname>] which might have helped me - this kept me busy checking permissions and typos etc... for a good while before I clicked that it doesn't like me sending across [] delimited names!!!!!

Thanks again
Stephen

Thanks all