Using local temporary table

Hap Em — Fri, 01 Jul 2016 10:56:57 -0400

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;

Regards,

Storing DateTime in database

dbalasse — Mon, 23 Mar 2015 17:45:22 -0400

Hello,

we use database (sqlserver) to store values. One of those field is an ivy DateTime. When we try to store it in the database we have this error :

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

The ivy field is a ch.ivyteam.ivy.scripting.objects.DateTime and it must be stored in a smalldatetime filed from sqlserver (10.50.4000.0).

What are we doing wrong ?

Database conversion stays forever on "SET READ_COMMITTED_SNAPSHOT ON"

SupportIvyTeam — Mon, 21 Jul 2014 17:30:15 -0400

If I convert the Ivy System Database from version 4.2 (Database version 25) to 4.3 (DB version 30) or newer the conversion stays forever on (is blocked on) the task "SET READ_COMMITTED_SNAPSHOT ON".
The conversion dialog looks like this: What is wrong? Is my database to big? Is there any fix or workaround?

Questions Tagged With database_conversion

Using local temporary table

Storing DateTime in database

Database conversion stays forever on "SET READ_COMMITTED_SNAPSHOT ON"