Hello Every body!

Look at the bellow picture, I have 2 database steps: 1. For the first step, it'll create a LOCAL temporary table and store the data in this table (For ex: It is named '#SUPERIOR') 2. The seconds step, it'll get data from '#SUPERIOR'

But, we have some strange behaviors that sometimes it can not get data from '#SUPERIOR' temporary table. I guess that the '#SUPERIOR' is already dropped (because connection is closed --> local temporary table is dropped) before step 2 is executed.

Our application have a lot of users that can access concurrency (more than 1000 users)

Could you pls give me some ideas?

Thanks for your help!

This is sample of step1:


Declare @ORSID int =1;
  DECLARE @EMPKEY int = 20018;
  DECLARE @CONNUMB varchar = '1';
  DECLARE @SUPERIORORLID int = 1;
  DECLARE @PARENTID int = 0;

  IF OBJECT_ID('tempdb..#SUPERIORORGTREEANDROLE_eRAP20018') IS NOT NULL
    DROP TABLE  #SUPERIORORGTREEANDROLE_eRAP20018

  DECLARE @ORGTREEANDROLE TABLE(
    ORGID INT, ORGPNTOU INT, ORGCHDOU INT,ORSID INT, 
    OUNDESC varchar(MAX), OUNTYPE varchar(MAX), 
    ASNID INT, EMPKEY INT, CONNUMB varchar(MAX), ASNMAIN varchar(MAX),
    ORLID INT
  );
  INSERT INTO @ORGTREEANDROLE
  SELECT org.ORGID, org.ORGPNTOU, org.ORGCHDOU, org.ORSID, 
        ounc.OUNDESC, ounc.OUNTYPE, 
        asnc.ASNID, asnc.EMPKEY, asnc.CONNUMB, asnc.ASNMAIN,
        asr.ORLID

  FROM HRMORG org 
  LEFT OUTER JOIN HRMOUN ounc ON ounc.OUNKEY = org.ORGCHDOU
  LEFT OUTER JOIN HRMASN asnc ON asnc.OUNKEY = ounc.OUNKEY  and  asnc.ASNFROM <= CONVERT(DATE, GETDATE()) and  asnc.ASNUNTIL >= CONVERT(DATE, GETDATE())
  LEFT OUTER JOIN HRMASR asr ON asr.ASNID = asnc.ASNID
  WHERE  org.ORSID = @ORSID and ounc.OUNFROM <= CONVERT(DATE, GETDATE()) and ounc.OUNUNTIL >= CONVERT(DATE, GETDATE())
  ORDER BY org.ORGID


  SELECT @PARENTID = orgTree.ORGPNTOU FROM @ORGTREEANDROLE orgTree 
    WHERE orgTree.CONNUMB = @CONNUMB AND orgTree.EMPKEY = @EMPKEY AND orgTree.ASNMAIN = 'Y'

  DECLARE @COUNT INT = 0;

  WHILE (@COUNT < 20)
  BEGIN

    SET @COUNT = @COUNT + 1;
    IF (@PARENTID = 0 OR @PARENTID = NULL ) 
    BEGIN
        BREAK;
    END
    ELSE
    BEGIN
        IF ((SELECT COUNT(ORGID)FROM @ORGTREEANDROLE orgTree 
        WHERE orgTree.ORGCHDOU = @PARENTID AND orgTree.ORLID = @SUPERIORORLID) > 0)
        BEGIN
            SELECT orgTree.EMPKEY, orgTree.CONNUMB, orgTree.ORGCHDOU ,orgTree.ASNID
            INTO #SUPERIORORGTREEANDROLE_eRAP20018
            FROM @ORGTREEANDROLE orgTree 
            WHERE orgTree.ORGCHDOU = @PARENTID
                AND orgTree.ORLID = @SUPERIORORLID
            BREAK;
        END
    END
    SELECT @PARENTID = orgTree.ORGPNTOU 
    FROM @ORGTREEANDROLE orgTree 
    WHERE orgTree.ORGCHDOU = @PARENTID

  END

This is the sample for step2:


SELECT * FROM #SUPERIORORGTREEANDROLE_eRAP20018;

alt text

Regards,

asked 01.07.2016 at 10:56

Hap%20Em's gravatar image

Hap Em
3191013
accept rate: 0%


Hi

Note that we use a so called connection pool in the background. Therefore it is not ensured that both database steps are executed in the same connection. Connections normally lives at least 10 minutes after they are used the last time.

Since temporary tables lives only as long as the connection is open it may also be only visible to the connection in which it was generated? If this is the case it is clear that you sometimes get into trouble.

The connection that generated the temporary table could also in the mean time be used by another db step that throws an error. In this case the connection is closed immediatly and the temporary table is removed. After that the seconds element is executed but the temporary table is already removed.

To solve this issue it is possible to get a connection from the connection pool in pure java. Then you can created the temporary table and afterwards directly execute the query. Note that it is important to close the connection afterwards so that it is put back to the connection pool.

Regards

Axon.ivy Support

Reto Weiss

link

answered 04.07.2016 at 15:35

Reto%20Weiss's gravatar image

Reto Weiss ♦♦
4.9k182756
accept rate: 74%

Thanks for your suggestion! Look at this link, i saw that your analysis seems to be correct! :) http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server

(05.07.2016 at 04:52) Hap Em Hap%20Em's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×125
×3

Asked: 01.07.2016 at 10:56

Seen: 817 times

Last updated: 05.07.2016 at 04:52