Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Temporary table disappearing

Author  Topic 

Mar
Starting Member

47 Posts

Posted - 2011-11-11 : 13:48:38
Hello.

I just want to first point out that I do not like the procedure that we have in place, but my protests have not helped at all.

We currently monitor change tracking by inserting the new record into a global temporary table. Then a SP is run to copy the record from the base table into a changes table. Then the base table is updated.

We do not get the benefit of a prepared statement by using an insert statement nor can I see any use of the temporary table except to consume resources and slow the process down. The permissions are the same for all tables so that point is moot. If anyone knows any other reasons why this procedure is poor please state them so I can use them in my next argument.

The problem is that the temporary table disappears once in a while before the insert. Most of the time it works.

I shortened the field list for clarity.

Here is the code (imsDataServices is a reference to a third party data manager that I steal the connection from):
        If CreateTempTableRI_Note(cmd) Then
If InsertIntoTempTableRI_Note(cmd, SaveType, RI_Note) Then
'cmd = New SqlCommand(gconSPRotair_RI_Note, New imsDataServices().Connection)
cmd.CommandText = gconSPRotair_RI_Note
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Clear()
Parameter = cmd.Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 100)
Parameter.Direction = ParameterDirection.Output
Parameter = cmd.Parameters.Add("@ReturnCode", SqlDbType.Int)
Parameter.Direction = ParameterDirection.Output
mintSPRC = cmd.ExecuteNonQuery
If mintSPRC = -1 Then
If Val(cmd.Parameters(1).Value) = 0 Then
Return True
Else
Return False
End If
Else
mstrLastError = cmd.Parameters(0).Value.ToString
Return False
End If
End If
End If




Here is CreateTempTableRI_Note:
        cmd = New SqlCommand("SET NOCOUNT ON", New imsDataServices().Connection)
'cmd = New SqlCommand
'cmd.Connection = New imsDataServices().Connection
If Not TempTableExists(cmd.Connection, gconTempTableRI_Note) Then

intRC = 0
strSQL = "SELECT " & _
[String].Format("{0}", _
gcontblRI_Note_DeleteBy) & _
" INTO " & gconTempTableRI_Note & _
" FROM " & gconTableRI_Note & _
" WHERE 0 = 1"

Else

intRC = -1
strSQL = "TRUNCATE TABLE " & gconTempTableRI_Note

End If

cmd.CommandText = strSQL
mintSPRC = cmd.ExecuteNonQuery

If mintSPRC = intRC Then
Return True
Else
Return False
End If




Here is InsertIntoTempTableRI_Note:
        strSQL = "INSERT INTO " & gconTempTableRI_Note & _
" (" & [String].Format("{0}", _
gcontblRI_Note_DeleteBy,) & ")" & _
" VALUES (" & gconParameter_Note_DeleteBy & ")"

cmd.CommandText = strSQL
With RI_Note
cmd.Parameters.AddWithValue(gconParameter_Note_DeleteBy, IIf(.Field("DeleteBy") = vbNullString, DBNull.Value, .Field("DeleteBy")))

End With

If cmd.ExecuteNonQuery > 0 Then
Return True
Else
Return False
End If


Any assistance is appreciated.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-12 : 10:01:48
>> We do not get the benefit of a prepared statement by using an insert statement nor can I see any use of the temporary table except to consume resources and slow the process down.

I suspect none of that is really important. Resources and speed shouldn't really be an issue.
More important is the functionality. Why is it thougt necessary to use a global temp table? Is this actually a global temp table or just a temp table?
If a global I suspect it's used because someone thought that it would be available for a query run on another connection. Problem is that it will be dropped as soon as the creating connection is dropped - I suspect the issue is that a second instance is seeing that the table exists, doesn't create and the creating connection is closed and the second connection finds the table has been dropped before it can be used.

Why isn't it a permament table - doesn't seem to be any reason not to be one as it is used as a staging table so the database must be set up. Looks like it is trying to be generic by table but that isn't going to work
It's best to keep things simple if you don't understand how things work and that seems to be the problem - needless complication.
All communication with the server should be by stored procedures and that will prevent attempts at fragile processes like this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Mar
Starting Member

47 Posts

Posted - 2011-11-14 : 09:12:36
quote:
Originally posted by nigelrivett

Is this actually a global temp table or just a temp table?


It is a temporary table. You indicate a global temporary table by prefixing the name with two pound signs ("##").



quote:
Originally posted by nigelrivett

If a global I suspect it's used because someone thought that it would be available for a query run on another connection. Problem is that it will be dropped as soon as the creating connection is dropped - I suspect the issue is that a second instance is seeing that the table exists, doesn't create and the creating connection is closed and the second connection finds the table has been dropped before it can be used.


There is no other connection using the table. What you see is what you got. The above code is the entirety of the functionality of the temp table with lists of field names removed for clarity. In the process the temp table occasionally disappears. There is no need to invent extra complexity. That would not get anyone anywhere.



quote:
Originally posted by nigelrivett

Why isn't it a permament table - doesn't seem to be any reason not to be one as it is used as a staging table so the database must be set up. Looks like it is trying to be generic by table but that isn't going to work


It can not be a permanent table because that could cause contention. Suppose two different users tried to update the same table at the same time both using the same staging table? There is just no reason to consider a possibility like that, avoid it in the first place. Having a separate table for each user solves this.



quote:
Originally posted by nigelrivett



It's best to keep things simple if you don't understand how things work and that seems to be the problem - needless complication.
All communication with the server should be by stored procedures and that will prevent attempts at fragile processes like this.



If you would read the first line in my post again:
quote:
Originally posted by Mar
I just want to first point out that I do not like the procedure that we have in place, but my protests have not helped at all.

Apparently you did not understand the point I was trying to make. I guess one sentence was not enough, so I will provide more.

1) I do not like the procedure we have in place.
2) I would never implement the procedure we have in place.
3) I must use the procedure we have in place.
4) The procedure we have in place broke when moved from Access to SQL server.
5) My superiors wish to use the current procedure with out changing it.
6) I must try everything to make the current procedure work.
7) If it is impossible I will get to change it, however this will greatly slow the upgrade and I would prefer to improve the situation after the upgrade is complete.



I may have no choice as all the searches I have done do not supply an answer and no one here has a clue as to why this happens. Someone on another forum says that ADO.NET can open a second connection behind my back sometimes. I can not find any documentation to confirm or deny this, but that would explain the problem. I will try wrapping the process in a transaction to see if that helps.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-14 : 09:21:51
That was my suspicion (about the mutiple connections from the app).
Seems that you are happier on your own though.

You seem to now think it is a temp table but say it is created with ## - which would make it a global temp table which oyu originally stated. These are very different and the writer of this could have been trying to solve an issue. Anyway it wouldn't act as you have suggested
Obvious thing would be to trace the connection to see why it is happening. I would suspect a connection reset when not expected or an issue with the garbage handler in .net.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Mar
Starting Member

47 Posts

Posted - 2011-11-15 : 11:04:24
@nigel So by your response you have no idea why or when ADO.NET will open a second connection, correct?

Thank you for your time but I think my question is above the level of this forum.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 11:18:34
Not so but it doesn't sound like you want to give enough information to follow up this issue.
Think you probably need to get someone in to review what you are trying to do - suspect the issues probably go a lot deeper than this and are more architectural than coding.

I suggest that you shuold first find out whether you are using a temp table or a global temp table. Have a look at the differences between them and probably test how they react to being on connections from the client.
Then you can decide on which you want to use and how it should behave.

Note - not true that there is necessarily a contention issue with a permanent table on multiple connections - but that depends on how it is set up and probably needs someone more database specialist than application. Again should look at the system as a whole - and whether the overhead of creating temp tables (which do cause contention) is also an issue.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Mar
Starting Member

47 Posts

Posted - 2011-11-16 : 11:46:16
Sorry nigel, but I do not think you have the skill to help me.
I pretty much supplied all the information that you need. If you do not get it then it is probably over your head. If you need information then why did you not ask for it? I would've provided answers, but I am not a mind reader.

I also told you more than once what kind of temp table I am using. Just because I used shorthand (temp) for the full name (global temp) doesn't change any facts. A global temp table IS a temp table just as a local temp table IS a temp table. If you would make a practice program and work with them you would learn about them and perhaps understand them.

And there would be a contention issue with a permanent table OR an application re-write. Both of which are unacceptable. A better solution would be to simplify and streamline the change tracking process which would not require any data structure changes (which a permanent table would require) and eliminate a pointless table (which a permanent table would be, just another pointless table changed from a temp table to a permanent one). I would just have to re-write all the stored procedures for the best fix. I have a code generator I can modify to produce correct stored procedures.

If a forum called SQLTEAM can not provide any clue as to how or why ADO.NET would create a secondary connection nor provide any links then I may be able to convince the powers that be to abandon these troublesome temp tables.

Thanks you for your post nigel. Even though it does not provide any useful information, it demonstrates my attempt at finding a solution to a problem I haven't seem any solutions for at any forum. Death to the temp table (I hope!)
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 12:04:45
Sounds like a very common sort of problem but as I said - doesn't siound as though you want a solution.
I think you're rright in that I'm not going to be much help.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 12:06:21
Sounds like a very common sort of problem but as I said - doesn't siound as though you want a solution.
I think you're rright in that I'm not going to be much help.
Feel free to come back if you decide you want to learn and want help.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -