Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server Trigger locking up third party application.

Posted on 2007-03-19
3
250 Views
Last Modified: 2012-05-05
We just upgraded from SQL Server 2000 to SQL Server 2005.  We have several Triggers, all but one we were able to convert with no problem.  We have this one that when the Application hits it, it just makes the application hang.

TRIGGER:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[EMAIL] ON [dbo].[RECEIVABLE_LINE]
FOR INSERT
AS

DECLARE @CUST_ORDER_ID AS VARCHAR(15)
DECLARE @INVOICE_ID AS VARCHAR(15)
DECLARE @TERRITORY AS VARCHAR(15)
DECLARE @INVOICE_LINE_NO AS INT
DECLARE @PACKLIST_ID AS VARCHAR(15)
DECLARE @FRT_TRM AS VARCHAR(15)

SET @INVOICE_ID = (SELECT INVOICE_ID FROM inserted)
SET @CUST_ORDER_ID = (SELECT TOP 1 CUST_ORDER_ID FROM RECEIVABLE_LINE WHERE INVOICE_ID = @INVOICE_ID)
SET @PACKLIST_ID =  (SELECT PACKLIST_ID FROM inserted)
SET @TERRITORY = (SELECT TERRITORY FROM CUSTOMER_ORDER WHERE ID = @CUST_ORDER_ID)
SET @INVOICE_LINE_NO = (SELECT LINE_NO FROM inserted)

IF @TERRITORY = 'Yes' and @INVOICE_LINE_NO = 1

BEGIN

DECLARE @CUSTOMER_ID AS VARCHAR(15)
DECLARE @CUSTOMER_PO AS VARCHAR(30)
DECLARE @FREIGHT_TERMS AS CHAR(1)
DECLARE @CUSTOMER_NAME AS VARCHAR(50)
DECLARE @SHIPTO_ID AS VARCHAR(15)
DECLARE @FOB AS VARCHAR(25)
DECLARE @SHIPPED_DATE AS CHAR(10)
DECLARE @WAYBILL AS VARCHAR(20)
DECLARE @VEHICLE AS VARCHAR(20)
DECLARE @INTER AS VARCHAR(20)
DECLARE @SHIP_VIA AS VARCHAR(40)
DECLARE @TODAY AS CHAR(10)
DECLARE @ORDER_SPEC AS VARCHAR(5000)

DECLARE @TERMS_DAYS AS SMALLINT
DECLARE @TERMS_NET_DAYS AS SMALLINT
DECLARE @TERMS_DISC AS DECIMAL(6,3)
DECLARE @TERMS_DESC AS VARCHAR(50)
DECLARE @PAYMENT_TERMS AS VARCHAR(100)

DECLARE @SOLD_ADDR_1 VARCHAR(50)
DECLARE @SOLD_ADDR_2 VARCHAR(50)
DECLARE @SOLD_ADDR_3 VARCHAR(50)
DECLARE @SOLD_ADDR VARCHAR(150)
DECLARE @SOLD_CITY VARCHAR(30)
DECLARE @SOLD_STATE VARCHAR(10)
DECLARE @SOLD_ZIPCODE VARCHAR(10)
DECLARE @SOLD_COUNTRY VARCHAR(50)

DECLARE @SHIP_NAME VARCHAR(50)
DECLARE @SHIP_ADDR_1 VARCHAR(50)
DECLARE @SHIP_ADDR_2 VARCHAR(50)
DECLARE @SHIP_ADDR_3 VARCHAR(50)
DECLARE @SHIP_ADDR VARCHAR(150)
DECLARE @SHIP_CITY VARCHAR(30)
DECLARE @SHIP_STATE VARCHAR(10)
DECLARE @SHIP_ZIPCODE VARCHAR(10)
DECLARE @SHIP_COUNTRY VARCHAR(50)

--variables for email
DECLARE @recipt VARCHAR(50)
DECLARE @sbjct VARCHAR(255)
DECLARE @msg VARCHAR(7990)
DECLARE @msg1 VARCHAR(200)
DECLARE @msg2 VARCHAR(60)
DECLARE @msg3 VARCHAR(200)
DECLARE @msg35 VARCHAR(15)
DECLARE @msg4 VARCHAR(200)
DECLARE @msg5 VARCHAR(200)
DECLARE @msg6 VARCHAR(100)
DECLARE @msg7 VARCHAR(100)
DECLARE @msg8 VARCHAR(100)
DECLARE @msg9 VARCHAR(100)

DECLARE @rtn INT
DECLARE @CRLF Char(2)
DECLARE @TAB Char(1)

SET @CRLF= Char(13)+Char(10)
SET @TAB = Char(9)

SELECT     @ORDER_SPEC = CAST(CONVERT(varchar(5000), CONVERT(binary(8000), CUST_ORDER_BINARY.BITS)) AS varchar(4000))
FROM         CUSTOMER_ORDER INNER JOIN
                      CUST_ORDER_BINARY ON CUSTOMER_ORDER.ID = CUST_ORDER_BINARY.CUST_ORDER_ID
WHERE CUSTOMER_ORDER.ID = @CUST_ORDER_ID

SET @ORDER_SPEC = 'CUSTOMER ORDER SPECIFICATIONS' + @CRLF + isnull(@ORDER_SPEC,' ') + @CRLF

SELECT @CUSTOMER_PO = CUSTOMER_PO_REF,  @FREIGHT_TERMS = FREIGHT_TERMS,
               @FOB = FREE_ON_BOARD, @CUSTOMER_ID = CUSTOMER_ID , @recipt  = CONTACT_EMAIL,
              @TERMS_DAYS = TERMS_DISC_DAYS, @TERMS_DISC = TERMS_DISC_PERCENT, @TERMS_DESC = TERMS_DESCRIPTION,
                @TERMS_NET_DAYS = TERMS_NET_DAYS
FROM CUSTOMER_ORDER WHERE ID = @CUST_ORDER_ID

SELECT DISTINCT  @SHIPTO_ID = CUST_ORDER_LINE.SHIPTO_ID
FROM         SHIPPER_LINE INNER JOIN
                      CUST_ORDER_LINE ON SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND
                      SHIPPER_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO
WHERE SHIPPER_LINE.PACKLIST_ID = @PACKLIST_ID AND  (SHIPPER_LINE.USER_SHIPPED_QTY > 0)

if @SHIPTO_ID IS NULL
BEGIN
SELECT  @SHIPTO_ID = SHIPTO_ID
FROM CUSTOMER_ORDER WHERE ID = @CUST_ORDER_ID
END

if @TERMS_DISC  =  0
BEGIN
set @PAYMENT_TERMS =   'Net ' +  isnull(cast(@TERMS_NET_DAYS as varchar(5)),' ' ) + '  ' + isnull(@TERMS_DESC, ' ')
END
ELSE
BEGIN
set @PAYMENT_TERMS =  isnull(cast(@TERMS_DISC as varchar(10)),' ' ) + '% / ' + isnull(cast(@TERMS_DAYS as varchar(5)),' ' ) +   ' days , Net ' +  isnull(cast(@TERMS_NET_DAYS as varchar(5)),' ' ) +  + isnull(@TERMS_DESC, ' ')
END

SELECT @SHIPPED_DATE = CONVERT(char(10), SHIPPED_DATE, 101) ,  @WAYBILL = WAYBILL_NUMBER, @VEHICLE = VEHICLE_NUMBER, @INTER = INTER_CONSIGNEE, @SHIP_VIA  = POOL_CODE
FROM SHIPPER WHERE PACKLIST_ID = @PACKLIST_ID

SELECT    @CUSTOMER_NAME = CUSTOMER.NAME, @SOLD_ADDR_1 = CUSTOMER.ADDR_1, @SOLD_ADDR_2 = CUSTOMER.ADDR_2,
                  @SOLD_ADDR_3 =  CUSTOMER.ADDR_3, @SOLD_CITY= CUSTOMER.CITY, @SOLD_STATE =  CUSTOMER.STATE,
                  @SOLD_ZIPCODE = CUSTOMER.ZIPCODE, @SOLD_COUNTRY =  CUSTOMER.COUNTRY
FROM         CUSTOMER_ORDER INNER JOIN
                      CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID
WHERE CUSTOMER_ORDER.ID = @CUST_ORDER_ID

SET @TODAY =   (SELECT     CONVERT(char(10), { fn NOW() },101) )
SET @sbjct =  'Your Order #' + @CUSTOMER_PO +  ' has been shipped'
SET @msg1 = 'Dear Customer, '  + @CRLF+ @CRLF +  'This is to inform you that your order, ' + @CUSTOMER_PO +  ', shipped on Packlist #' + @PACKLIST_ID + +', dated ' + Cast(@SHIPPED_DATE as varchar(20)) + '.  Below are the details of the shipment:'  + @CRLF + @CRLF
--SET @msg2 = 'Packlist ID: ' + @PACKLIST_ID +  @CRLF +  'Date: ' +Cast(@TODAY as varchar(20)) +   @CRLF+  @CRLF
SET @msg2 = '  ' + @CRLF
SET @msg3 = 'Portec Rail Products, Inc.' + @CRLF + 'RMP Division' + @CRLF + 'PO Box 38250' + @CRLF +  '900 Old Freeport Rd.' + @CRLF + 'Pittsburgh, PA  15238-3124'  + @CRLF + '(412) 782-6000' + @CRLF + @CRLF + @CRLF

      IF @SOLD_ADDR_3 IS NOT NULL
      BEGIN
            SET @SOLD_ADDR = @SOLD_ADDR_1 +  @CRLF +  @SOLD_ADDR_2  + @CRLF +  @SOLD_ADDR_3  + @CRLF
      END
             ELSE IF  @SOLD_ADDR_2 IS NOT NULL
             BEGIN
            SET @SOLD_ADDR = @SOLD_ADDR_1  +@CRLF + @SOLD_ADDR_2 + @CRLF
      END
      ELSE
      BEGIN
            SET @SOLD_ADDR = @SOLD_ADDR_1 + @CRLF


      END

SET @msg4 = 'Sold To Address'   +@CRLF+   @CUSTOMER_NAME  + @CRLF +   isnull(@SOLD_ADDR,' ' ) +  isnull(@SOLD_CITY,' ') + ', ' +  isnull(@SOLD_STATE, ' ') + ' ' +  isnull(@SOLD_ZIPCODE,' ')  + @CRLF+   isnull(@SOLD_COUNTRY,' ')  +@CRLF +@CRLF
IF @SHIPTO_ID IS NULL
BEGIN
SET @msg5 = 'Ship To Address'  + @CRLF +    @CUSTOMER_NAME  + @CRLF+   isnull(@SOLD_ADDR,' ') +  isnull(@SOLD_CITY, ' ') + ', ' + isnull(@SOLD_STATE,' ') + ' ' +  isnull(@SOLD_ZIPCODE,' ')  + @CRLF + isnull(@SOLD_COUNTRY,' ' )  + @CRLF  + @CRLF  + @CRLF
END
ELSE
BEGIN

SELECT     @SHIP_NAME = NAME, @SHIP_ADDR_1 =  CUST_ADDRESS.ADDR_1,  @SHIP_ADDR_2  = CUST_ADDRESS.ADDR_2,
                   @SHIP_ADDR_3  = CUST_ADDRESS.ADDR_3,  @SHIP_CITY = CUST_ADDRESS.CITY,
                   @SHIP_STATE =  CUST_ADDRESS.STATE,  @SHIP_ZIPCODE = CUST_ADDRESS.ZIPCODE,
                  @SHIP_COUNTRY =  CUST_ADDRESS.COUNTRY
FROM           CUST_ADDRESS
WHERE   CUST_ADDRESS.SHIPTO_ID = @SHIPTO_ID  AND CUST_ADDRESS.CUSTOMER_ID = @CUSTOMER_ID

SET @SHIP_ADDR = @SHIP_ADDR_1  + @CRLF+ ISNULL(@SHIP_ADDR_2  + @CRLF,'') + @SHIP_ADDR_3

SET @msg5 = 'Ship To Address'   +@CRLF +  isnull(@SHIP_NAME,' ')  +@CRLF +isnull(@SHIP_ADDR+@CRLF,' ')+ isnull(@SHIP_CITY,' ') + ', ' + isnull(@SHIP_STATE,' ') + ' ' + isnull(@SHIP_ZIPCODE, ' ')  + @CRLF+  isnull(@SHIP_COUNTRY,' ')  + @CRLF  + @CRLF  + @CRLF + @CRLF

END

IF @FREIGHT_TERMS='B'
BEGIN
      SET @FRT_TRM = 'BILLED'
END

IF @FREIGHT_TERMS='P'
BEGIN
      SET @FRT_TRM = 'PREPAID'
END

IF @FREIGHT_TERMS='C'
BEGIN
      SET @FRT_TRM = 'COLLECT'
END


SET @msg6 = '       CUSTOMER ID: ' + @CUSTOMER_ID +@TAB +@TAB + '     PORTEC CO #: ' + @CUST_ORDER_ID + @CRLF
SET @msg7 = '     FREIGHT TERMS: ' + isnull(@FRT_TRM, ' ') + @TAB + @TAB + @TAB + '    F.O.B.: ' + isnull(@FOB, ' ') + @CRLF
SET @msg8 = 'PRO NUMBER/WAYBILL: ' + isnull(@WAYBILL, ' ') + ', ' + isnull(@VEHICLE,' ') + ', ' + isnull(@INTER, ' ') +  @TAB + @TAB + ' SHIPPING METHOD: ' + isnull(@SHIP_VIA, ' ') + @CRLF
--SET @msg9 = '  OUR ORDER NUMBER: ' + @CUST_ORDER_ID  + @CRLF   + @CRLF   + @CRLF


--Get packlist line info
if object_id('##tmp_email_shipped') is not null
DROP TABLE   tmp_email_shipped

--SELECT    CUST_ORDER_LINE.USER_ORDER_QTY AS ORD_QTY,  SHIPPER_LINE.USER_SHIPPED_QTY AS SHIP_QTY,
SELECT       SHIPPER_LINE.USER_SHIPPED_QTY AS SHIP_QTY,
                     CUST_ORDER_LINE.PART_ID,   PART.DESCRIPTION,
                       isnull(CUST_ORDER_LINE.CUSTOMER_PART_ID,' ') as CUST_PART_ID
INTO            ##tmp_email_shipped
FROM         SHIPPER_LINE INNER JOIN
                      CUST_ORDER_LINE ON SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND
                      SHIPPER_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO INNER JOIN
                      PART ON CUST_ORDER_LINE.PART_ID = PART.ID
WHERE SHIPPER_LINE.PACKLIST_ID = @PACKLIST_ID AND  (SHIPPER_LINE.USER_SHIPPED_QTY > 0)

if object_id('##tmp_email_shipped_not') is not null
DROP TABLE   ##tmp_email_shipped_not

--SELECT     CUST_ORDER_LINE.USER_ORDER_QTY AS ORD_QTY, SHIPPER_LINE.USER_SHIPPED_QTY AS SHIP_QTY, CUST_ORDER_LINE.PART_ID, PART.DESCRIPTION,
SELECT     SHIPPER_LINE.USER_SHIPPED_QTY AS SHIP_QTY, CUST_ORDER_LINE.PART_ID, PART.DESCRIPTION,
                      isnull(CUST_ORDER_LINE.CUSTOMER_PART_ID,' ') AS CUST_PART_ID
INTO            ##tmp_email_shipped_not
FROM         SHIPPER_LINE INNER JOIN
                      CUST_ORDER_LINE ON SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND
                      SHIPPER_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO INNER JOIN
                      PART ON CUST_ORDER_LINE.PART_ID = PART.ID
WHERE SHIPPER_LINE.PACKLIST_ID = @PACKLIST_ID AND  (SHIPPER_LINE.USER_SHIPPED_QTY = 0)

INSERT INTO ##tmp_email_shipped_not
SELECT     *
FROM         ##tmp_email_shipped

SET @msg =  @msg1 + @msg3 +  @msg4 + @msg5 + @msg6 + @msg7 + @msg8 + @msg2


  EXEC msdb.dbo.sp_send_dbmail
    @recipients = @recipt,
    @body = @msg,
    @query = 'SELECT * FROM  ##tmp_email_shipped',
--   @copy_recipients = 'scox@portecrail.com; mautnerben@yahoo.com' ,
    @subject = @sbjct
DROP TABLE ##tmp_email_shipped_not
DROP TABLE ##tmp_email_shipped
END
0
Comment
Question by:ourguru
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
ID: 18751268
ok the first problem is that the trigger is incorrectly written and doesn't
cater for multiple rows being insert at the same time.

second is that you need to get rid of all the extraneous sql select statements by combining them into
a simpler set .,..
e.g.

this
SELECT   @ORDER_SPEC = CAST(CONVERT(varchar(5000), CONVERT(binary(8000), CUST_ORDER_BINARY.BITS)) AS varchar(4000))
FROM      CUSTOMER_ORDER
INNER JOIN  CUST_ORDER_BINARY ON CUSTOMER_ORDER.ID = CUST_ORDER_BINARY.CUST_ORDER_ID
WHERE CUSTOMER_ORDER.ID = @CUST_ORDER_ID

SET @ORDER_SPEC = 'CUSTOMER ORDER SPECIFICATIONS' + @CRLF + isnull(@ORDER_SPEC,' ') + @CRLF

SELECT @CUSTOMER_PO = CUSTOMER_PO_REF,  @FREIGHT_TERMS = FREIGHT_TERMS,
               @FOB = FREE_ON_BOARD, @CUSTOMER_ID = CUSTOMER_ID , @recipt  = CONTACT_EMAIL,
              @TERMS_DAYS = TERMS_DISC_DAYS, @TERMS_DISC = TERMS_DISC_PERCENT, @TERMS_DESC = TERMS_DESCRIPTION,
                @TERMS_NET_DAYS = TERMS_NET_DAYS
FROM CUSTOMER_ORDER WHERE ID = @CUST_ORDER_ID

to

SELECT   @ORDER_SPEC = 'CUSTOMER ORDER SPECIFICATIONS' + @CRLF
       + isnull(CAST(CONVERT(varchar(5000), CONVERT(binary(8000), CUST_ORDER_BINARY.BITS)) AS varchar(4000))
            ,' ') + @CRLF
            ,@CUSTOMER_PO = CUSTOMER_PO_REF,  @FREIGHT_TERMS = FREIGHT_TERMS,
           @FOB = FREE_ON_BOARD, @CUSTOMER_ID = CUSTOMER_ID , @recipt  = CONTACT_EMAIL,
           @TERMS_DAYS = TERMS_DISC_DAYS, @TERMS_DISC = TERMS_DISC_PERCENT, @TERMS_DESC = TERMS_DESCRIPTION,
           @TERMS_NET_DAYS = TERMS_NET_DAYS

FROM      CUSTOMER_ORDER
Left Outer  JOIN  CUST_ORDER_BINARY
  ON CUSTOMER_ORDER.ID = CUST_ORDER_BINARY.CUST_ORDER_ID
WHERE CUSTOMER_ORDER.ID = @CUST_ORDER_ID


0
 

Author Comment

by:ourguru
ID: 18751402
Ok, made those changes, still hangs though.
0
 

Assisted Solution

by:BarkleyDog
BarkleyDog earned 200 total points
ID: 18766049
This line seems out of place

--Get packlist line info
if object_id('##tmp_email_shipped') is not null
DROP TABLE   tmp_email_shipped

Everywhere else you drop the ##tablename

perhaps it is hanging on the table drop - would DROP TABLE ##tmp_email_shipped do the trick?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question