on 11-25-2014 4:54 PM
We have noticed that when we use a temp table in a stored procedure and then execute the procedure, the temp table remains. So if we execute the procedure again, it will fail with a table already exists error.
Are we missing something? Here is what we are trying to do:
CREATE PROCEDURE SessionVariableGetMany
(
SessionId CHAR ( 16 ),
VarNames CHAR ( 2000 ),
CURSOR VARYING OUTPUT
)
BEGIN
Declare @Sql char(2000);
@Sql = 'Select SV_Key, SV_Value Into #tempSVGM From SessionV Where SV_CustNum = ''' + _SessionID + ''' And SV_Key In (' + RTrim(_VarNames) + ');';
Execute Immediate RTrim(@Sql);
Select * From #tempSVGM;
END;
Any insight into this would be greatly appreciated.
Dave Anderson
Hi Dave,
That would be expected. The temp table will continue to be available until it is dropped or the connection is closed.
This behavior lets a Stored Procedure create and populate a temp table that is then available to further query at a later time if needed (i.e. another sql statement or another stored procedure)
There are two possibilities.
A) Use a TRY/CATCH block to create the table / delete the contents
TRY
Create table #tempSVGM ....
CATCH ALL
delete from #tempSVGM...
END TRY
B) Use an if exists with sp_GetTables
if exists execute procedure sp_GetTables(null, null, 'tempSVGM', 'LOCAL TEMPORARY').....
(I've not had a chance to try B, but I'm confident it would work).
Edgar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Edgar,
We tried your first example to do a create of the temporary table but the following Execute Immediate throws an error when it is changed to an Insert Into statement. The records still seem to get populated, at least when tried in the sql query window (not tried in the sp yet). I am not sure why an error is thrown and it a non-descript ISAM error. We also tried to drop the table in the try catch instead of creating the table and leaving the select into statement as is, but it says that it is locked and does not drop the table, so this does not work either. Are we going to have to trap the execute immediate error as well and just ignore?
Thank you,
Dave Anderson
Below is the stored procedure we are trying to create. Below that is the ISAM error we get when doing the insert into the temporary table. It is very similar to your example but the select is coming out of a temporary table. The parameters used were '0000321234695246' and '''Cust_Delivery_Info'',''Pickup_Delivery'''. I have tried removing the "And SV_Key In(..." statement but I get the same error either way. Any insights would be helpful and appreciated.
Create PROCEDURE SessionVariableGetMany
(
SessionId CHAR ( 16 ),
VarNames CHAR ( 2000 ),
CURSOR VARYING OUTPUT
)
BEGIN
Declare @Sql char(2000);
@Sql = 'Insert Into #tempSVGM (SV_Key, SV_Value) Select SV_Key, SV_Value From SessionV Where SV_CustNum = ''' + _SessionID + ''' And SV_Key In (' + RTrim(_VarNames) + ');';
Try
Create Table #tempSVGM (SV_Key Char( 50 ), SV_Value Char( 254 ));
Catch All
Delete From #tempSVGM;
End Try;
Execute Immediate RTrim(@Sql);
Select * From #tempSVGM;
END;
poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154; [iAnywhere Solutions][Advantage SQL][ASA] Error 5154:
Execution of the stored procedure failed. Procedure Name: SessionVariableGetMany. Error 7200: AQE Error: State = S0000;
NativeError = 2004; [iAnywhere Solutions][Advantage SQL Engine][ISAM]ISAM error <identifier> - EXECUTE IMMEDIATE --
Location of error in the SQL statement is: 310 (line: 10 column: 21)
Hmm...
This works for me. Just changed the base table in the select statement to one I had already.
I was running this on 11.10.0.20 (and upgraded to 11.10.0.22 just in case it introduced something)
Some things that come to mind.
A) Is there anything logged into the ads_err.adt / ads_err.dbf at the same time?
B) Is it possible you already have another temp table created with a bit different structure? - If you are using ARC, maybe close the ARC SQL window and run it again?
Are you running this in a different client? Shouldn't matter, but just checking.
We found the issue. It has to do with null values in the fields of the table being selected from that are to be inserted into the temporary table. Even though the temporary table is created to accept nulls when the insert is attempted it fails with the ISAM error. If the select is changed to do an IsNull(<fieldName>. '') the process completes without failure. There seems to be an issue with inserting nulls into temporary tables.
We are using ADS 11.10.0.22 as well. The base table, however, is a Foxpro table. We suspect that there is an issue in the translator for this value. We should use the work around for now, unless you have other ideas or we are not importing or querying the Foxpro tables properly.
Thank you again for your help in narrowing down what the issues were,
Dave
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.