SqlDbx
You are not logged in.
Pages: 1
In this scenario, I have procs out there I am revising. They are compiled and sitting in the Development server. Twice this week when compiling, I would get errors telling me a #Temp table already exists, even though all I am doing is trying to compile. So, the compile fails. After a few trys, I would close SqlDbx, thinking there was a memory glitch and I needed to clear it of the #Temp table. Upon return to SqlDbx, the procedure I am developing is gone!
The first proc this happened to, the changes were small, and I redid them. The 2nd time, I lost two hours of work. I have been using SqlDbx for a couple of months now without any problems until this. I am backing up the work interactively now just to be safe!
Could there be a bug in SqlDbx that would manage to DROP a proc during a buggy compile error? The DBA's do not think anything on the server side would do this.
Jason
More insight....
I took the proc data and copied it to another tab, and stripped the code so I could run it interactively. From this new tab, I had to drop the #Temp tables each time I re ran it. THEN, I go back to the proc tab and make a change and compile. It seems the Proc tab becomes confused with the #temp table from the other tab and give the error something like this.... Cannot create temporary table '#temp_fa'. Prefix name '#temp_fa' is already in use by another temporary table '#temp_fa'.†Once I see that error, the Proc is dropped and not recreated, therefore it is gone!
So, the problem seems to be a bug between two tabs sharing a same-named #Temp table(s)!!!!
I am now backing up into a script file continually as I develop, rather then just saving to the database. This just saved me an hour of work!!
Jason
So, I see two issues.
1) When two or more tabs have a #Temp table with the same name, should tabs recognize the #Temp table in another tab. It would be best if they are completely separate, if possible.
2) When compiling a Proc, why should it care if a #Temp table exists or not, especially if it is from another tab?
How temp tables created in a first place?
Somehow they are created when you execute script.
SqlDbx does not drop anything on compile error.
Temp tables are unique per connection and not per tab, so it should be
no different of where they referenced. All tabs belong to the same connection.
Is it Sybase or Sql Server? What version?
Offline
OK. So the #temp table is based on the connection. But why is there a "#temp table already exists" error upon compile of a Procedure?
I understand how the Proc got dropped. It is because of the DROP statement before the recompile. But since the recompile fails, and one does not understand what to do, if that tab gets closed, and there is not a backup of the changes to the proc, all the work is lost.
This is Sybase ASE "SQL Server/12.5/P/Generic/OS/1/OPT/Sat Jun 30 00:01:37 PDT 2001"
Jason
I did some testing and this is how Sybase behaves.
If you execute
CREATE TABLE #tmp1 (col1 INT)
and then will try to create procedure like this
CREATE PROCEDURE proctmp
AS
BEGIN
CREATE TABLE #tmp1 (col1 INT)
END
you will get an error "#tmp1 already exists".
Somewhere in your script temp tables created.
Couple of things to do, so you do not loose changes.
1. Check "Enable Session State" in Options->General
2. There's a special mode to edit procedures and functions
Right click on procedure you want to edit and select "Edit Procedure"
Procedure will show in a new window and tab does not have "*"
Modify procedure "*" star will show on a tab.
Execute script. If no error then "*" will disappear. If error then it will stay.
If you try to exit then SqlDbx will complain about changes not saved.
Offline
I had a case one time where I was trying to create a temp table in a if statement. In the else I was also creating a temp table with the same name. It was not compiling because it said the temp table already existed.
Don't know if this is same scenario you have but I thought I would mention it.
Offline
Pages: 1