How to work with temp tables (##tablename) on MS SQL and unigui Sessions

[irigsoft]

Hi, I have a working app moved from a desktop app to a standalone unigui app.

in my desktop application i create a temporary table in MS SQL and this table is used to write some data in it, it is shared by different users and terminals.

Each user has their own terminal ID and can store data in this temporary database

My problem with unigui is that when some user starts the session, it creates the temp table (if not already created by another user) and he can store data in it.

At that time, another user logs into an application with a different terminal ID and operator name and tries to create the temporary table (if it doesn't exist) and then he too can store his own data in it.

Problem: if some of the users close their session, then this action removes the temporary table and all other users can no longer store their data !!!

How do I disable this?

Maybe the problem is not related to unigui, but any help would be great, thanks

[Sherzod]

Hello,

2 hours ago, irigsoft said:

when some user starts the session, it creates the temp table (if not already created by another user)

Your case is not clear. 

[irigsoft]

37 minutes ago, Sherzod said:

Hello,

Your case is not clear. 

ОК, 

When every user login into my application , application check and create, if not exist this temp table (temp..##tablename).

this is my sql script:

IF OBJECT_ID ('tempdb..##WorkCard') IS NULL begin
    CREATE TABLE tempdb..##WorkCard (
        ArtID numeric (18,2), Kol real, Price real, SN varchar (150)
        , OperName varchar (70), StoreID varchar (100), TermID smallInt
    ) 
END;

SELECT ##WorkCard.* FROM ##WorkCard

When this table is created every user can add data on it.

Problem: With this application work 2 users (or more). When one of users close session (or session expires) this temp table is deleted and all other users get message from MS SQL "##tablename is not exist".

Is it possible that this temporary table is not destroyed when the session is closed?

[GoldLine]

Hi irigsoft,
the local temp table is visible only to the connection/session that creates it and is deleted when the connection/session is closed. You should work with GLOBAL TEMP TABLES.  

GLOBAL TEMPRORY TABLE SCOPE -

The global temporary table is automatically dropped when the session that created the table ends and the last active Transact-SQL statement (not session) referencing this table in other sessions end.

Global temp tables are visible to everyone and are deleted when all connections that have referenced them have closed.

Global temp table Advantage -

A flexible solution, no need to change applications, data are cleaned automatically.

 

 

[Skyp]

On 9/12/2023 at 1:33 PM, irigsoft said:

ОК, 

When every user login into my application , application check and create, if not exist this temp table (temp..##tablename).

this is my sql script:

IF OBJECT_ID ('tempdb..##WorkCard') IS NULL begin
    CREATE TABLE tempdb..##WorkCard (
        ArtID numeric (18,2), Kol real, Price real, SN varchar (150)
        , OperName varchar (70), StoreID varchar (100), TermID smallInt
    ) 
END;

SELECT ##WorkCard.* FROM ##WorkCard

When this table is created every user can add data on it.

Problem: With this application work 2 users (or more). When one of users close session (or session expires) this temp table is deleted and all other users get message from MS SQL "##tablename is not exist".

Is it possible that this temporary table is not destroyed when the session is closed?

Привет. Странный запрос...

Если Вам постоянно нужна эта таблица, то почему не использовать постоянную сущность?

[irigsoft]

4 hours ago, Skyp said:

Привет. Странный запрос...

Если Вам постоянно нужна эта таблица, то почему не использовать постоянную сущность?

Yes, I use it like permanent table. Thanks