Solved

SQL Server Trigger locking up third party application.

Posted on 2007-03-19
3
255 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
[X]
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
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

707 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