Solved

SQL Server Trigger locking up third party application.

Posted on 2007-03-19
3
243 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now