Using temporary objects in MSSQL Server with Access?

[Banana]

I am trying to find out if it's feasible to use temporary and local objects (in this specific case, a temporary local table) with Access being involved as front-end clients. I'm trying to do some data processing on server side, with Access feeding the inputs, which ultimately will return a recordset but this requires several steps along the way.

I know that Access may create more than one session during the application lifetime with the SQL Server, so the local temporary tables may go out of scope if it's left idle, for instance. Would it be remedied by holding a persistent ADO connection, or can session change even with the same connection object?

Thanks for any guidance.

 

[pbaldy]

I was hoping someone would answer that might have more experience with temporary objects, but I'll jump in. I'm a little confused as to exactly what you are referring to, and where. I'm assuming temp tables on the SQL Server side. I sometimes use temporary tables within SQL Server stored procedures, but those only live as long as the SP. This type of thing:

SELECT Blah
INTO #TempTableName
FROM Blah

SELECT Blah
FROM OtherTable LEFT JOIN #TempTableName ON >>>

with the final result passed back to Access. Is that the direction you're going in, or am I suffering the late afternoon brain cramps?

 

[Banana]

Yes, that's what I'm looking at, except that I want to see if I can get it to live between calls and/or stored procedures.

From what I'm reading, local temporary tables (e.g. #temptablename) lives as long as the session. The problem is I can't find a definitive answer of what defines one session in context of ADO connection. From profiler, it looks like it's same connection even if the ADO connection object go out of scope and is recalled, but I was hoping for something in writing supporting this.

Did that clear things up?

 

[pbaldy]

Yes, but I've got to head out to meet my wife for dinner. Look in BOL for

##TableName

instead of

#TableName

The double # has a different life, but I can't remember the details off the top of my head. I'll check back later if you're still on the prowl for an answer.

 

[Banana]

Paul,

Thank. The ## is global temporary, IINM, and yes it can live beyond, but is also visible to everyone, which I don't want.

I'm also looking at this article with suggestion of using the global temp table with process ID to keep the individual user's data separate.

 

[SQL_Hell]

Hi Banana

I have seen a similar method to the one used in your last link in a place I used to work, they used a slightly different method of creating the global temporary table with the user's SID as part of the table name.

Other methods you could use are:

1. table variable -not for large recordsets, unless you have loads of ram , still will have security issue.
2. Permanent table -requires manitenance, but permissions will be easier to control.


I guess the real question is why do you need to drop the connection? Can you not do all your processing in 1 stored procedure?

 

[Banana]

I could, and did, in fact. I was more curious to see if it's possible to offload the criteria asynchronously, so there would be less wait time while user fills in the forms, which is a multi-page tab control, by sending off the gathered information every time a user advance a page.

All of this would be done on Access and ADO, but I can't find any documentation that guarantee that ADO connection will stay alive the whole process, and hence my question to prepare in such contingency where the connection has to be regenerated and thus given different user ID.

I suppose one way would be at the start of connection, to retrieve the SID and hold it in Access and reference it throughout the process of building the recordset.

 

[SQL_Hell]

Sorry Banana I think my first post was misleading, I just want to clarify one point:

The SID never changes unless the login is dropped and recreated, it is the SPID (processID) that changes.
SQL server puts the SPID on the end of local temporary tables. *edited* if the connection is dropped then the temp table is dropped but a new SPID can be generated on the same connection
If you create a local temp table, you can see the actual name of the table by running the following:

USE TEMPDB

SELECT * FROM SYS.SYSOBJECTS

 

[Banana]

Even so, wouldn't the local temp table go out of the scope once the stored procedure has finished executing, even if the connection hasn't been dropped, thus warranting a global temporary table with SID as PK anyway?

 

[SQL_Hell]

Yes it will be out of scope because the subsequent request / transation will have a new processID, 1 connection can create multiple processIDs.

The global temporary table with SID as PK is still warranted due to the need for security.

 

[Banana]

Thought so.

Last question and I'm done. If I'm doing this asynchrously, I would have to check and make sure that the previous SP that did something to the SID-keyed global temp table is done before proceeding on next SP. Is there a means of verifying this?

(I'm thinking that ADO's built-in event for progress complete will do that, but wanted to make sure)

 

[SQL_Hell]

I am not sure how ado deals with it tbh, but if you use GO between your procedure calls then one will not start before the other has finished.

 

[Banana]

Just to be sure I understood it clearly, if I did this:

SP #1:

USE blah

GO

EXEC SP1 <parameter>

GO

then in next seperate call,

GO

EXEC SP2 <parameters>

GO

This will always execute serialized, and the second one will wait for the first to finish, even if the second one was called in parallel with the first?

If so, great!

 

[SQL_Hell]

I did wonder if you meant this, no GO will not do that, I thought you were calling them one after another in the same process.

But however SQL server standard locking behaviour should sort this one out for you, if the global temp table is being inserted into / updated or deleted then there will be an exclusive lock on the object at the piont where the data is being written to disk.

It could deadlock but unlikely IMO, most deadlocks are due to lock escalation (bad performance) in my experience

 

[Banana]

When you say exclusive lock, it scares me, because I usually think of it as table-level lock, rather than a row-level, and if it does, it would just suck because of concurrency issues that comes with it. Using SID pretty much guarantee that any other users won't be inserting/deleting/updating same rows used by someone else, so row-level locking should be sufficient, but it won't really solve the problem of calling SP #2 only when the SP #1 is done.

 

[SQL_Hell]

It's only at the point of physically writing to the disk, so we're talking miliseconds. You can see this behaviour by running

exec sp_lock

Look for lock modes of X

I think personally it would be better to have a different global temp table for each procedure then they will never come into contention, but that is probably in contrast to what you want to do.

Or

You could write a manual procedure log table with start date and end date, then do a check at the beggining of each procedure that there are no entries with start date populated and enddate null. It does depend on how long your procedure executes for as to whether this would work or not.

I don't know of any internal SQL server methods for checking there are no procedures still running.

 

[Banana]

The SPs would be dealing with same set of data per users so global temp for each sp won't work (and besides, I'd be using local temp table if that were the case). As said before, it's currently one big and long SP, and if the performance is an issue, I will definitely want to break that SP into smaller SPs and call them as the user moves through the criteria building pages.

I think I'll just use ADO's ExecuteComplete event to verify that a previous SP is done before executing the next SP if I do go down that route.

Thanks!!!