| Author |
Message |
ahsansheraz782
Newbie
Joined: 21 Apr 2014
Location: Saudi Arabia
Online Status: Offline
Posts: 2
|
   Topic: MSSQL Temp Tables in Multi-user environ inCR-11.5
Posted: 22 Apr 2014 at 8:24am |
|
Dear Friends
I have a problem in my vb application, I always working with temp tables because my queries are very heavy, and you know temp tables always generated with the path of user id. On design time you have to give path of your tables, works well, but on live it doesn't. WHY... because other user generate this temp table, source was changed result NO OUTPUT and ERROR as well.
Any solution of this problem.
THANKS IN ADVANCE.
Edited by ahsansheraz782 - 22 Apr 2014 at 8:31am
|
|
Ahs on net
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 23 Apr 2014 at 5:18am |
|
if you use a stored procedure to populate and retrieve from the temp table, then life should be fine (done it for years).
if by temp table, you are referring to a physical table that is populated by users, then you are probably out of luck...though what you could do is in the stored proc create a unique table name, populate it, manipulate/select from it, then drop it. Probably will cause the log file to expand greatly as well as eat up disk space if it is a large temp table and many users are creating/using it.
Otherwise, since a temp table is only visible/exist for the session, no other user or process should be able to clear it out...so I guess it comes down to what type of table you are using and calling a temp table:
create table #temp --temp
or
create table permanentTemp --not temp
or
create table ##globalTemp --temp, but other sessions/users can see them, so not quite as temp as you might assume as a truncate table ##globalTemp would clear the values for all processes that access the table.
|
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 23 Apr 2014 at 9:30am |
|
I'm kind of new to SQL, so this confuses me...
Why would people who work with the same database use different user id's? They should be given the same user id, but different passwords.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 23 Apr 2014 at 9:33am |
|
userIDs are part of security, and like a desktop, only 1 password goes with 1 id.
the same user can create multiple sessions which are all independent...but I think that they get tagged by SQL as being a different ID so that the sessions variables and temp objects don't interfere with each other
|
|
IP Logged |
|
ahsansheraz782
Newbie
Joined: 21 Apr 2014
Location: Saudi Arabia
Online Status: Offline
Posts: 2
|
Posted: 26 Apr 2014 at 8:35pm |
|
Dear Brothers.... Thanks very much for your detailed Help. Since Last 12 years I am doing as follows :-
All the users are logged in by sa.
When request of any report generation occurs, I search the temp table generated and available in db dictionary if found, A delay for 10 sec will be initiated so other user fetch the data from existing table, then it is deleted auto. otherwise road is open. I did this thing successfully till now. But Now I want my app to be deployed on CLOUD. thats why I am bit curious about this. Please suggest me in this regard and see the code which i am using to create delay between two processes.
According to your suggestions, I have almost 70 reports in my app, every reports have atleast one temp table. It is very difficult to write the fixed table structures for almost all the reports.
Thanks in adv.
PR_TempTables.Requery
If MySeek(ls_TableName, "Name", PR_TempTables) Then
If Lb_DelTable Then
Gc_dbcon.Execute ("Drop Table " & ls_TableName & ";")
Else
ln_time = Time
Ln_Delay = 0.0000116 * 60
Do While MySeek(ls_TableName, "Name", PR_TempTables) And Time < (ln_time + Ln_Delay)
PR_TempTables.Requery
Loop
PR_TempTables.Requery
If MySeek(ls_TableName, "Name", PR_TempTables) Then
Gc_dbcon.Execute ("Drop Table " & ls_TableName & ";")
End If
End If
End If
Edited by ahsansheraz782 - 26 Apr 2014 at 8:44pm
|
|
Ahs on net
|
|
IP Logged |
|
|
|
