RE: Cursor Not Running Correctly.. Need Help/Suggestions...

From: John Bell (JohnBell_at_discussions.microsoft.com)
Date: 08/07/04 

Date: Fri, 6 Aug 2004 23:17:01 -0700

Hi

It is not a good idea to use a trigger to send mail as this could severly
increase the time your transactions are taking and therefore make your system
unusable/unscaleable The most likely reasons you are having problems is to do
with permissions or you are trying to use data that has not been commited.

John

"Robert Leonard III" wrote:

> I'm a newbie here, so if this is an obvious problem with my code, please be
> kind! :)
>
> Firstly, I am using SQL Server 2000 on a Windows 2000 Advanced Server...
>
> For some reason the following code works perfectly when run through Query
> Analyzer, yet when I fire it off from a Trigger, the CURSOR portion of the
> code doesn't appear to work.. the rest of the report works fine..
>
> Is there something I need to change to get this to work? The only
> difference I can think of between running it as a Stored Procedure and
> through Query Analyzer is that the Stored Procedure executes it from a
> Trigger call.. When I moved this code into the trigger itself, rather than
> calling the Stored Procedure from within the trigger, it failed the same
> way. and that code also worked when pasted into the query analyzer.. I don't
> get it! The @@Fetch_Status variable returns a -1 the first pass when run in
> the "Non-Query Ananlyzer", ie, real, mode.. when I run that SAME code in
> Query Analyzer it works perfectly..
>
> Any Idea?
>
> The Code is pasted Below..
> Thanks!
>
> ---------------------------------------
> CREATE PROCEDURE dbo.usp_SendNewWebOrder AS
>
>
> EXEC master.dbo.xp_sendmail
> @recipients = 'Preferred Customer Specialists',
> @subject = 'New Web Order',
> @message = 'A HTML File Containing New Web Order Information is
> Attached. Please Open to retreive Information',
> @attachments='NewWebOrder.html',
> @attach_results = 'TRUE',
> @width = 999,
> @dbuse = 'iGoma',
> @no_header='TRUE',
> @query = '
>
>
> SELECT ''<HTML><HEAD><TITLE>New Web Order</TITLE></HEAD>'',
> ''<BODY>
> <H1><CENTER>New Web Order</CENTER></H1>''
>
>
> DECLARE @NewOrderID INT
> Select @NewOrderID = OrderID from NewWebOrder
>
> DECLARE @CustName VARCHAR (30)
> DECLARE @CompanyName VARCHAR (40)
> DECLARE @AddressLine1 VARCHAR (80)
> DECLARE @AddressLine2 VARCHAR (80)
> DECLARE @City VARCHAR (20)
> DECLARE @REGION VARCHAR (20)
> DECLARE @PostCode VARCHAR (15)
> DECLARE @Phone VARCHAR (15)
> DECLARE @Extension VARCHAR (15)
> DECLARE @Fax VARCHAR (15)
> DECLARE @Country VARCHAR (30)
>
> /* Bill To Information */
> SELECT ''<H3>Bill To Information</H3>''
> SELECT @CustName = OrderAddresses.FirstName + '' '' +
> OrderAddresses.LastName,
> @CompanyName = OrderAddresses.Company,@AddressLine1 =
> OrderAddresses.AddressLine1,
> @AddressLine2 = OrderAddresses.AddressLine2, @City = OrderAddresses.City,
> @Region = OrderAddresses.Region, @PostCode = OrderAddresses.PostalCode,
> @Country = OrderAddresses.Country, @phone = OrderAddresses.Phone,
> @Extension = OrderAddresses.Extension,
> @Fax = OrderAddresses.Fax
> FROM OrderAddresses
> INNER JOIN Orders
> ON OrderAddresses.OrderAddressID = Orders.BillToAddressID
> WHERE (Orders.OrderID = @NewOrderID)
>
>
> SELECT ''Customer Name: '', @CustName, ''<BR>'', ''Company Name: '',
> @CompanyName, ''<BR>'', ''Billing Address Line1: '',
> @AddressLine1, ''<BR>'', ''Billing Address Line2: '', @AddressLine2,
> ''<BR>'', ''City: '', @City, ''<BR>'',''State/Region: '',
> @Region, ''<BR>'', ''Postal Code: '', @PostCode, ''<BR>'', ''Country: '',
> @Country, ''<BR>'',
> ''Phone Number: '', @Phone, ''<BR>'', '' Extension: '', @Extension,
> ''<BR>'', ''Fax Number: '', @Fax, ''<BR>''
>
>
> /* Ship To Information */
> SELECT ''<H3>Ship To Information</H3>''
> SELECT @CustName = OrderAddresses.FirstName + '' '' +
> OrderAddresses.LastName,
> @CompanyName = OrderAddresses.Company,@AddressLine1 =
> OrderAddresses.AddressLine1,
> @AddressLine2 = OrderAddresses.AddressLine2, @City = OrderAddresses.City,
> @Region = OrderAddresses.Region, @PostCode = OrderAddresses.PostalCode,
> @Country = OrderAddresses.Country, @phone = OrderAddresses.Phone,
> @Extension = OrderAddresses.Extension,
> @Fax = OrderAddresses.Fax
> FROM OrderAddresses
> INNER JOIN Orders
> ON OrderAddresses.OrderAddressID = Orders.ShipToAddressID
> WHERE (Orders.OrderID = @NewOrderID)
>
>
> SELECT ''Customer Name: '', @CustName, ''<BR>'', ''Company Name: '',
> @CompanyName, ''<BR>'', ''Billing Address Line1: '',
> @AddressLine1, ''<BR>'', ''Billing Address Line2: '', @AddressLine2,
> ''<BR>'', ''City: '', @City, ''<BR>'',''State/Region: '',
> @Region, ''<BR>'', ''Postal Code: '', @PostCode, ''<BR>'', ''Country: '',
> @Country, ''<BR>'',
> ''Phone Number: '', @Phone, ''<BR>'', '' Extension: '', @Extension,
> ''<BR>'', ''Fax Number: '', @Fax, ''<BR>''
>
> SELECT ''<H3>Order Information</H3>''
> DECLARE @OrderNo VARCHAR (15)
> DECLARE @DateOrdered VARCHAR (30)
> DECLARE @PONumber VARCHAR (10)
> DECLARE @SubTotal MONEY
> DECLARE @Tax Money
> DECLARE @ShippingCharge MONEY
> DECLARE @Total MONEY
> DECLARE @ShipMethod VARCHAR (10)
> DECLARE @TenderType VARCHAR (15)
>
> SELECT @OrderNo = Orders.OrderNo, @DateOrdered = Orders.DateOrdered,
> @PONumber = Orders.PONumber,
> @SubTotal = Orders.SubTotal, @ShipMethod = ShippingMethods.Description,
> @TenderType = TenderTypes.Description
> FROM Orders
> INNER JOIN ShippingMethods
> ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID
> INNER JOIN TenderTypes
> ON Orders.TenderTypeID = TenderTypes.TenderTypeID
> WHERE (Orders.OrderID = @NewOrderID)
>
> SELECT ''Order Number: '', @OrderNo, ''<BR>'', ''Date of Order: '',
> @DateOrdered, ''<BR>'',
> ''PO Number: '', @PONumber, ''<BR>'', ''SubTotal: '', @SubTotal, ''<BR>'',
> ''Tax: '', @Tax,
> ''<BR>'', ''Shipping Charge: '', @ShippingCharge, ''<BR>'', ''Total: '',
> @Total, ''<BR>'',
> ''Shipping Method: '', @ShipMethod, ''<BR>'', ''Tender Type: '',
> @TenderType, ''<BR>''
>
> SELECT ''<H3>Parts Ordered</H3>''
> DECLARE @PartNum VARCHAR (80)
> DECLARE @Quantity VARCHAR (10)
> DECLARE @UnitCost MONEY
> DECLARE @PartName VARCHAR (80)
> DECLARE Parts CURSOR LOCAL
> FORWARD_ONLY
> FOR
> SELECT Parts.PartNumber, Parts.PartName, OrderDetails.Quantity,
> OrderDetails.UnitCost
> FROM OrderDetails
> INNER JOIN Parts
> ON OrderDetails.PartID = Parts.PartID
> INNER JOIN Orders
> ON OrderDetails.OrderID = Orders.OrderID
> WHERE (Orders.OrderID = @NewOrderID)
> OPEN Parts
> FETCH NEXT FROM Parts
> INTO @PartNum, @PartName, @Quantity, @UnitCost
> WHILE @@FETCH_STATUS=0
> BEGIN
> SELECT ''Part Number: '', @Partnum, ''<BR>'',
> ''Part Name: '', @PartName, ''<BR>'',
> ''Quantity: '', @Quantity, ''<BR>'',
> ''Unit Cost: '', @UnitCost, ''<BR><BR>''
> FETCH NEXT FROM Parts
> INTO @PartNum, @PartName, @Quantity, @UnitCost
> END
> CLOSE Parts
> DEALLOCATE Parts
> SELECT ''</BODY></HTML>''
> '
> DROP TABLE NewWebOrder
> GO
>
>

Relevant Pages

  • Cursor Not Running Correctly.. Need Help/Suggestions...
    ... DECLARE @NewOrderID INT ... DECLARE @CompanyName VARCHAR ... INNER JOIN Orders ... DECLARE @SubTotal MONEY ...
    (microsoft.public.sqlserver.programming)
  • Re: INSTEAD OF UPDATE Trigger
    ... and I am sorry that you spent so much time building that trigger. ... Pro SQL Server 2000 Database Design - ... > @Calc_WComputed_X_Amt Money, ...
    (microsoft.public.sqlserver.programming)
  • simple trigger question...
    ... Take the following test trigger: ... declare @id int ... SELECT @id=x.ID FROM xxx x inner join inserted i on i.id = x.id Inner join ...
    (microsoft.public.sqlserver.programming)
  • Re: adding record to sql db
    ... > create trigger tD_BCustomerCA on BCustomerCA for DELETE as ... > declare @numrows int, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: how to assign the contents of a field to a variable
    ... what happens if the MSMQ server is ... teh requirements of the trigger are to export an XML document to MSMQ ... DECLARE @int_msmqqueue INT ... IF @int_result 0 GOTO ErrorHandler ...
    (microsoft.public.sqlserver.programming)
Loading