Procedure will hang if using two statements

Procedure will hang if using two statements

Post by Uwe Ricke » Thu, 27 Jul 2000 04:00:00



Hallo NG,

I have a problem which will confuse me

Plattform:    NT-Server 4.0, SQL 6.5 SP5a

I have a table called tblStammdatenProperties

I will fire a stored proc to enter values into this table (as shown below)
When I remove the RETURN between the two statements the sp hangs (Profiler will show no actions)

The proble here is a very very compley trigger (i will not post it because its more than 500 lines!)

This trigger will change values in the same table (tblStammdatenProperties)

When i use only the first INSERT/UPDATE-Statement the proc will run perfect
As soon i remove the RETURN (to start 2 insert/updates) the stored proc hangs and will block the whole server

Any ideas, what i can do?

Thank you in advance

Uwe Ricken
GNS GmbH, Frankfurt am Main

 /* Entering DuesBase in die Daten */
 BEGIN TRANSACTION
  IF EXISTS (
    SELECT * FROM dbo.tblStammdatenProperties

     )
    UPDATE dbo.tblStammdatenProperties


  ELSE
    INSERT INTO dbo.tblStammdatenProperties
    (ST_ID, PropId, PropValue)
    VALUES

 COMMIT TRANSACTION

 RETURN        -- !!! This will cause the problem

 /* When entered DuesBase now we can enter DuesAmount*/
 BEGIN TRANSACTION
  IF EXISTS (
    SELECT * FROM dbo.tblStammdatenProperties

     )
   UPDATE sp3
    SET PropValue = MbsDues
   FROM
    tblStammdatenProperties sp1 INNER JOIN tblStammdatenProperties sp2

    INNER JOIN dbo.tblStammdatenProperties sp3

    ON (sp1.PropValue = md.MbsCode AND sp2.PropValue = md.MbsDuesBase)
   WHERE

  ELSE
   INSERT INTO dbo.tblStammdatenProperties
   (ST_ID, PropId, PropValue)
                 SELECT


    md.MbsDues
   FROM
    tblStammdatenProperties sp1 INNER JOIN tblStammdatenProperties sp2

    ON (sp1.PropValue = md.MbsCode AND sp2.PropValue = md.MbsDuesBase)
   WHERE

 COMMIT TRANSACTION

--
Gru?, Uwe Ricken
MCP FOR MS ACCESS DEVELOPMENT
exklusiver Ausbilder der "Ausbilder und Berufschullehrer des Landes Hessen"
fr Datenbankentwicklung
GNS GmbH, Frankfurt am Main
http://www.gns-online.de

____________________________________________________
APP:    http://www.AccessProfiPool.com
FAQ:   http://www.donkarl.com/AccessFAQ.htm
____________________________________________________

 
 
 

Procedure will hang if using two statements

Post by Uwe Ricke » Thu, 27 Jul 2000 04:00:00


Hallo NG,

sorry, I have found the problem by myself.

The problem was the other decimalformat in Germany than international.

We are using , as a decimalseparator but not a .

SQL-Server expects .

So I have made a replace inside the trigger, but the replace was not correct.

--
Gru?, Uwe Ricken
MCP FOR MS ACCESS DEVELOPMENT
exklusiver Ausbilder der "Ausbilder und Berufschullehrer des Landes Hessen"
fr Datenbankentwicklung
GNS GmbH, Frankfurt am Main
http://www.gns-online.de

____________________________________________________
APP:    http://www.AccessProfiPool.com
FAQ:   http://www.donkarl.com/AccessFAQ.htm
____________________________________________________


  Hallo NG,

  I have a problem which will confuse me

  Plattform:    NT-Server 4.0, SQL 6.5 SP5a

  I have a table called tblStammdatenProperties

  I will fire a stored proc to enter values into this table (as shown below)
  When I remove the RETURN between the two statements the sp hangs (Profiler will show no actions)

  The proble here is a very very compley trigger (i will not post it because its more than 500 lines!)

  This trigger will change values in the same table (tblStammdatenProperties)

  When i use only the first INSERT/UPDATE-Statement the proc will run perfect
  As soon i remove the RETURN (to start 2 insert/updates) the stored proc hangs and will block the whole server

  Any ideas, what i can do?

  Thank you in advance

  Uwe Ricken
  GNS GmbH, Frankfurt am Main

   /* Entering DuesBase in die Daten */
   BEGIN TRANSACTION
    IF EXISTS (
      SELECT * FROM dbo.tblStammdatenProperties

       )
      UPDATE dbo.tblStammdatenProperties


    ELSE
      INSERT INTO dbo.tblStammdatenProperties
      (ST_ID, PropId, PropValue)
      VALUES

   COMMIT TRANSACTION

   RETURN        -- !!! This will cause the problem

   /* When entered DuesBase now we can enter DuesAmount*/
   BEGIN TRANSACTION
    IF EXISTS (
      SELECT * FROM dbo.tblStammdatenProperties

       )
     UPDATE sp3
      SET PropValue = MbsDues
     FROM
      tblStammdatenProperties sp1 INNER JOIN tblStammdatenProperties sp2

      INNER JOIN dbo.tblStammdatenProperties sp3

      ON (sp1.PropValue = md.MbsCode AND sp2.PropValue = md.MbsDuesBase)
     WHERE

    ELSE
     INSERT INTO dbo.tblStammdatenProperties
     (ST_ID, PropId, PropValue)
                   SELECT


      md.MbsDues
     FROM
      tblStammdatenProperties sp1 INNER JOIN tblStammdatenProperties sp2

      ON (sp1.PropValue = md.MbsCode AND sp2.PropValue = md.MbsDuesBase)
     WHERE

   COMMIT TRANSACTION

  --
  Gru?, Uwe Ricken
  MCP FOR MS ACCESS DEVELOPMENT
  exklusiver Ausbilder der "Ausbilder und Berufschullehrer des Landes Hessen"
  fr Datenbankentwicklung
  GNS GmbH, Frankfurt am Main
  http://www.gns-online.de

  ____________________________________________________
  APP:    http://www.AccessProfiPool.com
  FAQ:   http://www.donkarl.com/AccessFAQ.htm
  ____________________________________________________

 
 
 

1. Fulltext hangs when using two catalogs

Hi

Im having a weird problem with fulltext searches.

I have two catalogs one for each table. One table holds about .5M rows and
the other less then 10k rows.

My application searches these table using a store procedure trough the sql
server. In the SP each catalog searched on its own.

If searches are run only one catalog there are no problems or errors and the
fulltext service can serve more then 500 connection simultaneously. But if
the SP use two catalogs the server hangs and answers from the full text are
being timed out.

Have any body heard about something like that ?

Some technical data :

Im running sql2k sp2 on win2k advance server sp3  on  cluster machine.

There are 7 cpus on the node only 5 are used by the sql server, and the sql
server is limited to 2G Ram out of the 3G Ram on the node.

I have even noticed that mssearch is not using more then 40M Ram and more
then 30% cpu.

the fulltext resource usage is set to 5

Help

Guy Rafalovich

2. Chop zeros off end of date

3. question RE. running two SQL statements in one Stored Procedure

4. ODBC Connection via TCP/IP

5. Create SQL Statement using two text file

6. Jdbc-Oci7 Error!

7. using two databases with one SQL statement

8. Beta testers wanted for new "blueshell Data Guy Professional"

9. Stored Procedure using xp_sendmail hangs

10. Stored Procedure using xp_sendmail Hangs

11. Two statements in 1 EXEC statement

12. Insert Stored Procedure using two tables

13. Joining two tables from two servers using Execute ...