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

From: Robert Leonard III (RobertLeonardIII_at_discussions.microsoft.com)
Date: 08/05/04 

Date: Thu, 5 Aug 2004 07:21:03 -0700

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